How does MS Access get data for query?

[Erick]

I realise my thread title is a little ambiguous. The reason for my question is that I have a query that outputs 2 columns ('EquipNum' & 'Key'). When I run this query, it probably takes about 10secs to come up with result.

I have second query that also outputs 2 columns ('AssetNum' & 'Key'). This also takes about 10secs to come up with result.

If I create a third query that outputs EquipNum & AssetNum, where query1.key = query2.key, after running for 8hrs, the query still hasn't come up for air!!!

If I convert the first 2 queries into tables and query these tables instead, it takes about 10secs to complete.

I just don't understand why it takes so long to query the 2 queries. I would have guessed that it shouldn't take more than 30secs to complete this task...10sec to run the first query, another 10secs to run the second, and then another 10secs to combine these 2 queries and extract the result, the same as if it was looking up tables.

What would be the logical explanation here?

 

[xenou]

Can you post your SQL? Chances are you have created a "cartesian join" or "cross join" - which can be millions of records. It's possible it's something else - not sure.

ξ

 

[Erick]

First Query (Query1):

SELECT AssetList.[Location Code] AS EquipNum, [Facility Code] & "." & [EquipType] & "." & ((Select Count (*) FROM [AssetList] as Temp WHERE ([Temp].[Location Code] < [AssetList].[Location Code] and [Temp].[EquipType] = [AssetList].[EquipType] and [Temp].[Facility Code] = [AssetList].[Facility Code])))+1 AS [Key]
FROM AssetList
WHERE (((AssetList.EquipType)>"0"));

The second query (Query2):

SELECT tblRcmLocations_Maximo_Map.Id AS AssetNum, [FacilityCode] & "." & [EquipmentType] & "." & ((Select Count (*) FROM [tblRcmLocations_Maximo_Map] as Temp WHERE ([Temp].[Id] < [tblRcmLocations_Maximo_Map].[Id] and [Temp].[EquipmentType] = [tblRcmLocations_Maximo_Map].[EquipmentType] and [Temp].[FacilityCode] = [tblRcmLocations_Maximo_Map].[FacilityCode])))+1 AS [Key]
FROM tblRcmLocations_Maximo_Map;

As I have said, these 2 queries only take about 10secs each to run.

My third query:

SELECT Query1.EquipNum, Query2.AssetNum
FROM Query1 INNER JOIN Query2 ON Query1.Key = Query2.Key;

this ****** sends my pc into near meltdown to run.

as I have said, if if modify the top 2 queries to a make-table queries and then query them the same way as how I have it in the 3rd query, it only takes about 10secs, once again to run.

 

[xenou]

Just from a quick glance it looks like the query has to run for every record retrieved, because of the nested clauses. Or worse ... Maybe it has to run the whole table to find the key in the other table to join on, for each record in the first table. These things don't need to be understood ... it's enough to know it takes 8 hours to run.

But no worries ... just drop the query results into a table and query the table ... If you script it into a few statements (DoCmd.blahblah DoCmd.BlahBlah DoCmd.BlahBlah) you can run it all with a "click of button" (literally).