Using Access as a front end, having horrible response times

[pheffley]

I'm experiencing very high response times using Access to Access versus Access to DB2(00:00:03 vs. 00:03:00). I would like to use Explain (or some other analyzer), but I don't know how to 'grab' the formatted query before it hits the database. Explain won't accept the original Access query and there are several, so formatting manually is not an option. We are using DB2 Connect Version 8, DB2 Version 6, and Access 2000.

Any ideas?

Thanks,

Paul

 

[blom0344]

What type of query are you running? Are these Select queries, or are you trying to update data in DB2.

Can you test and go for the 'pass-through' query option in Access. This should automatically create the correct syntax for DB2.

If you need a hand in re-writing the SQL syntax for DB2, than let me know

T. Blom
Information analyst
tbl@shimano-eu.com

 

[pheffley]

They are Select queries. The queries are running but we're having response time problems. Here's the story...I work for a State Agency in Oklahoma. We have an Access system that is used by people in every county of the state. This system is updated each week with data from our legacy system. They currently extract using Focus and distribute files over the network every Monday. This has become a problem because of network traffic and reliability.

A quick fix was initiated to move the data to DB2 and point the queries to the linked DB2 tables rather than the Access tables. Nobody has ever tried to normalize these tables and there are no primary keys established (in the old system). When we moved the data to DB2, we defined primary keys and are in the process of defining indexes. The major problem is that the old system is very fast in comparison to the new DB2 back-end. DB2 has some query analysis tools but they require the DB2 syntax. I can convert them, but as I mentioned there are several. I would like to be able to intercept the statement before it is processed in DB2, but can't figure out how....

Hope I haven't confused you.

Paul

 

[blom0344]

You seem to have everything you need, don't you?

1. You can rewrite the syntax to DB2.
2. You can do an explain plan and get the response time of the query, and look how the optimizer handles the query.

If you want to get a snapshot on the query that takes too much time, then download the following freeware tool:

http://chuzhoi_files.tripod.com

You can easily trap the syntax during run-time of the query, it works like a charm

Query performance on DB2 should beat Access hands-down on most occasions if you have a properly indexed database with statistics kept up to date (and no issue with the connection to the database)

T. Blom
Information analyst
tbl@shimano-eu.com