slow connection speed on FIRST try

[tchristo]

Hello all,

I've been reading all the info on the site about increasing the speed for displaying query results. Testing speeds AFTER the first time accessing a DB for all 3 connection types (listed below) seem about the same for speed. Also, the recordset.open versus recordset.execute seem about the same for speed.

HOWEVER,when the db is FIRST queried, using any of these methods, the speed slows significantly. So the intial speed may be as much as 1 min to timing out after 2min,30 sec whereas after the initial try it will only take 2-8 sec depending on the database.

What is going on?! Why so much slower on the first try and not when I query the same db within about 5min of the first attempt?

connection strings that I tried out are listed below (generic names substitute the actual web address and db names)

''''dsn connection
set cn = Server.CreateObject("ADODB.connection")
cn.connectiontimeout = 0
dim strCn
'strCn = "DSN=mydata"
'cn.ConnectionString = strCn
'cn.open

'''dsn-less connection
'cn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
' "Server=mywebaddress;" & _
' "DBQ=" & Server.MapPath("data\mydb.mdb")

'''ole jet (dsn-less) connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\web\data\mydb.mdb;

 

[Sheco]

Perhaps the results are cached or a temp index still exists on subsequent re-queries. This can happen on "big" databases but I don't know if MS Access does it.

 

[Sheco]

Is it slow for everything or just a specific query? If you do a little tiny query that only returns one row by key does the same thing happen?

What I'm getting at is if the problem is in the ADO connection or in the SQL command?

 

[tchristo]

Thanks for the ?'s Sheco.

It is a very big database (actually a GIS geodatabase) so it contains thousand of records for each query. Still, if I query one county the first time it will take a while (1min+). But then I can change the county name so it will be looking up different records and still be really quick the second time (so not just the same exact query being cached somehow).

 

[Sheco]

Oh, by "big" I was actually meaning one of the full scale DBMS platforms like Oracle, MS SQL Server, or IBM DB2.

It sounds like you have a lot of records in a MS Access database so it is big in terms of number of records. Is that right?

My guess is that the first time you call query it, it is taking a long time to copy the database into system memory and that subsequent calls are faster because it is working from memory instead of from disk.

The first thing I would do is to make sure that I have good indexes on the fields that I was using for common queries.

Other possibilities include breaking off only the part of the database that you need into its own separate .MDB file or moving the entire thing into a DBMS instead of Access.

If cost is an issue you can use the free MSDE from Microsoft... it is the SQL Server engine but without the snazzy Enterprise Manager and assorted tools. It also has a 2GB limit just like Access but, other than that, it is basically the same as SQL Server. You can connect to it and manage it from within Access as a "Project." It has much better indexing capabilities and also you can put your queries into stored procedures so they run that much better.

 

[tchristo]

Thanks for the suggestions. I'll take a look at the indexing and see if that will help. I've just begun experimenting with DB2 actually but the maintenance gets complicated because these are spatial databases that I'm working on. I'll test out the speeds on it though and see if there is a big improvement.