Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query which i first wrote as (query has been simplified a bit) :

select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = c.id or p.catid = c.id and p.deleted = 1 and p.statusid = 1 
group by c.name

This took 8 seconds.

If i changed it to the following it runs in under 1 second:

   select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
   inner join product p on p.indID = c.id or p.catid = c.id
   where p.deleted = 0 and p.statusid =1 
group by c.name

I'd like to understand why there's such a huge difference in performance. Not only is the performance worse but it seems to be ignoring my conditional statements on the first sql query it's counting records regardless of what p.deleted or p.statusid is

What I am not understanding about how joins work? This is being run on mssql 2012

share|improve this question
1  
This query is not valid (I guess you over-simplified it). You can't use c.CategoryTypeID in the SELECT list without an aggregate when it is not contained in the group by –  ypercube Apr 6 '13 at 1:25
add comment

1 Answer

up vote 3 down vote accepted

The OR operator in your first statement is making the output of both queries totally different. If you put brackets around the two items that are separated by the OR statement you will see the results are the same.

SELECT ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) 
FROM category c 
    INNER JOIN product p on (p.indID = c.id or p.catid = c.id) 
        and p.deleted = 1 
        and p.statusid = 1 
GROUP BY c.name

OR is evaluated after AND...

share|improve this answer
    
ahh yes i see - stupid mistake on my part. Thanks for pointing it out. –  merk Apr 9 '13 at 7:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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