IN vs WHERE EXISTS 1

[ptheriault]

Can someone shed some light on the best way to do this. I have two queries that are the same, excpet one uses where exists and the other uses IN. I ran a query plan for both of them and the cost is exactly the same. My question is which method is better?

WHERE   EVALUATION = '11/30/2006'
      AND EXISTS (select PI_CarrierCode 
                  FROM  pricing_PaperInformation_PI ppi 
                JOIN Partner_Paper pp ON PP_PaperID = PI_ID
                  WHERE PP_PartnerID = @PartnerID
                  AND CC.CARRIER_CODE = PI_CarrierCode )

OR

WHERE EVALUATION = '11/30/2006'
 AND CC.CARRIER_CODE IN ( select PI_CarrierCode 
                     FROM pricing_PaperInformation_PI ppi 
                  JOIN Partner_Paper pp ON PP_PaperID=PI_ID
                    WHERE PP_PartnerID = @PartnerID )

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Honestly, I think the two are interchangable. Books Online doesn't list much of a difference. You can search for NULLs with either, so that can't possibly be it. I just always use IN from personal preference.

Of course, if I'm filtering based on a sub-query like you are, I'd rather use an Inner Join to get the record set down before the finally WHERE clause processing. I use IN for specifically iterated values (1, 2, 56, 210) or NOT IN when I use subqueries and am looking for values that are outside of those in a non-query related table.

One thing I believe, though. The more WHERE clauses you have, the longer something takes to process. I could be wrong, but if you're pulling off of large tables and not using indexed columns in the subquery's WHERE clause, your first statement will potentially take longer to process because it has more conditionals in it. In this case you've got the same cost, but that doesn't mean that will always be true.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Thanks cat,
The pricing_PaperInformation_PI table only has 8 rows and it will be fairly static. Only when we add new partners will we add records to this table. I thought it was a 1:1 relationship with the carrier_code but I found out that a partner could have more than one record in the pricing_PaperInformation_PI table. I created the statement that uses the IN and a developer created the statement that uses the where exists. We have 25 procedures to create that will use this syntax so I wanted to settle on a standard.


- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

NP. Glad I could help.

BTW, which ever one you pick, you might consider monitoring it as the table grows to see if performance is remaining consistant or if it's getting more and more off kilter. If the later, then consider trying out the other one.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"