Meu colega agora responsável pelo desenvolvimento de SQL diz que eu nunca deveria usar uma ORinstrução porque isso atrapalharia o otimizador de consultas e ignoraria índices de tabela produzindo consultas lentas. Não consegui encontrar nenhum exemplo disso enquanto pesquisava no Google. A alternativa para a consulta a seguir se torna realmente feia com uma dúzia de blocos de código que parecem quase idênticos (ao exemplo) usando instruções if else para cada estado de variável. Observe as variáveis que estão marcadas para curto-circuito e retornam todos os resultados se o valor for 2 ou então filtre pelo campo.
Solicitei alguns recursos contendo essas afirmações sobre por que não usar uma ORdeclaração e recebi os seguintes links (estamos usando o 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
Nenhum desses exemplos parece se assemelhar à implementação atual, conforme abaixo. Acho difícil acreditar que este código é problemático, mas por favor, deixe-me saber se é. Eu também gostaria de mais algumas informações onde o comentário feito sobre não usar ORpode realmente ser verdade e por que, para entender melhor o problema.
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
Acredito que duplicar código é sempre ruim, a menos que haja uma boa razão. Depois de testar a consulta, confirmei que os índices adequados estavam sendo usados. Depois de mencionar isso, me disseram que ele usaria as melhores práticas. Não vi nenhuma prática recomendada me dizendo para não usar OR. Alguém pode me levar a esses?
Aqui está a abominação antes de eu atualizá-la há muito tempo. Se você pegou o @Departmente está se perguntando sobre isso, não é um erro. Um componente de relatório da Telerik está fazendo algo com esse código e expandindo uma matriz em segundo plano antes de chegar ao servidor.
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
NOTA: Eu removi alguns no exemplo de código inicial para simplificar.
De acordo com sua explicação, links e minhas pesquisas, acho difícil acreditar que minha solução atual não seja a melhor para desempenho e legibilidade. Aceito que pode haver alguns casos por aí que arrisquem a degradação do desempenho, mas duvido muito que esse seja o caso da minha implementação. Não gosto da ideia de que ORnunca deve ser usada quando não vi nenhuma documentação que diga isso.
Quando mostrei a consulta, me disseram como uma generalização para nunca usar o OR. Agora estou fazendo minha lição de casa para ver se esta é uma informação válida. Eu realmente odiaria usar o código anterior, pois está errado de muitas maneiras.