Query: Improve Performance (2002)

Topic

New Reply

I’m using Access 2002 (10.2627.3501)SP-1

I’ve been testing an ODBC driver to pull records from a DOS database.

If I create a standard select query, and use all records from a table, the query when run is quite quick.

If I create relationships linking two or more tables and then create a select query, the performance is terrible.
The main table “EST” contains about 18000 records, from this, pulling a query with a date range takes forever.

Has anyone any solutions on improving speed with the query ?
Compact and repair doesn’t really improve much at all.

Reply | Quote

Replies

Would it be practicable to import the table(s) from the DOS database into Access first, and run the query on the imported tables? Or have you already imported the tables?

Reply | Quote

My guess is that you can’t do much. Let’s say you have Customer and Invoice tables, and the common field is CustomerNo. I’m guessing that Access doesn’t see a CustomerNo index on the Invoice table, so linking the 2 tables is very infefficient. It would almost seem easier to import the Invoice table into a temp table in Access, where you can control the indexes.

Reply | Quote

My guess is that you can’t do much. Let’s say you have Customer and Invoice tables, and the common field is CustomerNo. I’m guessing that Access doesn’t see a CustomerNo index on the Invoice table, so linking the 2 tables is very infefficient. It would almost seem easier to import the Invoice table into a temp table in Access, where you can control the indexes.

Reply | Quote

In point of fact this often happens with ODBC linked tables, and is largely a function of the ODBC driver. The problem comes when you start joining tables, either entirely in Access or partially in Access and partially in the external database. When you have those situations, Access brings in the entire table(s) so it can do the join internally – if it is just a single table, the ODBC driver is usually “smart” enough to get just the data you need and deliver it to Access. (The SQL Server ODBC driver is smart enough that it will often run two or three table joins where all the tables are in SQL Server in the database engine, but try it with two tables in SQL Server and one in Access and the wheels fall off.)

In your case I agree both with Hans and Mark – bringing the entire set of data in on a temporary or periodic basis would be the best solution, OR you could simply rewrite the application that creates and uses the data in Access! [evingrin]

Reply | Quote

Ok well thankyou all for the wise words.

The tables are linked at the moment, so answering all your guess’s.
I’ll do some testing to see which is the best method.
If I created the relationships now, will those relationships still be present once imported. ?

Reply | Quote

You will have to create the relationships anew after importing the data, since you will have to delete the links before importing the tables.

Added: if you want, you can import, create indexes and relationships in code using DAO.

Reply | Quote

Hmmmmmmmmm

Looks like this needs to be done with VBA ?

Reply | Quote

Hmmmmmmmmm

Looks like this needs to be done with VBA ?

Reply | Quote

You can type the following keywords in the Visual Basic Editor (in a module or in the Immediate window) and press F1 to get help on them:

TransferDatabase to import a table.
CreateIndex to create a new index on a table.
CreateRelation to create a new relationship between two tables.

The latter two require that you have set a reference to the Microsoft DAO 3.6 Object Library.

Reply | Quote

Ok Hans.
Thanks, Thats what I’ll do.

Reply | Quote

Ok Hans.
Thanks, Thats what I’ll do.

Reply | Quote

Hans

Wouldn’t it be easier to run a make table query on a splash and then a delete query on closing the db ?

Reply | Quote

Hi Dave,

This would only be possible if your database will be never used by more than one user simultaneously. If there are multiple users, it would result in chaos.

How often do the data in the external database change? Perhaps it would be enough to refresh the data once a day, or once a week.

Reply | Quote

Hans

The idea behind the db is basically to pull information for reporting only.
There would possibly only two users MD and Accounts to pull month end results which the data would be 99% last months.
I don’t intend giving any of the users the facility to write to the ODBC tables at all.
Other information I would need (Which is not part of the DOS tables), I can create Access tables and link them.

So creating Make table queries becomes possible, how about the relationships, VBA still ?

BTW

I enterred CreateRelation in the Immediate window which came back with no help.

Reply | Quote

If the data are those from last month, there is no need to refresh them each time the database is opened. And even with only two users, if they could be active at the same time, you should NOT import the tables when the database is opened, and delete them when the database is closed.

