为什么innerjoin的where子句中的这个小sql更改会带来巨大的性能改进？

Question

背景：

我们将ERP中的一些性能问题缩小到一条SQL语句。我们的支持人员对SQL语句进行了一处小更改，性能得到了提高。后端是Microsoft Access。(是的，我知道，是的，这很尴尬，不，在这件事上我别无选择)

我们从运行语句需要90秒的75%的时间，从25%的2-3秒减少到100%的2-3秒。

POHDR表很大，超过20,000行，SUPPNAME不到1000行。两个表都有索引的vnum字段。

下面的SQL语句，但全部更改的是Where子句使用SUPPNAME.vnum而不是POHDR.vnum。

之前：

SELECT DISTINCTROW POHDR.*, 
                   SUPPNAME.SNAME1,
                   suppname.sname1 & chr(13) & chr(10) & POHDR.vnum as SUPPFLD 
    FROM POHDR 
        INNER JOIN SUPPNAME 
            ON POHDR.VNUM = SUPPNAME.VNUM 
    WHERE ((POHDR.VNUM= '20023' AND POHDR.RECDATE Is Null))  
        AND [POHDR].[CANCEL] Is Null and ((POHDR.CLOSED=No)) 
    order by IIf(InStr(PO,'-'),Left(PO,InStr(PO,'-')) & '_' & Mid(PO,InStr(PO,'-')),PO)

之后：

SELECT DISTINCTROW POHDR.*, 
                   SUPPNAME.SNAME1,
                   suppname.sname1 & chr(13) & chr(10) & POHDR.vnum as SUPPFLD 
    FROM POHDR 
        INNER JOIN SUPPNAME 
            ON POHDR.VNUM = SUPPNAME.VNUM 
    WHERE ((SUPPNAME.VNUM= '26037' AND POHDR.RECDATE Is Null))  
        AND [POHDR].[CANCEL] Is Null 
        and ((POHDR.CLOSED=No)) 
    order by IIf(InStr(PO,'-'),Left(PO,InStr(PO,'-')) & '_' & Mid(PO,InStr(PO,'-')),PO)

将选择vnum的位置更改为一个较小的表，而vnum的复制较少或没有复制，是否真的会有那么大的不同，或者是发生了其他事情？

谢谢，好奇的布莱恩。

附注：另外，我没有编写或控制这个sql语句。也不确定order by子句中的if到底发生了什么。

Answer 1

除了更改WHERE子句会导致优化器选择不同的查询计划之外，我不知道是否有一个非常好的“为什么”回答您的问题。

优化器可以根据许多因素(索引、统计信息、黑魔法等)选择查询计划。只要您有良好的索引，您通常能做的最好的事情就是仔细测试查询的不同相似版本，并比较它们生成的查询计划。

Answer 2

这种查询优化的关键是索引-所以首先要检查是否所有字段都已索引，特别是SUPPNAME.VNUM和POHDR.VNUM。

第二个当然是每个表中满足这些条件的记录的数量。如果只有一个SUPPNAME.VNUM= '26037‘，它可以消除许多POHDR.VNUM= '20023’，这些a在其他条件下会失败，然后就不需要执行了。

但是，如果没有看到数据库的结构和其中的数据，我不会做出任何明确的结论。