我现在负责 SQL 开发的同事说我永远不应该使用OR
语句,因为它会弄乱查询优化器并忽略产生缓慢查询的表索引。我在谷歌搜索时找不到任何这样的例子。以下查询的替代方案变得非常难看,有十几个代码块,这些代码块看起来几乎相同(与示例相同),对每个变量状态使用 if else 语句。注意检查短路的变量,如果值为 2,则返回所有结果,否则按字段过滤。
我询问了一些包含这些关于为什么不使用OR
语句的声明的资源,并收到了以下链接(我们使用的是 MS SQL Server)。
- https://stackoverflow.com/questions/5639710/union-all-vs-or-condition-in-sql-server-query
- https://bertwagner.com/2018/02/20/or-vs-union-all-is-one-better-for-performance/
- http://sqlserverplanet.com/optimization/using-union-instead-of-or
这些示例似乎都不像下面的当前实现。我很难相信这段代码有问题,但如果有问题请告诉我。我还想了解更多关于不使用的评论OR
实际上可能成立的信息以及为什么如此,以便更好地理解这个问题。
SELECT
e.EmployeeName,
e.DepartmentName,
crs.Title,
c.Name as CompanyName
FROM Employee E
Left Outer Join Company c ON c.Id = @companyId
INNER JOIN Department d on e.DepartmentId = d.Id
WHERE
c.Id = @companyId
AND (@Active = 2 OR crs.IsActive = @Active)
AND (@Dot = 2 OR IsDot = @Dot)
AND crs.CompanyId = @companyId
AND d.CompanyId = @companyId
ORDER BY EmployeeName, Title, PassedDate
我相信除非有充分的理由,否则复制代码总是不好的。测试查询后,我确认使用了正确的索引。提到这一点后,我被告知他会使用最佳实践。我还没有看到任何最佳实践告诉我不要使用OR
. 谁能带我去这些?
这是我很久以前更新之前的可憎之处。如果您发现@Department
并对此感到疑惑,那不是错误。Telerik 报告组件正在对这段代码做一些事情,并在后台扩展一个数组,然后再到达服务器。
IF @Active = 2
BEGIN
--ACTIVE AND INACTIVE
IF 0 IN (@Department)
BEGIN
IF @DOT = 1
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 1
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 0
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 2
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 0
ORDER BY EmployeeName, Title, PassedDate
END
END
ELSE
BEGIN
IF @DOT = 1
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 1
AND A.DepartmentId IN (@Department)
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 0
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND A.DepartmentId IN (@Department)
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 2
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 0
AND A.DepartmentId IN (@Department)
ORDER BY EmployeeName, Title, PassedDate
END
END
END
ELSE
BEGIN
--ACTIVE OR INACTIVE
IF 0 IN (@Department)
BEGIN
IF @DOT = 1
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.IsActive = @Active
AND A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 1
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 0
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.IsActive = @Active
AND A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 2
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.IsActive = @Active
AND A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 0
ORDER BY EmployeeName, Title, PassedDate
END
END
ELSE
BEGIN
IF @DOT = 1
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.IsActive = @Active
AND A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 1
AND A.DepartmentId IN (@Department)
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 0
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.IsActive = @Active
AND A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND A.DepartmentId IN (@Department)
ORDER BY EmployeeName, Title, PassedDate
END
IF @DOT = 2
BEGIN
Select
A.LastName + ', ' + A.FirstName as EmployeeName,
A.DepartmentName,
C.Title,
ISNULL(B.Comments, ' ') as Remarks,
CONVERT(varchar, B.PassedDate, 101) as DateOut,
D.Name as CompanyName
FROM Employee A
Left Outer Join EmployeeCourse B ON A.Id = B.EmployeeId
Left Outer Join CompanyCourse C ON B.CompanyCourseId = C.Id
Left Outer Join Company D ON @companyId = D.Id
WHERE A.IsActive = @Active
AND A.CompanyId = @companyId
AND B.PassedDate IS Not NULL
AND C.DotCourse = 0
AND A.DepartmentId IN (@Department)
ORDER BY EmployeeName, Title, PassedDate
END
END
END
注意:我在初始代码示例中删除了一些以简化。
根据他的解释、链接和我的搜索,我很难相信我当前的解决方案在性能和可读性方面不是最好的。我接受可能存在一些可能会降低性能的情况,但我高度怀疑我的实施是否属于这种情况。OR
当我没有看到任何说明这一点的文档时,我不喜欢永远不应该使用的想法。
当我显示查询时,我被告知作为概括永远不要使用OR
. 我现在正在做功课,看看这是否是有效的信息。我真的很讨厌使用以前的代码,因为错误的方式太多了。
永远不要听任何人说你永远不应该做 X。
一般来说,如果没有一些非常好的理由,你不应该试图超越查询优化器。
确实在某些情况下,太多
OR
的 s 会导致次优的1计划,但是您应该单独考虑每个这样的情况,并且只有在原始查询性能不可接受时才寻找解决方法。如果您确实需要解决您发布的查询的性能问题,请考虑提出不同的问题。
1 - 从人的角度来看。实际上,该计划对于该特定查询变体是最佳的(在优化器功能的范围内);我的意思是重写查询可能会产生一个执行更快或消耗更少资源的不同计划。
并不是说您永远不应该在 where 子句中使用 OR,而是您在这种情况下遵循的特定模式是一个非常糟糕的模式。我写过并记录了这个主题:
此外,您选择替换它的模式不会按照您的想法进行。看这里:
使用 or 子句的连接也可能很困难:
在不同的情况下,例如不使用可选参数,只要您有支持谓词的索引,就可以使用 OR 几乎没有什么害处。我并不是说它总是最好的,但它是可行的。
很多时候,使用 UNION ALL 替换 OR 会发现自己处于更好的位置,但在特定情况下,最好使用动态 SQL 构建适当的查询并执行它。我链接到的视频中介绍了该技术。
从对Erik 回答的评论中挑选:
你基本上有 ol' 可选参数的情况。您似乎希望我们告诉您哪个选项最好。我们不能这样做,因为我们不适合你。我们可以做的是概述一些替代方案供您调查,然后确定哪种方案更适合您的特定情况。
使用 IF 进行分支并手工制作每个查询形状。只要您知道在为所有查询生成 proc-plan 时将使用传递给 proc 的参数,而不管您的分支代码如何。即,您最终会在生成计划时在运行时获得第二个查询,但会为所有查询生成计划。现在的其他计划很有可能会有不正常的选择性估计。想象一下没有所有分支逻辑的过程。这是优化器将看到的。您最终可能会遇到“有时很快,有时很慢”的情况。此处为您提供的选项是 OPTIMIZE FOR 以获得“计划稳定性”和 OPTION(RECOMPILE),尽管如果您继续阅读,最后一个可能并不有趣。
使用带有 OR 的查询形状并抛出 OPTION(RECOMPILE) 以允许使用索引。但是您准备好为每次执行的计划生成付费了吗?那是你来回答的。
将动态 SQL 与 sp_executesql 一起使用,最终生成与各种查询形状匹配的 SQL。您现在可以使用索引和参数嗅探“真实”值。但是动态 SQL 有它的缺点。要获得使用动态 SQL 的许可,首先应该阅读Erland关于该主题的文章。:-)
因此,通常情况下,所有替代方案都有利有弊。Erland 的文章有一节介绍可选的搜索参数。阅读那篇文章是一个好的开始,希望能让您决定哪种替代方案最适合您的特定情况。