> I entered CreateRelation in the Immediate window which came back with no help.
Do you have a reference to the Microsoft DAO 3.6 Object Library?

Reply | Quote

Ok Hans, I understand the issue with Multiple users.

Do you have a reference to the Microsoft DAO 3.6 Object Library?, No I hadn’t but is ok now.

Reply | Quote

I had the same problem with ODBC data from an Accounting package and like you I was really only using it for reporting. Eventually I got to the stage that each month, I delete all the data in the tables then reimport the data into the existing tables (thus preserving relationships but being sure I had up to date data) . Means I spend 15 mins or so each month importing all the data but overcame the ODBC speed problem
Steve (holy molly an answer from me rather than a question – It will probably turn out I misunderstood the question :-0

Reply | Quote

Steve
Thanks for the comments, and yes, you understood the question correctly.
You’ve basically confirmed what I need to do for reporting.

The information I can gather is extremely valuable to us, so 15 mins or so monthly is not such a big price to pay.
Thanks again.

Reply | Quote

Steve
Thanks for the comments, and yes, you understood the question correctly.
You’ve basically confirmed what I need to do for reporting.

The information I can gather is extremely valuable to us, so 15 mins or so monthly is not such a big price to pay.
Thanks again.

Reply | Quote

I had the same problem with ODBC data from an Accounting package and like you I was really only using it for reporting. Eventually I got to the stage that each month, I delete all the data in the tables then reimport the data into the existing tables (thus preserving relationships but being sure I had up to date data) . Means I spend 15 mins or so each month importing all the data but overcame the ODBC speed problem
Steve (holy molly an answer from me rather than a question – It will probably turn out I misunderstood the question :-0

Reply | Quote

Ok Hans, I understand the issue with Multiple users.

Do you have a reference to the Microsoft DAO 3.6 Object Library?, No I hadn’t but is ok now.

Reply | Quote

Hans

The idea behind the db is basically to pull information for reporting only.
There would possibly only two users MD and Accounts to pull month end results which the data would be 99% last months.
I don’t intend giving any of the users the facility to write to the ODBC tables at all.
Other information I would need (Which is not part of the DOS tables), I can create Access tables and link them.

So creating Make table queries becomes possible, how about the relationships, VBA still ?

BTW

I enterred CreateRelation in the Immediate window which came back with no help.

Reply | Quote

Hi Dave,

This would only be possible if your database will be never used by more than one user simultaneously. If there are multiple users, it would result in chaos.

How often do the data in the external database change? Perhaps it would be enough to refresh the data once a day, or once a week.

Reply | Quote

Hans

Wouldn’t it be easier to run a make table query on a splash and then a delete query on closing the db ?

Reply | Quote

You can type the following keywords in the Visual Basic Editor (in a module or in the Immediate window) and press F1 to get help on them:

TransferDatabase to import a table.
CreateIndex to create a new index on a table.
CreateRelation to create a new relationship between two tables.

The latter two require that you have set a reference to the Microsoft DAO 3.6 Object Library.

Reply | Quote

You will have to create the relationships anew after importing the data, since you will have to delete the links before importing the tables.

Added: if you want, you can import, create indexes and relationships in code using DAO.

Reply | Quote

Ok well thankyou all for the wise words.

The tables are linked at the moment, so answering all your guess’s.
I’ll do some testing to see which is the best method.
If I created the relationships now, will those relationships still be present once imported. ?

Reply | Quote

In point of fact this often happens with ODBC linked tables, and is largely a function of the ODBC driver. The problem comes when you start joining tables, either entirely in Access or partially in Access and partially in the external database. When you have those situations, Access brings in the entire table(s) so it can do the join internally – if it is just a single table, the ODBC driver is usually “smart” enough to get just the data you need and deliver it to Access. (The SQL Server ODBC driver is smart enough that it will often run two or three table joins where all the tables are in SQL Server in the database engine, but try it with two tables in SQL Server and one in Access and the wheels fall off.)

In your case I agree both with Hans and Mark – bringing the entire set of data in on a temporary or periodic basis would be the best solution, OR you could simply rewrite the application that creates and uses the data in Access! [evingrin]

Reply | Quote