By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,668 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,668 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Feb 6 '07 #1
Share this Question
Share on Google+
3 Replies


iburyak
Expert 100+
P: 1,017
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

Motoma
Expert 2.5K+
P: 3,235
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

P: 2
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
  1.  
  2. Set rsData = New ADODB.Recordset
  3. rsData.Open sSql, conGPAM, adOpenKeyset, adLockOptimistic
  4. aData = rsSCD.GetRows(-1)
  5.  
I have tried to change LockType and CursorType, but that doesn't really make a difference.

Any suggestions?

Thanks
Feb 7 '07 #4

Post your reply

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