Re: Access Backend DB (97)

Well, Access (and ADO from what I've seen) prefers to handle optimized queries, which mean saved queries will behave better than SQL. In Access 2000, there's a major and visible difference in performance between saved queries and SQL due to advance optimization, but I don't know how much of that is due to ADO and how much to the query engine itself.

The first time you run a SQL query, Jet optimizes it, which can take some time, especially if you have a website involved I imagine. After that, it caches the query so it's faster next time. Of course, if you close the database or, in this case, approach it from a different machine, I imagine that it will have to optimze the SQL all over again. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Re: Access Backend DB (97)

> Would they be better as saved queries?
>Or is there a time lag inherent with loading Access (or any DB) first time around?

Probably and yes. If there are no issues in saving the queries then do so. If the query is built up at run-time because its functionality depends on various other factors that can only be determined at run-time then it becomes a balancing act. Do you complicate the query so it can cater for the various situations; do you create multiple queries; or is it easier to leave things as they are?

There is definitely an extra time lag before the first access to an Access DB through ADO which has nothing to do with compiled or uncompiled queries - it happens even if the Access DB contains nothing more than tables which you attempt to access, as tables, through ADO. I don't imagine there's anything that can be done about it, but someone may know of a way of minimising it.

Re: Access Backend DB (97)

Simon,

The queries are easy to store, with a simple parameter. So it's quite easy to make stored queries. I just didn't realise that the quesries i've previously stored for adhoc enquiries could be stored and parameterised. I've had little contact with Access- but I know enough about databases for using it to be quite intuitive and easy.

Would that time lag through ADO be as pronounced with another DBMS? (Or am I asking the wrong forum?) Is there much difference between DAO and ADO?

Re: Access Backend DB (97)

<hr>I've noticed quite a lag when I first access the DB, but in subsequent accesses, it performs OK. I don't know if this is an Access thing, a ADO thing, or something else.<hr>

I am reading a fascinating book called ADO 2.6 Programmer's Reference, from Wrox. Says that by default ADO uses a technique called "connection pooling," whereby closing a connection and then opening a new connection within a certain period of time using the identical connection string will re-use the previous connection (although it is a new connection as far as your code is concerned). This would explain why subsequent connections are much faster

Re: Access Backend DB (97)

I think the time lag is going to depend not so much on the DBMS (assuming it isn't a database server) as on the kind of connection you're using. If you use a database server, then you should be able to use server-side cursors and connection pooling with ADO, which would speed it up. Of course, you can also create a connection as a global object and then keep reusing that connection. It isn't quite pooling, but it does give you a slight performance improvement. If you use ODBC drivers and DAO, then you can't do much to speed the connection up, but a database server will still return records faster than Access.

If Jet is in the picture for the queries, then you can't expect much speed improvement in either case, because the Jet query engine is comparatively slow and clunky.