Performance difference between CursorLocation=adUseClient and GetRows?

[dirkg]

Hi all,

I'm optimizing my asp-application with a SQL Server 2000 database. I was wondering whether there's a difference between opening a recordset with rst.CursorLocation = adUseClient


Set rst = Server.CreateObject("ADODB.Recordset&quot
rst.CursorLocation = adUseClient
rst.Open strSQL,objConn
rst.ActiveConnection = Nothing
objConn.Close
Set objConn = Nothing
Set objCmd = Nothing

Do until rst.eof
.....
Loop

rst.close
set rst = nothing
where I can close the connection before starting to iterate through the recordset but the recordset remains

and using the getrows

Set rst = Server.CreateObject("ADODB.Recordset&quot
rst.CursorLocation = adUseClient
rst.Open strSQL,objConn
alldata= rst.getrows
rst.close
set rst = Nothing
objConn.Close
Set objConn = Nothing
Set objCmd = Nothing

numrows=ubound(alldata,2)
FOR rowcounter= 0 TO numrows
....
NEXT


where I can immedeately close the recordset and the data are loaded in an array.

I'm hence wondering what the difference is between the two and which one will give me the best performance? (my database is still to small to see an actual difference but will grow soon in size and number of users)


Thanks a lot in advance.

Greetz,

Dirk

 

[Tarwn]

Ok, thanks for reminding me I forgot to post a benchmark last wek, I am going to post in in a tip thrad in a few minutes, so keep an eye out

For your own information you will want to compare Test2a and Test2b against Test2c and Test2d, I'll explain in the other thread.

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

 

[baddos]

GetRows() should be faster because ADO doesn't have to check with every access if it's EOF or not.

 

[Tarwn]

Curosor location adUseClient only means allow the client to choose the cursor they are going to use, so comparing that to GetRows is not actually going to work because it depends on whether your use the dynamic cursor (which acts in the way baddos has mentioned), the keyset cursor (which is the half-****ed version of dynamic and a little faster), the statis cursor (which only executes the query once but can be used to send back updates), or the readonly cursor which only allows static reading (and is the fastest of the four).

The true comparison for GetRows is comparing GetRows to looping through the recordset pulling values out of the fields. The comparison here being whether it is faster to use GetRows to put it in an array and then loop through that, or to use the ADO recordset object to loop through it's internal collection of ADO Record objects.

Note: BTW, didn't notice it before, but you are using adClientCursor in both your examples above

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

 

[Tarwn]

Correction to my previous post, that should have been Forward Only not Read Only...thats three times I got languages mixed up today, darnit...I must be distracted or something..

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting