我现在负责 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
. 我现在正在做功课,看看这是否是有效的信息。我真的很讨厌使用以前的代码,因为错误的方式太多了。