Query running slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that searches a table that has about 300,000 records. As it
is, it takes about 5 minutes to run. Is there any way to speed this up?
SELECT TagFixCombinations.os, TagFixCombinations.type,
TagFixCombinations.tag, TagFixCombinations.Fix, Max(IIf([Selected Fixes
Installed]!tag Is Null,1,Null)) AS missing, TagFixCombinations.Column
FROM TagFixCombinations LEFT JOIN [Selected Fixes Installed] ON
(TagFixCombinations.tag = [Selected Fixes Installed].tag) AND
(TagFixCombinations.Fix = [Selected Fixes Installed].Fix)
GROUP BY TagFixCombinations.os, TagFixCombinations.type,
TagFixCombinations.tag, TagFixCombinations.Fix, TagFixCombinations.Column;
 
Do you have indexes on:
TagFixCombinations.tag
[Selected Fixes Installed].tag
TagFixCombinations.Fix
[Selected Fixes Installed].Fix
TagFixCombinations.os
TagFixCombinations.type
TagFixCombinations.Column
 
I have indexes on anything from Selected Fixes Installed, but
TagFixCombinations isn't indexed. The problem is, this is an automatically
generated table that I can't change the properties of (they would just
refresh overnight).

Duane Hookom said:
Do you have indexes on:
TagFixCombinations.tag
[Selected Fixes Installed].tag
TagFixCombinations.Fix
[Selected Fixes Installed].Fix
TagFixCombinations.os
TagFixCombinations.type
TagFixCombinations.Column

--
Duane Hookom
MS Access MVP


MLT said:
I have a query that searches a table that has about 300,000 records. As it
is, it takes about 5 minutes to run. Is there any way to speed this up?
SELECT TagFixCombinations.os, TagFixCombinations.type,
TagFixCombinations.tag, TagFixCombinations.Fix, Max(IIf([Selected Fixes
Installed]!tag Is Null,1,Null)) AS missing, TagFixCombinations.Column
FROM TagFixCombinations LEFT JOIN [Selected Fixes Installed] ON
(TagFixCombinations.tag = [Selected Fixes Installed].tag) AND
(TagFixCombinations.Fix = [Selected Fixes Installed].Fix)
GROUP BY TagFixCombinations.os, TagFixCombinations.type,
TagFixCombinations.tag, TagFixCombinations.Fix, TagFixCombinations.Column;
Click to expand...
Click to expand...
 
If you can't add indexes then live with the slow running query. I suppose
you could make sure the mdb file is located on your hard drive and you have
lots of memory.

There are ways to add indexes to existing tables or append the records from
a generated table to a temporary holding table with all the proper indexes.

--
Duane Hookom
MS Access MVP


MLT said:
I have indexes on anything from Selected Fixes Installed, but
TagFixCombinations isn't indexed. The problem is, this is an
automatically
generated table that I can't change the properties of (they would just
refresh overnight).

Duane Hookom said:
Do you have indexes on:
TagFixCombinations.tag
[Selected Fixes Installed].tag
TagFixCombinations.Fix
[Selected Fixes Installed].Fix
TagFixCombinations.os
TagFixCombinations.type
TagFixCombinations.Column

--
Duane Hookom
MS Access MVP


MLT said:
I have a query that searches a table that has about 300,000 records. As
it
is, it takes about 5 minutes to run. Is there any way to speed this
up?
SELECT TagFixCombinations.os, TagFixCombinations.type,
TagFixCombinations.tag, TagFixCombinations.Fix, Max(IIf([Selected Fixes
Installed]!tag Is Null,1,Null)) AS missing, TagFixCombinations.Column
FROM TagFixCombinations LEFT JOIN [Selected Fixes Installed] ON
(TagFixCombinations.tag = [Selected Fixes Installed].tag) AND
(TagFixCombinations.Fix = [Selected Fixes Installed].Fix)
GROUP BY TagFixCombinations.os, TagFixCombinations.type,
TagFixCombinations.tag, TagFixCombinations.Fix,
TagFixCombinations.Column;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top