Database connection/speed optimization with Access

[CHeighlund]

I am working on a program designed to draw information from an Access database. Originally, for testing purposes I was working with the data on a PostgreSQL database, using Zeos components to connect to the db. On heading to the Access db, I swapped over to adoQuery components. The maximum run (hit every point of the database) that used to take half an hour with the PostgreSQL version is now going strong over an hour after being started on the Access db.

This is going to be run against multiple copies of the Access database. I -do not- have the ability to modify the databases on the end-users' systems. What options short of eliminating code do I have for attempting to speed up the connection/transfer process here?

 

[Griffyn]

I don't have any known tips for you, but one thing that I thought of would be changing from client side cursors to server side (CursorLocation property). You could also try changing the CursorType property - Forward and Static cursors should be faster than the others.

 

[AP81]

PostgreSQL and Access behave very differently, i.e. transaction locking, join optimizations etc.

One thing to investigate is whether you are saving the result of each query. Make sure that INSERT,DELETE,DROP,TRUNCATE and any other command which does not need a result set are simply executed (I think ADO has an ExecSQL function). This will makes things faster with Access.

Also, revisit your SQL and check whether your joins are optimized. Also limit your queries to only return the columns you want (not select *).

 

[harebrain]

Make sure that INSERT,DELETE,DROP,TRUNCATE and any other command which does not need a result set are simply executed

You'd do that with the ADOCommand component; forget ExecSQL.

Some questions need answering:

- What version of Access? The old JET engine, or the stripped-down SQL Server engine?

- Is there a network involved, or is the local machine also the database engine?

Really, this setup should blaze, so I'm guessing we're not getting the whole story. Why on earth would you design for one db engine, then port to another for your software release?

 

[AP81]

My bad, it has been a long time since I last used Access (Thank God).

 

[roo0047]

The speed-bump is definitely Access. But try doing your conversion as a {$APPTYPE CONSOLE}. The speed difference will astound you.

Roo
Delphi Rules!