Increase speed when retrieving data from a Hosted SQL SERVER into VBA

[Jakob Petersen]

Hi,

I need to increase the speed when retrieving data from a hosted SQL Server into VBA. I'm using simple SELECT statements.

How important is the speed of my Internet connection? (I have 4mbits)

Should I index my tables or use Stored Procedures? Or is there a kind of "flush" function or readonly function or...

Or is it simply a question of the amount of data transmitted over the Internet?

Thank you for your time and help,

Jakob Petersen

[iburyak]

Definitely indexes should be on a database.
But not just any indexes it should be carefully thought of depend on kind selections you do and database structure as well.

Stored procedures can increase some speed but not like indexes.

[Motoma]

Welcome to The Scripts.
Indexes will help you out, but it really depends on the size of the database, as well as the size of the datasets and the complexety of your query.
Perhaps if you gave us a little more information, we could assist you in speeding things ups.

[Jakob Petersen]

the database is about 30mb. The queries are quite simple - just "SELECT xx, xx FROM xx WHERE xx=yy".

I just converted my Access 2003 database, which was stored locally, to hosted SQL Server. An update which previously took about 10 seconds now takes about 2 minutes.

I tried to limit the number of rows which helped quite a lot. Then I also tried to change the format of a field from "nText" to "nVar" that also helped a lot. It seems like the amount of data transmitted is the single most important factor - even though I'm running on a 8mbit line.

Most of my data I just need in arrays - I usually don't update any tables. My thought was that it should be possible to send the query result from the SQL Server in some kind of "flat" text array format and not in a recordset format.

I'm using ADODB in VBA with code lines like:

 
Set rsData = New ADODB.Recordset
rsData.Open sSql, conGPAM, adOpenKeyset, adLockOptimistic
aData = rsSCD.GetRows(-1)
 

I have tried to change LockType and CursorType, but that doesn't really make a difference.

Any suggestions?

Thanks