Speeding up code database and sessions

[intomuso]

Hi,

I currently have a shopping basket which is fine for one item at a time. It loads fast enough. However I adapted the code so people can choose multiple items via tick box, these can sometimes number 10 or more at a time. It's very slow and can sometimes take 15 seconds to load, mostly people won't wait around that long.

I think the problem is that it's going through the database and adding each item to the session object which is making it slow, I can't seem to find a quick way around this unless I totally rewrote the basket which is actually an adaptation of someone else's.

Here's the code that adds the item to the session.

Any ideas on improving the speed would be most appreciated.

Thanks

########################################################
sub addToCart()

For counter = 1 To Request.Form("track").Count

'This gets each individual track that was sent from a form
track=Request.Form("track")(counter)

'if it's empty to nothing
If scartItem < maxCartItems Then
scartItem = scartItem + 1
End If


Session("cartItem") = scartItem

dim rs, sqlProductInfo
'get the details for each track id

sqlProductInfo="SELECT * table1 RIGHT JOIN table2"
sqlProductInfo=sqlProductInfo & " ON table1.ID=table2.ID"
sqlProductInfo=sqlProductInfo & " WHERE Table2.ID=" & track & ""

'open connection - returns dbc as Active connection
call openConn()
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sqlProductInfo, dbc, adOpenForwardOnly,adLockReadOnly,adCmdText
If Not rs.EOF Then
'add the track to the session cookie
arrCart(cProductid,scartItem) = track
arrCart(cProductCode,scartItem) = rs("ID")
arrCart(cProductname,scartItem) = rs("nameview")
arrCart(cdescription,scartItem)= rs("Name")
arrCart(cQuantity,scartItem) = CInt(quantity)
arrCart(cUnitPrice,scartItem) = rs("price")
arrCart(cPostageUK,scartItem) = "0"
'arrCart(cPostageworld,scartItem) = rs("cpostageworld")



Session("MyCart") = arrCart
End If

next


call closeConn()

end sub

 

[onpnt]

Best place you will improve your performance is in the SQL itself and the methods used to open your recoedset.

Look into the .Execute method over the Open.

Also, inheritantly using the * wild card is bad form and will slow the query down

There is a thread from a long time ago where Tarwn did benchmarks on RS methods etc.. I'll try and track it down and post the link. It was very revealing on performance with RS's

 

[onpnt]

I just realized you are looping through all that.

1) you are opening the connection every iteration. Don't! Open it prior to going into the loop

2) For Next is the oldest and worst performing loop there is. Do While would probably gain you some speed.

