19

My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't find any example of this while googling. The alternative to the following query becomes really ugly with a dozen or so blocks of code which look almost identical(to the example) using if else statements for each variable state. Note the variables that are checked to short-circuit and return all results if the value is 2 or else filter by the field.

I asked for some resources containing these claims about why not to use an OR statement and received the following links (we are using MS SQL Server).

None of these examples seem to resemble the current implementation as below. I find it hard to believe that this code is problematic but please let me know if it is. I would also like some more information where the comment made about not using OR might actually hold true and why so, as to better understand the issue.

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

I am a believer that duplicating code is always bad unless there is a really good reason. After testing the query I confirmed that the proper indexes were being used. After mentioning this, I was told that he would use best practices. I haven't seen any best practices telling me not to use OR. Can anyone lead me to these?

Here is the abomination before I updated it a long time ago. If you caught the @Department and are wondering about it, it is not an error. A Telerik reporting component is doing something to this code and expanding an array in the background before it gets to the server.

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

NOTE: I removed some in the initial code example to simplify.

According to his explanation, links, and my searches I find it hard to believe that my current solution is not the best for performance and readability. I accept that there may be some cases out there that risk performance degradation but I highly doubt that this is the case with my implementation. I don't like the idea that OR should never be used when I haven't seen any documentation that says this.

When I showed the query I was told as a generalization to never use the OR. I am now doing my homework to see if this is valid information. I would really hate to use the previous code as it is wrong is so many ways.

Improve this question
0

4 Answers 4

Reset to default
26

Never listen to anyone saying that you should never do X.

Generally, you should not try to outsmart the query optimizer without some really good reasons.

It is true that in certain scenarios too many ORs can lead to a suboptimal1 plan, but you should consider every such scenario individually and only look for workarounds if the original query performance is unacceptable.

If you do need to address performance of the query you posted, please consider asking a different question.

1 - From the human point of view. The plan in fact will be optimal for that particular query variant (to the extent of the optimizer capabilities); what I mean is that rewriting the query might produce a different plan that executes faster or consumes fewer resources.

Improve this answer
1
  • "Never listen to anyone saying that you should never do X." Best programming / IT advice ever.
    – Peter Moore
    Commented Mar 4 at 19:37
18

It's not that you should never use OR in a where clause, it's that the particular pattern you're following in this case is a very bad one. I've written and recorded on the subject:

Additionally, the pattern you chose to replace it with will not work out the way you think. See here:

Joins with or clauses can also be difficult:

Under different circumstances, e.g. not using optional parameters, OR can be used to little detriment, as long as you have indexing to support the predicates. I'm not saying it's always best, but it's workable.

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.

Improve this answer
0
4

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.

Improve this answer
0
0

Wholeheartedly agree with the other answers but just wanted to offer an intuitive explanation why OR is virtually always less efficient than AND in the absence of optimization, based on first principles and nothing unique to SQL.

If all WHERE conditions are linked with AND, they can each only narrow the initial set (based on the SELECT and FROM) or leave it unchanged. The set will never grow. Once part of a set is excluded it is excluded for good and never needs to be looked at again. Consequently there is also no need for a deduplication step; with each condition evaluated, you can just feed the resulting set from the prior condition into the next one and whatever you end up with is your answer.

However, if any condition is an OR condition, you can no longer discard part of the initial set just because it fails to satisfy one condition, as it might satisfy a different one. You have to evaluate each branch of the OR against the entire initial set almost as though it were an independent query. You then wind up with a set for each OR branch, and then those sets must be joined via a logical union so you don't get duplicate results.

Just thinking about it in these terms it's obvious OR involves more work than pure ANDs unless optimization is employed. And, luckily for us, database programmers are geniuses. They're skilled mathematicians in addition to being skilled programmers. They've invented all kinds of optimizations so the vast majority of the time you don't get penalized for using OR. Indeed, with proper optimization some OR queries can even be faster than AND. A trivial example is WHERE [something extremely complicated] OR TRUE, which should be easily optimizable and thus a lot faster than WHERE [something extremely complicated] AND TRUE. In fact any time you know that one set on either side of an OR is a subset of the other, you can just evaluate the one that's larger and ignore the other and skip the union. This is where you just have trust the DB programmers here to have figured out the best way to handle these kinds of scenarios.

All that said, sometimes you can absolutely get hosed by OR, or by UNION for the same reasons. (Remember SELECT * FROM T WHERE [A] OR [B] is functionally equivalent to SELECT * FROM T WHERE [A] UNION SELECT * FROM T WHERE [B]). Of course that doesn't mean you should never use them, but you should be aware that you're more dependent on the optimizer and its potential quirks when you do.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.