在 SQL 中添加额外的 Where 子句会对性能造成很大影响 [英] Adding Additional Where Clause in SQL Takes Big Performance Hit

问题描述

我有一个贯穿多个视图和表的 SQL 查询.

I have an SQL Query that runs through multiple views and tables.

查询运行良好，但是当我在 WHERE 子句中添加另一个条件时，它开始对性能产生巨大影响.

The query runs fine, but when I added another condition in the WHERE clause, it started to have tremendous hit on the performance.

查询的结构类似于...

The query is structured like...

         SELECT a.*
           FROM vw_myView a
LEFT OUTER JOIN tbl1 b ON a.ID = b.ID
LEFT OUTER JOIN vw_OtherView c ON a.ID = c.ID
LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID
          WHERE a.Column1 = 'VALUE'
            AND a.Column2 LIKE ISNULL(@parameter, a.Column2)

从上面的查询来看，当我在 WHERE 子句中添加其他条件时，我的查询现在需要很多时间(超过 3 分钟)来执行并返回 1000 条记录.删除添加的条件子句和查询，7 秒内返回记录.

From the query above, when I add the other condition in the WHERE clause, my query now takes a lot of time (more than 3 minutes) to execute and return 1000 records. Removing the added conditional clause and query returns the records in less than 7 secs.

谢谢.我应该检查什么以查看性能影响巨大的地方

Thanks. What should I be checking to see where the performance hit is tremendous

推荐答案

你的查询子句 AND a.Column2 LIKE ISNULL(@parameter, a.Column2) 涉及调用一个函数，所以 SQL Server 有在您的表中执行表扫描(即针对每一行测试该函数)以查看它是否满足查询.

Your query clause AND a.Column2 LIKE ISNULL(@parameter, a.Column2) involves calling a function, so SQL Server has to do a table scan (ie test that function against every row) in your table to see if it satisfies the query.

即使你在 a.Column2 上有索引，Sql Server 也无法使用它.

Even if you had an index on a.Column2, Sql Server would not be able to use it.

这是在大多数情况下加快速度的一种方法

-- Only this part executes when the @parameter has a value.
-- It returns nothing and executes fast when @parameter is NULL

SELECT a.*
           FROM vw_myView a
LEFT OUTER JOIN tbl1 b ON a.ID = b.ID
LEFT OUTER JOIN vw_OtherView c ON a.ID = c.ID
LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID
          WHERE a.Column1 = 'VALUE'
            AND a.Column2 LIKE @parameter

UNION ALL

-- This part does not execute when the @paramter has a value
-- This will leave the 2nd query clause out and run faster when @parameter is not specified, avoiding a table scan

SELECT a.*
           FROM vw_myView a
LEFT OUTER JOIN tbl1 b ON a.ID = b.ID
LEFT OUTER JOIN vw_OtherView c ON a.ID = c.ID
LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID
          WHERE a.Column1 = 'VALUE'
            AND @parameter IS NULL

这篇关于在 SQL 中添加额外的 Where 子句会对性能造成很大影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！