3) you are using concatenation in you SQL string build where it really isn't needed. Concatenation although something not really perceived as a performance hit can be. So don't use it unless you are actually building in conditional values (var's etc...)

sqlProductInfo="SELECT * table1 RIGHT JOIN table2" & _
                  " ON table1.ID=table2.ID" & _
                  " WHERE Table2.ID=" & track & ""

all little things but together can make your code better performing and even more maintainable

 

[Genimuse]

The whole thing can be pulled back in one recordset, too, by just substituting some ORs to assemble your WHERE clause, a la

[COLOR=green]'Request.Form("track") will return a comma-delimited list
'so split it at the commas and rejoin with ORs[/color]
strWhereClause = Join(Split(Request.Form("track"), ","), " OR Table2.ID=")
[COLOR=green]'Tack on the first part of the clause[/color]
strWhereClause = " WHERE Table2.ID=" & strWhereClause
[COLOR=green]'Create the sql[/color]
sqlProductInfo="SELECT * table1 RIGHT JOIN table2 ON table1.ID=table2.ID" & strWhereClause

Instead of the split/join, if your database supports it you do this instead (probably even faster):

strWhereClause = " WHERE Table2.ID IN (" & Request.Form("track") & ")"

Note that everything -- the entire cart procedure -- should be wrapped in "If Request.Form("track").count > 0 Then" now instead of your loop.

Then either use GetRows to pull back the results at once and loop throug the array (preferred) or use "While Not rs.Eof" for your loop and "rs.MoveNext" inside the loop. You can assign all your session stuff there, too.

End result of onpnt's suggestions and mine: one connection, one recordset, one concatenation, tighter SQL if you specify the columns you want back instead of using *, and if you use GetRows, a connection that's only open for a fraction of a second. End result: way, way, way faster.

 

[Tarwn]

Additionally you are assigning the session variable holding your cart on every single loop. This could be done one time when your loops are completed, since your storing all of this data in a single array.

 

[intomuso]

Hi Thanks for all your help,

I've taken on and re coded using all the tips, and as with genimuse's suggestion I've got the id in the query. It's quicker but the problem is it's not letting me add the details to the session cookie. All I get is the last record added. I can see from the sql that it's looping through and getting records for all the ID's it just won't add to the below session one at a time??

'#####################################################

Session("cartItem") = scartItem
rs.open sqlProductInfo,objConn4
rs.movefirst
do while Not rs.EOF

arrCart(cProductid,scartItem) = rsItem("ID")
arrCart(cProductCode,scartItem) = rsItem("ArtID")
arrCart(cProductname,scartItem) = rsItem("Songnameview")
arrCart(cdescription,scartItem)= rsItem("ArtistName")
arrCart(cQuantity,scartItem) = CInt(quantity)
arrCart(cUnitPrice,scartItem) = rsItem("price")
arrCart(cPostageUK,scartItem) = "0"
'arrCart(cPostageworld,scartItem) = rsItem("cpostageworld")




response.write rsItem("ID")
I can see it's get the id's sequentially from the database, but not adding to the cart
Session("MyCart") = arrCart
rs.movenext
response.write "<br>"

loop

'#########################################################
what am I missing

thanks a lot

gavin

 

[intomuso]

Hi,

I'm posting this as a continuation of earlier post below, where I haven't found a solution yet. Suggestions from some guys have improved the speed, but I can't make the session cart add, items to the cart in a loop. for some reason my original code although painfully slow, around 15 seconds, it works and add mutiple items. The code below is fast but won't add a list of items to the cart.

Can anyone explain what might be wrong.
I can see from the sql that it's looping through and getting records for all the ID's it just won't add to the below session one at a time??

'#####################################################

Session("cartItem") = scartItem
rs.open sqlProductInfo,objConn4
rs.movefirst
do while Not rs.EOF

arrCart(cProductid,scartItem) = rsItem("ID")
arrCart(cProductCode,scartItem) = rsItem("ArtID")
arrCart(cProductname,scartItem) = rsItem("Songnameview")
arrCart(cdescription,scartItem)= rsItem("ArtistName")
arrCart(cQuantity,scartItem) = CInt(quantity)
arrCart(cUnitPrice,scartItem) = rsItem("price")
arrCart(cPostageUK,scartItem) = "0"
'arrCart(cPostageworld,scartItem) = rsItem("cpostageworld")




response.write rsItem("ID")
I can see it's get the id's sequentially from the database, but not adding to the cart
Session("MyCart") = arrCart
rs.movenext
response.write "<br>"

loop

previous post -
speeding up code database and sessions.

http://www.tek-tips.com/viewthread.cfm?qid=1184538&page=2

 

[Tarwn]

In your original code you use a counter to assign values to your array. I don't see that counter being incremented in this loop like it was in your original code, so I think your probably overwriting the same entry (the initial scartitem value) over and over.

Also, it will be incredibly faster if you use theGetRows() function rather then pull the data out of your recordset object in that manner. In fact, you could even use a little trickery in your SQL statement to create a couple blank columns for quantity and postage, then add those values to the returned array and store that in your session variable.
Assuming Genimuses SQL worked for you, since you haven't posted back what your working with, you could do something like this:

strWhereClause = " WHERE Table2.ID=" & Join(Split(Request.Form("track"), ","), " OR Table2.ID=")
'Create the sql
sqlProductInfo="SELECT [highlight]Table1.ID, ArtID, Songnameview, ArtistName, 0 as quantity, price, 0 as postage, cpostageworld[/highlight] FROM table1 RIGHT JOIN table2 ON table1.ID=table2.ID " & strWhereClause

call openConn()
Set rs = objConn4.Execute(sqlProductInfo) 'not sure if I got the connection object name correct

'get the data array
Dim arrCart
arrCart = rs.GetRows()

'optional - assign some variables for the array indexes 
'   these line up to their indexes in the array and will make it easier to write and read the code that references items in the array
CONST FLD_ID = 0
CONST FLD_ARTID = 1
CONST FLD_SONGNAMEVIEW = 2
CONST FLD_ARTISTNAME = 3
CONST FLD_QUANTITY = 4
CONST FLD_PRICE = 5
CONST FLD_POSTAGEUK = 6
CONST FLD_POSTAGEWORLD = 7

'close out and clean up the recordset and connection, they aren't needed anymore
Set rs = Nothing
objConn4.Close
Set objConn4 = Nothing

'loop through to add in the quantities for each row - not sure where your getting these, your original code is assigning the same quantity ot every row
Dim rowCtr
For rowCtr = 0 to UBound(arrCart,2)
   arrCart(FLD_QUANTITY,rowCtr) = cInt(quantity)  'again, not sure where this quantity variable came from
Next

Session("MyCart") = arrCart

'test display of the data - this will display horizantally
Dim i, j
Response.Write "<table>"
For i = 0 to UBound(arrCart,1)
   Response.Write "<tr>"
   For j = 0 to UBound(arrCart,2)
      Response.Write "<td>" & arrCart(i,j) & "</td>"
   Next
   Response.Write "</tr>"
Next
Response.Write "</table>"

Hope at least the first part of the post helps. If you try the second part be aware that I have written C#, PHP, and a little Perl this month (but no ASP), so my ASP syntax may be a bit off

-T

 

[intomuso]

Hi

This is so frustrating, I used your code above and the response.write was producing the correct information collected from the database. However it's being subborn and won't add it to the cart, the cart just displays an empty field so nothing is adding to the cart.

is there anything missing? maybe it's not possible to add mulitiple things to the cart without closing and adding individually, I don't know, I'm near the brink of giving up and employing someone who unlike me might know what their doing.

gavin

 

[Tarwn]

Sory for the delay in geting back to you, been tied up the last few days and don't post as often as I used to.

It's odd that the value is not there. Try printing out your Session.SessionID at the top of every page. If this value changes as you go from one page to the next then it means that your browser is not storing the SessionID cookie that the server gives you when you first hit the site.

Also I just realized that the code above will work to fill the cart from scratch, but will not work to add additional items to the cart (as you would be overriding your previous array when you did). I have some ideas on that, but we need to clear up your session difficulties first (and I need to go shower and head towards work).

-T