Excel-SQL -> Converting recordset to a variant array. GetRows() very slow.

[RawlinsCross]

I have a function where you pass through a SQL database table name and an optional string query (if non supplied the query becomes the 'Select *' from the table). I want to pass the returning record set to a variant array. When attempting the code, it all works fine but it's awfully slow. So I stepped through the code and found that the "rs.Open ....." line is quite fast, but the "rs.GetRows()" line is very very slow. Is there a faster alternative?

Public Function GetSQLTable(sTable As String, Optional sSuppliedQuery As String) As Variant

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Dim i As Long
Dim ListObj As ListObject
Dim vTable As Variant
Dim sQuery As String

Set rs = New ADODB.Recordset

If sSuppliedQuery = "" Then
    sQuery = "Select * From " & sTable
Else
    sQuery = sSuppliedQuery
End If

Server_Name = "ServerName" 
Database_Name = "DBName" 
User_ID = "USERID" 
Password = "PASSWORD123" ' not the real password  ;)
SQLStr = sQuery

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic '<- this step is fast

vTable = rs.GetRows()  '<- growing old at this step

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

GetSQLTable = vTable

End Function

 

[smozgur]

Although returning all records from a table, especially from a remote database is not the best thing to do, I understand that it might be necessary. If you can't limit the returned data rows, and absolutely need to fetch all records, then perhaps setting the CursorLocation at the client-side (before opening the recordset - rs.Open) instead of using the default server-side setting (which is adUseServer) might help a bit.

rs.CursorLocation = adUseClient

And actually, I don't think you need to open the recordset as static. Using cursor type as forward-only might be faster.

rs.Open SQLStr, Cn, adOpenForwardOnly

 

[RawlinsCross]

Hi Smozgur,

Thanks for the tips, I'll do some speed-tests and report back.

 

[RawlinsCross]

Smozgur,

Apologize for the delay. So I did four tests. I'm glad Test#2 won out as the RangeToArray option (see below) was a bit obtuse. I'm thrilled (but not over surprised) on the difference between Test#1 and Test#2 - do you have any references I can read to explain such a marked improvment? Again thanks for the head's up!

Test #1 - Using rs.GetRows, rs.CursorLocation = adUseServer (default), CursorType = adOpenStatic
Execution Time (avg) = 78 s
Test #2 - Using rs.GetRows, rs.CursorLocation = adUseClient, CursorType = adOpenForwardOnly
Execution Time (avg) = 0.25 s

Before your email, I had (in desperation) jettisoned using the .GetRows method and opted instead to dump the recordset to a range and then assigned that range to a variant array

Test #3 - Using RangeToArray, rs.CursorLocation = adUseServer (default), CursorType = adOpenStatic
Execution Time (avg) = 0.71 s
Test #4 - Using RangeToArray, rs.CursorLocation = adUseClient, CursorType = adOpenForwardOnly
Execution Time (avg) = 0.32 s

 

[smozgur]

The adUseClient option downloads all recordset from the server at once instead of making multiple visits to the server. That's why GetRows is supposed to be running much faster with it. However, this will also cause slowness if you have a really large data set to bring all data from the server at once even though it will be much faster to browse in the recordset after loading it.

Note: Actually, adOpenForwardOnly shouldn't affect when you use adUseClient since adUseClient will only use adOpenStatic (even you set it to otherwise).

And if you are not going to update/delete any records from the recordset, then you can also add adLockReadOnly to avoid record level locking at the server-side which will avoid other connections to be waiting while you are working with it and until you close the recordset.

There are really many options for different scenarios, that's why you should decide the best one according to your application/usage. I would benchmark the following settings in your situation:

rs.CursorLocation=adUseServer
rs.CursorType=adOpenForwardOnly
rs.LockType=adLockReadOnly

and

rs.CursorLocation=adUseClient
rs.CursorType=adOpenStatic
rs.LockType=adLockReadOnly

The additional best approach would be using Range("A1").CopyFromRecordset rs if you are going to retrieve the whole data into the worksheet by the way. From my experience, this would be the fastest method.

One more suggestion, always request for the necessary fields/columns instead of "Select * From" since it will directly and dramatically affect the recordset loading. This way, adUseClient might be even faster than using adUseServer in different scenarios. I am sure there are many fields that you wouldn't need in Excel, so just create your query with necessary fields like "Select field1, field2, field4 From", this way you can also structure your range as you want instead of cleaning it after loading the whole data.

Long time I didn't work with ADODB, however, I worked with it much enough to learn it might be a real pain to exchange data between the server and the application without properly setting these options for each different case (usage, server, data amount, etc.,).

I hope this helps you a bit.

 

[RawlinsCross]

Great... thanks for all the help. I'll definitely look more into the technical aspects you have mentioned.

 

[jacksonmoore457]

Was excited when I saw that RawlinsCross had such a sharp decrease in time but I don't get close to the same improvement. I get a subset of my data that is about 7k rows, and 5 columns. Converting to an array with the different methods mentioned gets between 10 - 15 seconds (rs.Open and array = rs.GetRows combined) and using copyFromRecordset is actually the fastest at 7 seconds. So maybe he was only counting the getRows function which is < 1s with client but then opening is slow and then vice versa.