Access 2007:子查询导致大量的性能损失 [英] Access 2007: Subqueries causing massive performance loss

问题描述

我正在建立一个查询来搜索与15列以上的表中任意数量的字段匹配的记录. (所有为其提供输入的字段必须匹配.)

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.)

但是，原始表的设计不正确，在某些情况下，同一表最多包含十个字段. (使用Street_11之类的名称.)我已将数据分成多个表，以使Street_2至Street_12现在都在单独的表中的Street_2下标记，仅包含该列和fileID，这是采用的从主表的主键. (尽管辅助表中的fileID用作外键，但两者之间没有正式关系.)

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.)

在进行标准化之前，搜索表单使用的是此查询.虽然不是最佳选择，但至少可以发挥作用.此后，我删除了搜索Street_X和Block_X字段的段，将其替换为子查询:

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)
)

以上查询搜索较小的表.但是，当在主查询中包括这些内容时(在FROM和当前WHERE子句之间，而当前的WHERE被AND替换)，我确实遇到了惊人的性能损失.仅包含Street子句(删除了原始查询的相关部分)将处理时间从〜5s提升至〜45s.同时添加两个子查询会使搜索时间缩短到三分钟以上.

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?

推荐答案

尝试使用联接而不是子查询，它通常会更好. 示例:

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] & "*'

这篇关于Access 2007:子查询导致大量的性能损失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！