CODEWITHSUNDEEP
sqlms-accessms-access-2007
Nathaniel Bendinsky
Nathaniel Bendinsky

Reputation: 143

Access 2007: Subqueries causing massive performance loss

I am building a query to search for records matching any number of fields in a table with 15+ columns. (All fields for which an input is given must match.)

However, the original table was badly designed, in some cases having upwards of ten fields for the same thing. (With names such as Street_11.) I have separated the data into multiple tables such that Street_2 through Street_12 are now all labeled under Street_2 in a separate table, containing only that column and fileID, which is taken from the main table's primary key. (There is no formal relation between the two, though fileID in the secondary table serves as a foreign key.)

Prior to the normalization efforts, the search form was using this query. While not optimal, it was at least functional. I have since removed the segments searching the Street_X and Block_X fields, replacing them with subqueries:

WHERE 
(
    [Map index].fileID IN 
    (
    SELECT fileID FROM [fileID-Street] 
    WHERE [fileID-Street].Street_2 LIKE "*" & [Forms]![DW_Form]![Street] & "*"
    ) 
  OR 
    ([Forms]![DW_Form]![Street] Is Null)
)
AND 
(
    [Map index].fileID IN 
    (
    SELECT fileID FROM [fileID-blockLot] 
    WHERE [fileID-blockLot].Block LIKE "*" & [Forms]![DW_Form]![Street] & "*"
    ) 
OR 
    ([Forms]![DW_Form]![Block] Is Null)
)

The above queries search the smaller tables. However, when including these in the main query (between the FROM and current WHERE clauses, with the current WHERE replaced with AND)I have encountered a truly staggering loss of performance; including just the Street clause (with relevant section of the original query removed) raises processing time from ~5s to ~45s. Adding both subqueries slows the search time to well over three minutes.

At this point it's clear that I'm likely going about this the wrong way; what information I've been able to find (such as here) seems to indicate that the subqueries might be running on the entire subtable for every record. What should I be doing in this situation?

Upvotes: 0

Views: 65

Answers (1)

JCro
JCro

Reputation: 696

Try using a join instead of a subquery, it generally performs better. Example:

SELECT ...
FROM [Map index] LEFT OUTER JOIN [fileID-Street] 
ON [Map index].fileID=[fileID-Street].fileID
WHERE ... AND [fileID-Street].Street_2 LIKE '*" & [Forms]![DW_Form]![Street] & "*'

Upvotes: 1

Related Questions