May 21, 2007 at 8:38 am
I'm on a SQL 2000 backend, using ADO in classic ASP. I recently discovered a performance problem in some code; a script was executing two queries, the first was a recordset of items, and the second was a recordset of assigned items. It was looping through the first set, then looping through the second set to create a select box and mark which were "selecte". The second recordset was using .movefirst, and because it was a forward only recordset, it was reexecuting the query each time it moved back to the beginning, slowing things down.
I fixed this by changing it to use a static cursor, which improved things immensely. However, another problem surfaced soon afterwards (somewhat related...well, the problem I don't think was related to that, but it possibly exposed it) related to the query creating a cursor on the server and processing through that, as described on this page: http://www.sqlteam.com/item.asp?ItemID=11842
So, which is the better way to do things, using the default server side cursor, or using a client side cursor? Performance seems to be fine now using the default server side, but I wanted to check and see what the correct way to do things is. I can post code if needed; the gist is the two-query scenario: looping through one recordset from beginning to end, and for each of those items checking a second recordset to see if they match when building a select box or checkboxes. Thanks for any info or advice!
May 22, 2007 at 3:17 am
Why not change the "loop within a loop" style operation to be a left join...across the 2 tables...and do the action all in one go?
May 22, 2007 at 6:29 am
Andrew is right in that the best performance option will be to try to combine the recordsets before you bring them back using a join operation on the SQL Server. Barring that, here we go...
If you intend to use an entire recordset more than once (loop through it several times), a client-side, static cursor is probably going to be faster because it will be in local memory and you do not need to make additional trips to the database. This assumes that you have enough memory and since you are in an ASP environment, you may have a problem with memory if enough users are connected at once. It is also important to note that if you are looping in ADO, the order in which you data is in the recordset will have a huge impact on finding it. If you do repeated find operations, not hainvg to MoveFirst each time will help a great deal.
If you intend to use only part of a recordset, have limited memory on the client, or only intend to stream through a recordset once, server-side is probably going to be better. Even if you use the entire recordset and loop through it one time, a server-side cursor will stream it a portion at a time getting you the first record faster so you can start processing.
So, in your case, you should probably have one of each for what you have described and the second recordset should be in the correct order to help your searches.
It is probably important to note that the ADO objects you should be using are the Hierarchial data sets (Data Shapes) that allow you to bring back multiple recordets that are essentially already joined. Here is an article: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6512
May 22, 2007 at 7:07 am
Ahhhh, thanks! I hadn't thought about trying to do it all in a join....I'm working on inherited code here :-). I'll try and compare the performance between cursors and see how it goes. Basically the logic is the first recordset returns the entire ordered set of selectable items (Projects for instance), and the second recordset returns the set of "assigned projects". It then goes through and compares them to build the controls on the form. Thanks for the info and the article, and I'll see what I can do!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply