how to use indexes/keys in asp?

[NinjaTurtle]

dear,

i already define a new indexes in SQL SERVER 2000 named IX_ITEMCODE, but i dont know how to used it in my SQL Statement. i try to list down all the items from table, but i use more than 1 minutes to display those records, any way to faster the speed??? ecept store procedure, bcos i dunno how to use it.

Example:

SELECT * FROM ITEM

how to used it????i use indexes is bcos i have >300 items in the table called ITEM. i want to list all the items in on

[raf]

not sure i understood all that. Now, indexes are used to speed up ordering filtering/selecting record. But with your query, it woulnt make a difference. If you'de have a indexed variable "varind" and you'de run
select * from table where varind=1
then this would be processed faster then if the variable wasnot indexed. But it only does anything if you have a condition or order statement is your sql.

Don't need to do anything special in sql if the variable is indexed. its more a matter of db-design : for all variables that will be used frequently for selects, it's worth considering to index them (makes you db grow a bit thow)

Easiest way to speed up things is to limit the number of variables you select. So type in the variablenames (column-names) that you really need instead of *. Also, i can't imagen you need so much records it takes over a minute to get them out of the db. If that was true, i can't imagen you processing them on the webserver sending them to the client and displaying them.
Can't you have some sort of condition to limit the number of retrieved records ?

[Caffeine]

That was a good explanation Raf.

When using databases, one should ALWAYS have in mind to only fetch what you need, nothing more.

There is no use to fetch say 1 million rows if you only display 100 on a page. It may work fine for you and maybe some friends of yours, but once popularity grow things will get really, really slow.

-phleg-

[NinjaTurtle]

but i found that not all SQL statment with filter out which columns we need to use only is slower than select all...

example:

SELECT * FROM ITEM (used 15sec)

but if i change it to
SELECT ITEMCODE, ITEMNAME, ITEMABC
FROM ITEM (used 18sec)

!!!!
in ITEM Table have >300 rows, and 12 fields....

Actually why the records processing time so long is bcos between the looping of items records (300 items) i have 2 more looping actions.

here are my simple source:

<%
strSQL = "SELECT fitemLevelcode,fitemLevelDesc FROM tbitemlevel where fcompanycode ='abc' and status='a'"
set RSL1 = objConn.Execute(strSQL)

if RSL1.eof then
response.write "<TABLE border=0><TR><TD>No Category.</td></tr></table>
else

counter=1
strcount1 = "SELECT count(itemcode) as numbercount1 FROM Item where itemStatus='A' AND (itemType='O' OR itemType='B') AND companycode ='abc'"
set RSCount1 = objConn.Execute(strcount1)
response.write "<div>:: Total item(s) : "&RSCount1("numbercount1") &"</div>"
Do while NOT RSL1.EOF
rscatcode = trim(RSL1("itemLevelcode"))
rscatdesc = trim(RSL1("itemLevelDesc"))
strcount = "SELECT count(itemcode) as numbercount FROM Item where itemStatus='A' AND companycode ='abc' AND (itemType='O' OR itemType='B') AND itemLevelcode='"&rscatcode&"'"
set RSCount = objConn.Execute(strcount)
response.write "<TABLE><TR><TD>"&Counter&". "&rscatdesc&RSCount("numbercount")&" item(s)</td> "& _
"<td></td></tr></table>"&vbcrlf
Response.write " <TABLE border=0 width=95%>"&_
" <tr><td>Item Code</td><td class=subtitleTD width='45%'>Item</td><td class=subtitleTD>Max. Quantity</td><td class=subtitleTD width='2%' align=center>FOC</td><td class=subtitleTD width='12%' align=center>Unit Price<br><span class=rrowtitle>"&currencycode&"</span></td><td class=subtitleTD width='15%' align=center>Quantity</td><td class=subtitleTD width='15%' align=center>Amount<BR><span class=rrowtitle>"&currencycode&"</span></td></tr>"&vbcrlf

strSQL2 = "SELECT * FROM tbItem where FitemStatus='A' AND (FitemType='O' OR FitemType='B') AND fcompanycode ='"&Session("CompanyCode")&"' AND fitemL1code='"&rscatcode&"' order by fitemcode"
set RSITEM = objConn.Execute(strSQL2)
dim rsitemcode,rsitemdesc,rsunitprice

