Query taking 2.5 minutes to process, plz help.

[shu745]

Hi all, in MS Access 2000 I have a query which joins two other queries together:
--- getExtras ---
SELECT [Extras].

, [Extras].[Coords], [getPhone].[Phone]
FROM Extras INNER JOIN getPhone ON [Extras].[Code]=[getPhone].[Code];

--- getMostUpdatedDup ---
SELECT [BadgeHolder].[Counter], [BadgeHolder].[Field1] AS Code, [Field2] AS FirstName, [BadgeHolder].[Field3] AS LastName, [BadgeHolder].[Field4] AS Dept, [BadgeHolder].[Field5] AS Site, [BadgeHolder].[PicturePath]
FROM BadgeHolder
WHERE ((BadgeHolder.Counter) In (SELECT Max(getDups.Counter) FROM  getDups GROUP BY Code));

--- slowQuery ---
SELECT [getMostUpdatedDup].[Counter], [getMostUpdatedDup].[Code], [getMostUpdatedDup].[FirstName], [getMostUpdatedDup].[LastName], [getMostUpdatedDup].[Dept], [getMostUpdatedDup].[Site], [getMostUpdatedDup].[PicturePath], [getExtras].[Coords], [getExtras].[Phone]
FROM getMostUpdatedDup INNER JOIN getExtras ON [getMostUpdatedDup].[Code]=[getExtras].[Code];

The getExtras and getMostUpdatedDup queries return instant results.  But slowQuery takes 2.5 minutes to process.  Any ideas why?

Please let me know if you need any more info.

Thx in advance.
Shu

 

[shu745]

It seems that the line that is making the slowQuery process slowly is the where clause in the getMostUpdatedDup query. If I remove that than it seems to run fast, but then obviously my results are incorrect.

Hope that helps.
- Shu

 

[lespaul]

do you have an index on the field in the table? If you provide some sample data from the TABLES and what your expected results are, i'm sure that someone can get you all the required information in a single query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

And what is getDups ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886