Optimize speed of Hosted SQL Server

Hi,

I'm retrieving data in VBA using simple SELECT statements on my Hosted SQL Server. How do I most effectively speed up the process?

Is it just about data amount, traffic, and speed of Internet conncetion?

Or should I use Stored Procedures or should I index tables?

Is there a function to kind of "flush" the data to the VBA program as it is all READ-ONLY?

Any help is much appreciated,

Jakob

Can you give more information about how your database schema looks like, what type of queries you are doing, and how your network architecture looks like?

Based on that it might be easier to answer your questions.

Thanks,

Marcel van der Holst
[MSFT]|||

The queries are very simple, just simple "SELECT xx, xx, xx FROM xx WHERE xx=xx"

I'm connecting from home with my lapto to a hosted SQL Server, so no big network. I have tried testing my code to see how and when it is slow. It it quite fast to open the connection, but when I try to through the records using for example the rs.movenext function I can see that it takes a second or two each time for each record and each field in each record. It seems like I'm maintaining an open connection over the internet when I actually just want to the data read-only.

Does that help?

Jakob

|||Would it be possible to run the queries against a local SQL Server that runs on your laptop. That way, you can ignore the network latency, and see if the query is causing the problem.

If you use RecordSet, it will keep the connection open until you explicitely close it. Even when you close it, connection pooling will be used to keep the connection open a while, just in case you need it again.

How much data do you select, i.e. how big is the data in the select xx,xx statement? if that data is big, and you have to go over a big network, it might cause the slowdown..

Another thing to consider is to retrieve all the data you need in a big select statement (select * from Table), and then store this data in a local cache, and do the searching and filtering locally, instead of doing it over the internet.

Thanks,

Marcel van der Holst
[MSFT]|||

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 = rsData.GetRows(.1)

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

Any suggestions?
|||

ADO is horrible for direct access. Use sprocs. Use output parameters if guaranteed to return just one row or if you only need a single output (like a count).

Check the network latency to your host.

Appropriate indexing is paramount for optimal perfomance. This is somewhat science and somewhat art/experience.