Poor Performance on IF, OR

Question

I have a stored procedure similar to something like this:

SELECT a.Name AS Author,
       b.Price,
       b.PublishDate,
       b.Title,
       b.ISBN
FROM   Book b
       INNER JOIN Author a ON b.Id = a.BookId
WHERE  a.Id = @authorId OR @author = 0

So when the input parameter @author is > 0 it returns records on a specific author, if 0 it returns records on every author.

Even when I pass any values greater than 0 for @author, the performance is sub-par (though it's not terribly slow). I then notice if I remove the OR clause the stored procedure runs much faster (at least 80% faster for the same input parameter). I then try to do something like:

IF @author > 0 
  --select records on a specific author
ELSE
  --select everything

However, the performance is pretty the same as OR. Is there anyway to code something like this in a different way to gain better performance?

It's more complicated than you might think. Here is a link that goes into detail about optimising optional parameters. It has links to other even more detailed information about it also. Have a read and see if it helps. — Nick.Mc, May 22 '15 at 00:31
If you are using SQL Server 2008 Standard or above I believe as @Sqluser said you can use an execution plan to see where the slow down is. This should also give you an index suggestion if one isn't already on the table and columns in the query — Nate S., May 22 '15 at 00:41
Doh! here it is: http://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure — Nick.Mc, May 22 '15 at 00:57
You have authorId and author. Do you mean to have two different? — paparazzo, May 22 '15 at 09:10

sqluser · Answer 1 · 2015-05-22T01:13:21.033

As Nate S said, you need an index

But in my experience, I got better performance using IN rather than OR

SELECT a.Name AS Author,
       b.Price,
       b.PublishDate,
       b.Title,
       b.ISBN
FROM   Book b
       INNER JOIN Author a ON b.Id = a.BookId
WHERE  a.Id IN (@authorId, 0)

But always try to see your query in execution plan and compare the result

Update

If you want conditional WHERE clause, you can use try these alternatives.

Using CASE statement

SELECT a.Name AS Author,
       b.Price,
       b.PublishDate,
       b.Title,
       b.ISBN
FROM   Book b
       INNER JOIN Author a ON b.Id = a.BookId
WHERE  a.Id  = CASE @authorId WHEN 0 THEN a.Id ELSE @authorId END

Also if you set the @authorId = NULL rather than 0, you can use ISNULL function but better not to use functions in WHERE clause

SELECT a.Name AS Author,
       b.Price,
       b.PublishDate,
       b.Title,
       b.ISBN
FROM   Book b
       INNER JOIN Author a ON b.Id = a.BookId
WHERE  a.Id  ISNULL(@authorId, a.Id)

Unfortunately that's different logic to what is required, and `IN`'s get converted to `OR`'s anyway behind the scenes. — Nick.Mc, May 22 '15 at 00:58
Thanks @Nick.McDermaid, I think I got the point you mentioned in your comment and add some more alternatives. Hopefully can be useful for the OP. — sqluser, May 22 '15 at 01:14

Nate S. · Answer 2 · 2015-05-22T00:51:06.110

1

You should add an index to the table, including any columns in the where clause. This should add an index on the authors table ID column.

CREATE INDEX 'INDEX NAME'
    ON dbo.Author (ID);

CREATE INDEX (Transact-SQL)

edited May 22 '15 at 00:51

answered May 22 '15 at 00:27

Nate S.

1,117
15
31

score 0 · Answer 3 · answered May 22 '15 at 01:16

Use below query and i am sure you will get your performance

SELECT a.Name AS Author,
           b.Price,
           b.PublishDate,
           b.Title,
           b.ISBN
    FROM   Book b
           INNER JOIN Author a ON b.Id = a.BookId
    WHERE  a.Id IN (@authorId, 0)
    option(OPTIMIZE for(@authorId =10))

score 0 · Answer 4 · edited May 22 '15 at 14:52

0

Try this
Joins hate OR

SELECT a.Name AS Author,
       b.Price,
       b.PublishDate,
       b.Title,
       b.ISBN
FROM   Book b
       INNER JOIN Author a ON b.Id = a.BookId and a.Id = @authorId 
union
SELECT a.Name AS Author,
       b.Price,
       b.PublishDate,
       b.Title,
       b.ISBN
FROM   Book b
       INNER JOIN Author a ON b.Id = a.BookId and @author = 0

edited May 22 '15 at 14:52

Nate S.

1,117
15
31

answered May 22 '15 at 06:46

paparazzo

44,497
23
105
176

Poor Performance on IF, OR

4 Answers4