471,122 Members | 1,008 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,122 software developers and data experts.

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


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
Feb 6 '07 #1
3 3480
1,017 Expert 512MB
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.
Feb 6 '07 #2
3,237 Expert 2GB
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.
Feb 6 '07 #3
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:

Expand|Select|Wrap|Line Numbers
  2. Set rsData = New ADODB.Recordset
  3. rsData.Open sSql, conGPAM, adOpenKeyset, adLockOptimistic
  4. aData = rsSCD.GetRows(-1)
I have tried to change LockType and CursorType, but that doesn't really make a difference.

Any suggestions?

Feb 7 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by WindAndWaves | last post: by
60 posts views Thread by Neil | last post: by
3 posts views Thread by Mike Kelly | last post: by
10 posts views Thread by Devang | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.