Access Backend DB (97)

Topic

New Reply

Hi,

I’ve used an Access 97 database as a backend to an intraweb, accessed via an ASP page using ADO.

It hasn’t gone live yet, but I’m concerned about the performance implications.

I’m not using any of the Access front-end features.

Some of the queries will be hitting the DB fairly hard.

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.

I would guess I have the option of going mySQL; we have an Oracle site licence as well, but I don’t know if we can go that path in the short term.

Reply | Quote

Replies

It may be an optimization issue. Are the queries saved queries or SQL?

Reply | Quote

They are SQL.

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

There might be some ASP issues as well- I may have to see if I can connect once, and leave the connection open for the session.

Reply | Quote

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.

Reply | Quote

Thanks Charlotte. I’m checking out the stored queries. I’ve used them in Oracle, but this is my first venture in Access.

Thanks for your help.

Reply | Quote

Sure. I’ll drop on this one…

.mdb does not scale. Is that safe to say? Which SQL product really does not matter these days, is suppose, as long as it’s DB2, SQL Server or Oracle. Sybase has (what’s it called?).

Write stored procedures on the SQL server for the most optimized retrievals.

Reply | Quote

[indent]

---

Sybase has (what’s it called?).

---

[/indent]It’s called Sybase SQL Server. They licensed the original product to Microsoft.

Reply | Quote

I remember that connection now. I believe Ashton-Tate (makers of the famed dBase) was also in on the original release of SQL Server?

Reply | Quote

I don’t recall that connection, but then I’ve only worked with SQL Server since 6.5.

Reply | Quote

> 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.

Hope this helps,

Simon

Reply | Quote

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?

Thanks

Reply | Quote

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.

Reply | Quote

[indent]

---

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.

---

[/indent]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

Reply | Quote