speed up link tables

[tracktraining]

Hi All,

I have two link tables in my db via odbc. I only need certain information
from these two tables so i wrote a query to get the information that i
needed. The problem is that the query is running very slow (10mins to
finish). Do anyone know how to speed this up?

Thanks in advance.
Tracktraining

 

[Beetle]

It would help if you post the SQL of your query, your version of Access,
what type of database the odbc linked tables are in, etc. It will probably
be difficult for anyone to tell you anything specific without knowing more.

 

[tracktraining]

This is my SQL(DocInfo):

SELECT [Part Master].PRTNUM_01 AS DocID, [Part Master].REVLEV_01 AS Revision
FROM [Part Master] LEFT JOIN [Mfg Part Master] ON [Part Master].PRTNUM_01 =
[Mfg Part Master].PRTNUM_49
ORDER BY [Part Master].PRTNUM_01;

I am using Access 2003.

The linked tables are in pervasive database. So [Part Master] and [Mfg Part
Master] are linked tables.

Running this particular query is not causing any speed problem. Running
other queries or opening up any form that uses this query takes up a lot of
time.

Let me know if you need any other information.

 

[John W. Vinson]

This is my SQL(DocInfo):

SELECT [Part Master].PRTNUM_01 AS DocID, [Part Master].REVLEV_01 AS Revision
FROM [Part Master] LEFT JOIN [Mfg Part Master] ON [Part Master].PRTNUM_01 =
[Mfg Part Master].PRTNUM_49
ORDER BY [Part Master].PRTNUM_01;

I am using Access 2003.

The linked tables are in pervasive database. So [Part Master] and [Mfg Part
Master] are linked tables.

Running this particular query is not causing any speed problem. Running
other queries or opening up any form that uses this query takes up a lot of
time.

Let me know if you need any other information.

If possible, put some sort of criteria on the query to limit the number of
records retrieved. There's not enough information shown to suggest how, but
perhaps if there is a PRTNUM_01 field on your form you could use it as a
criterion.

 

[tracktraining]

Thanks. I'll try that.
--
Learning

This is my SQL(DocInfo):

SELECT [Part Master].PRTNUM_01 AS DocID, [Part Master].REVLEV_01 AS Revision
FROM [Part Master] LEFT JOIN [Mfg Part Master] ON [Part Master].PRTNUM_01 =
[Mfg Part Master].PRTNUM_49
ORDER BY [Part Master].PRTNUM_01;

I am using Access 2003.

The linked tables are in pervasive database. So [Part Master] and [Mfg Part
Master] are linked tables.

Running this particular query is not causing any speed problem. Running
other queries or opening up any form that uses this query takes up a lot of
time.

Let me know if you need any other information.

If possible, put some sort of criteria on the query to limit the number of
records retrieved. There's not enough information shown to suggest how, but
perhaps if there is a PRTNUM_01 field on your form you could use it as a
criterion.

 

[Lord Kelvan]

i use odbc for some very large and complicated querys comming from a
large and complicated survalance databae of a little over 50 tables
basically joind up in every way that tables can and cannot be joind up
in including union joins and none of them take 10 mins only two things
i could think of you have a insanly large amount of records or you
have a shocking network

Regards
Kelvan

 

[Mike Painter]

Make sure you have indexes on what you are after.

This applies even to two part keys. both need additional indexing if you
want to return just one part.
(And it worked so well with just a few records...)