如何降低查询成本

Question

我能降低查询成本吗？

查看此查询->查询2的执行计划:查询成本(相对于批处理)：93%，唯一突出的是“哈希连接匹配(内部连接)成本: 45%”。

select t1.PrimaryKeyId, t1.AdditionalColumnId
from TableOne t1
    join TableTwo t2 on t1.ForeignKeyId = t2.PrimaryKeyId
    join TableThree t3 on t1.PrimaryKeyId = t3.ForeignKeyId
    join TableFour t4 on t3.ForeignKeyId = t4.PrimaryKeyId
    join TableFive t5 on t4.ForeignKeyId = t5.PrimaryKeyId
    join TableSix t6 on t5.ForeignKeyId = t6.PrimaryKeyId 
    join TableSeven t7 on t6.ForeignKeyId = t7.PrimaryKeyId
    join TableEight t8 on t7.ForeignKeyId = t8.PrimaryKeyId
    join TableEight t9 on t8.ForeignKeyId = t9.PrimaryKeyId
where 
    t1.isdeleted =  0  and
    t2.isdeleted =  0  and
    t1.startdttm >= @Begin_Date and t1.startdttm < @End_Date  and
    t1.paymentbalance = 0 and
    t8.pos_source_id in (select sit_source_id from @TableTen) and
    t2.transTypeId = 1 and
    t2.transitemtypeid in (1,7)


--- Table Counts 
SELECT COUNT(*) FROM TableOne = 4014596
SELECT COUNT(*) FROM TableTwo = 5409514
SELECT COUNT(*) FROM TableThree = 552980
SELECT COUNT(*) FROM TableFour  = 552976
SELECT COUNT(*) FROM TableFive  = 4376
SELECT COUNT(*) FROM TableSix   = 4376
SELECT COUNT(*) FROM TableSeven = 9
SELECT COUNT(*) FROM TableEight = 175
SELECT COUNT(*) FROM TableNine = 36098
SELECT COUNT(*) FROM @TableTen = 68

Answer 1

我建议对t1(isdeleted, paymentbalance, startddtm)进行索引，以满足where子句的要求。如果只需要来自t1的几行，这将是最好的。

哈希匹配非常有效。您可以代替索引查找。因此，还可以尝试以下索引：

t2(PrimaryKeyId, isdeleted, transtypeid, transittypeid)
t8(PrimaryKeyId, pos_source_id)

这将阻止访问表的数据。索引“覆盖”查询。