性能调优 SQL [英] Performance Tuning SQL

问题描述

我有以下 sql.当我检查此查询的执行计划时，我们观察到索引扫描.如何用索引查找替换它.我在 IdDeleted 列上有非聚集索引.

I have the following sql. When I check the execution plan of this query we observe an index scan. How do I replace it with index seek. I have non clustered index on IdDeleted column.

SELECT Cast(Format(Sum(COALESCE(InstalledSubtotal, 0)), 'F') AS MONEY) AS TotalSoldNet,
       BP.BoundProjectId                                               AS ProjectId
FROM   BoundProducts BP
WHERE  BP.IsDeleted=0 or BP.IsDeleted is null
GROUP  BY BP.BoundProjectId

我这样试过，得到了索引查找，但结果是错误的.

I tried like this and got index seek, but the result was wrong.

SELECT Cast(Format(Sum(COALESCE(InstalledSubtotal, 0)), 'F') AS MONEY) AS TotalSoldNet,
       BP.BoundProjectId                                               AS ProjectId
FROM   BoundProducts BP
WHERE  BP.IsDeleted=0
GROUP  BY BP.BoundProjectId 

任何人都可以建议我使用索引查找获得正确的结果集.

Can anyone kindly suggest me to get the right result set with index seek.

我的意思是如何替换(BP.IsDeleted=0 或 BP.IsDeleted 为空)条件以利用索引查找.

I mean how to I replace (BP.IsDeleted=0 or BP.IsDeleted is null) condition to make use of index seek.

编辑，从答案之一的评论中添加行数:

Edit, added row counts from comments of one of the answers:

null: 254962 rows
0:    392002 rows
1:     50211 rows

推荐答案

您没有进行索引查找，因为您正在获取表中几乎 93% 的行，在这种情况下，只需扫描整个索引更快更便宜.

You're not getting an index seek because you're fetching almost 93% of the rows in the table and in that kind of scenario, just scanning the whole index is faster and cheaper to do.

如果您有性能问题，您应该考虑删除 format() 函数，尤其是在查询返回大量行的情况下.从这篇博文中阅读更多信息

If you have performance issues, you should look into removing format() -function, especially if the query returns a lot of rows. Read more from this blog post

其他选项可能是创建索引视图并预先聚合您的数据.这当然会增加更新/插入操作的开销，因此请考虑仅当确实经常这样做与更新表的频率时才这样做.

Other option might be to create an indexed view and pre-aggregate your data. This of course adds an overhead to update / insert operations, so consider that only if this is done really often vs. how often the table is updated.

这篇关于性能调优 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！