Início / dba / Perguntas / 278156
Accepted
jwize
Asked: 2020-10-16 12:12:50 +0800 CST

O colega diz para nunca usar uma instrução OR no SQL, isso é verdade?

  • 772

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).

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.

sql-server query-performance

3 respostas

  1. Best Answer

    Nunca ouça ninguém dizendo que você nunca deve fazer X.

    Geralmente, você não deve tentar ser mais esperto do que o otimizador de consulta sem alguns bons motivos.

    É verdade que, em certos cenários, muitos ORs podem levar a um plano 1 abaixo do ideal , mas você deve considerar cada um desses cenários individualmente e procurar soluções alternativas apenas se o desempenho da consulta original for inaceitável .

    Se você precisar abordar o desempenho da consulta que postou, considere fazer uma pergunta diferente .

    1 - Do ponto de vista humano. Na verdade, o plano será ideal para essa variante de consulta específica (na medida dos recursos do otimizador); o que quero dizer é que reescrever a consulta pode produzir um plano diferente que executa mais rápido ou consome menos recursos.

    • 23

  2. Não é que você nunca deva usar OR em uma cláusula where, é que o padrão específico que você está seguindo neste caso é muito ruim. Escrevi e gravei sobre o assunto:

    Além disso, o padrão que você escolheu para substituí-lo não funcionará da maneira que você pensa. Veja aqui:

    As junções com ou cláusulas também podem ser difíceis:

    Sob diferentes circunstâncias, por exemplo, não usando parâmetros opcionais, OR pode ser usado com pouco prejuízo, desde que você tenha indexação para suportar os predicados. Não estou dizendo que é sempre melhor , mas é viável.

    There are many times you'll find yourself in a better position by using UNION ALL to replace OR, but in the specific case you have, you're better off using dynamic SQL to build the appropriate query and execute it. That technique is covered in the video I linked to.

    • 16

  3. Picking up from comments on Erik's answer:

    You basically have the ol' optional parameters case. It seems you expect us to tell you which option is best. We can't do that since we aren't in your shoes. What we can do is to outline a few alternatives for you to investigate and then determine which is better your your particular situation.

    Branching with IF and hand-crafting each query shape. As long as you are aware that parameters passed to a proc will be used when the proc-plan is generated for all queries, regardless of your branching code. I.e., you end up with for instance the second query at run time when plan is generated, but plans are generated for all queries. Chances are that the other plans now will have out-of-whack selectivity estimates. Just imagine the proc without all the branching logic. This is what the optimizer will see. You might end up with the "sometimes it is fast, sometimes it is slow" situation. Options here for you are OPTIMIZE FOR to get "plan stability" and OPTION(RECOMPILE), although the last one might not be interesting if you keep reading.

    Using your query shape with OR and throwing OPTION(RECOMPILE) to allow for index usage. But are you prepared to pay for plan generation on each execution? That is for you to answer.

    Using dynamic SQL with sp_executesql so you end up with generated SQL matching your various query shapes. You now have possibility of index usage and parameter sniffing the "real" values. But dynamic SQL has its drawback. To be licensed to use dynamic SQL, one should have read Erland's article on the topic first. :-)

    So, as it often is, there are upsides and downsides with all alternatives. Erland's article has a section on optional search arguments. Reading that article is a good start, hopefully allowing you to decide which alternative(s) is best for your particular situation.

    • 4

relate perguntas