if RSITEM.eof then
response.write "<tr><td class=browtitleTD colspan=6><img src='../images/spacer.gif' width=10 height=5><span class=rrowtitle>No item.</span></td></tr>"&vbcrlf
else

' THE MAIN ITEM LIST IS HERE
'*************************

Do while NOT RSITEM.EOF
if cccc="" then
cccc=1
end if
if mq="" then
mq=1
end if
rsitemcode = trim(RSITEM("itemcode"))
rsitemdesc = trim(RSITEM("itemdesc"))

' Check SP by customer first, secondly is by LEVEL.
strSPrice = "SELECT UnitPrice,UOM from Price where itemcode = '"&rsitemcode&"' and companycode='abc' AND Status='a' AND custcode='"&custcode&"' AND effectiveBDate<='"&todayDate&"' and effectiveEDate>='"&todayDate&"' order by itemcode"
SET rsSPrice = objConn.Execute(strSPrice)

IF NOT rsSPrice.EOF then
rsUPrice = rsSPrice("UnitPrice")
rsOrderUOM = rsSPrice("OrderUOM")
rsMaxquantity = ""
if rsUPrice=0 then
rowcolor = "bblueTD"
else
rowcolor="bgoldTD"
end if

ELSE
dim strSPriceL,rsSPriceL
strSPriceL = "SELECT UnitPrice,OrderUOM,ChargeUOM,Maxquantity from Pricewhere itemcode = '"&rsitemcode&"' and companycode='abc' AND Status='a' AND LEVELCODE='"&pricelevel&"' AND effectiveBDate<='"&todayDate&"' and effectiveEDate>='"&todayDate&"' order by itemcode"
SET rsSPriceL = objConn.Execute(strSPriceL)
IF NOT rsSPriceL.EOF then
rsUPrice = rsSPriceL("UnitPrice")
rsOrderUOM = rsSPriceL("OrderUOM")
rsChargeUOM = rsSPriceL("ChargeUOM")
rsMaxquantity = rsSPriceL("Maxquantity")
if rsUPrice=0 then
rowcolor = "bblueTD"
else
rowcolor="bgoldTD"
end if
ELSE
' If no specific Price then PRice Level will be considered.
strPriceGroup = "SELECT UnitPrice from PriceGroup where itemcode = '"&rsitemcode&"' and companycode='abc' AND effectiveDate>='"&year(todayDate)&"/"&month(todayDate)&"/"&day(todayDate)&"' AND Status='a' AND levelCode='"&pricelevel&"' order by itemcode"
SET rsPriceGroup = objConn.Execute(strPriceGroup)
IF NOT rsPriceGroup.EOF then
rsUPrice = rsPriceGroup("UnitPrice")
rsOrderUOM = rsitem("OrderUOM")
rsChargeUOM = rsitem("ChargeUOM")
rsMaxquantity = ""
if rsUPrice=0 then
rowcolor = "bblueTD"
else
rowcolor="rowtitleTD"
end if
ELSE
rowcolor="pinkTD"
rsUPrice = 0.00
rsOrderUOM = ""
rsChargeUOM = ""
rsMaxquantity = ""
END IF
END IF
END IF
end if
%>
<tr>
<td class=<%=rowcolor%>><%=rsitemcode%></td>
<td class=<%=rowcolor%>><%=rsitemdesc%><input name=cv<%=cccc%> type=hidden value="<%=CVValue%>" size="5"></td>
<td class=<%=rowcolor%> align="right"><%=rsMaxquantity%>
<%
if rsMaxquantity<>"" then
response.write "<INPUT TYPE=hidden name=Maxquantity"&mq& " value="&rsMaxquantity&"></td>"
mq=mq+1
end if
%>
<td class=<%=rowcolor%>><input name=FOC<%=cccc%> type="checkbox" value="Y"></td>
<td class=<%=rowcolor%> align=right><%=rsUPrice%>/<%=rsChargeUOM%><input type=hidden name=uom<%=cccc%> value=<%=FormatNumber(rsUPrice,2)%>><input name=ChargeUOM<%=cccc%> type="hidden" value="<%=rsChargeUOM%>">
</td>
<td class=<%=rowcolor%>>
<input type=text class=ctxtbox size=8 maxlength=5 name=qty<%=cccc%> onkeyup="<% if rsMaxquantity<>"" then response.write "checkquantity(this.form,"&mq-1&","&cccc&");"%>sumtotal(this.form,<%=cccc%>); updatetotal()" AUTOCOMPLETE="off"> <%=rsOrderUOM%>
<INPUT TYPE=hidden name=itemcode<%=cccc%> value=<%=rsitemcode%>>
<INPUT TYPE=hidden name=itemname<%=cccc%> value='<%=rsitemdesc%>'>
<INPUT TYPE=hidden name=uomorder<%=cccc%> value='<%=rsOrderUOM%>'>
</td>
<td class=<%=rowcolor%>><input type=textbox class=ctxtbox size=8 name=amt<%=cccc%> maxlength=4 value=0.00 readonly><BR>
</td>
</tr>
<%
cccc=cint(cccc)+1
RSITEM.movenext
Loop
end if
RSITEM.close
%>
</table><BR>
<%
response.write ""&vbcrlf
counter=cint(counter)+1
RSL1.movenext
Loop
end if
RSL1.close

[raf]

I unforunately have no time to read all that code and really evaluate it, but even at first look, it seems to me this is NOT a good coding and/or DB-design example.

(If you now feel offended, just tell me. If you'd like us to take a look at it and are willing to change the code and/or db-design and try to optimize it, just tell us all)

I never needed responsetimes of more then 2 seconds. Not even when i had thousands of records to query.

[Caffeine]

I don't have the time to go through the code either, but 2 things caught my attention:

1/ Many SELECT-queries
Often this is the dirty solution when the database is incorrect designed or the coders SQL-language knowledge is not good enough for the task(I mean no harm). I always try to have only one SELECT-query(maximum 2) on a page, if it's not a very complex one.

2/ Many loops and SQL-queries while databse is open
This is most likely what makes the page take time to load.
I use getRows almost everytime I fetch data from a database.
It's faster than objRS.moveNext (not always though) and it has the database open at a minimum time, compared to DO WHILE objRS.EOF which has the database open until it reaches the end.
I remember I had some problems understanding getRows in the beginning but once you understand it, you'll love it :D


-phleg-

[NinjaTurtle]

thank you Raf & phlegmatic.

urs kindly suggestion and recommendation, make me awareness that what normally i didnt aware..... really, im that kibd of ppl who desire to be better, so need more ppl to give advice.

i will try to change the SQL Statement and avoid using so many loops & ofcause MoveNext... & i will try to love getRows.. :)
bcos im very weak at SQL Stament, i only know the simple and basic to write a SQL stament, the most "advance" for me is JOIN Table.. .hahahaha... i try to found a book or web resources, i cant found any good resources with SQL and ASP... they all just talk about simple and easy(like waht i learn...). if any good web site just tell me.... TQ & TQ & TQ...

Thanks...

[raf]

Good attitude :thumbsup: Don't know any relly good tutorias. In fact, once you master joins and subquerys and agregation function, there not that much to learn any more (if were talking about Data Manapulation, that is)

I frequently look at the Access helpfunction :o It's duite good on JetSQL.

Building a good db and db-driven app has more to to with planning, analysing and restricting yourself. So it's kinda something you can only learn by building app's (each one i build is better then the previous)

Good luck!

[Roy Sinclair]

Originally posted by phlegmatic
...
2/ Many loops and SQL-queries while databse is open
This is most likely what makes the page take time to load.
I use getRows almost everytime I fetch data from a database.
It's faster than objRS.moveNext (not always though) and it has the database open at a minimum time, compared to DO WHILE objRS.EOF which has the database open until it reaches the end.
I remember I had some problems understanding getRows in the beginning but once you understand it, you'll love it :D


-phleg- [/B]

Er, Set RecordSet.ActiveConnection = nothing will disconnect the recordset from the Connection leaving you with a recordset you can continue to use while releasing the connection (which you can then close) and the database.

[Caffeine]

I was afraid my words could have been read the 'wrong way' but I'm happy to see that you read them like they were intended :)

You'll learn as time goes by, and when/if you do bad.
I managed to take down the companys intranet-server twice on my previous job. :o I certainly learned from that experience :D

-phleg-