performance

Question

Post reply

performance

sathiyan00

SSCommitted

Points: 1879
More actions
September 5, 2013 at 11:26 pm

#282769

Hi all,
Tablea
id product_id brand_id
1 1 1
2 2 1
3 3 2
4 1 1
5 4 2
I need to select the product which is there all brand
USING SET OPERATOR
select product from tablea WHERE brand_id=1
INTERSECT
select product from tablea WHERE brand_id=2
USING JOIN
SELECT product from tablea t
JOIN tablea t1 ON t.product=t1.product and t.brand_id=1 and t2.brand_id=2
which one is better in performance.or any other optimised way to achieve the same
thanks
sathiyan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Why not just do the following?

select product from tablea WHERE brand_id IN (1,2);

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sathiyan00 SSCommitted Points: 1879 More actions · Answer 2

i want the product which is there in both the brand not just in one

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

sathiyan00 (9/6/2013)
i want the product which is there in both the brand not just in one

Right. That wasn't entirely clear from your original post.

I guess they're about the same performance (I haven't found an article or blog post that contradicts this). You can test it out with a large number of rows and compare execution plans and execution times.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Personally I would not use INTERSECT. It is a relatively new operator in SQL Server and thus could be exposed to some bugs and/or not have all the efficiency "short-cuts" that other key words have had built in over time.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

TheSQLGuru (9/6/2013)
Personally I would not use INTERSECT. It is a relatively new operator in SQL Server and thus could be exposed to some bugs and/or not have all the efficiency "short-cuts" that other key words have had built in over time.

It was introduced in SQL Server 2005. How long does it take before it is not "relatively new" anymore?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Koen Verbeeck (9/6/2013)
TheSQLGuru (9/6/2013)
Personally I would not use INTERSECT. It is a relatively new operator in SQL Server and thus could be exposed to some bugs and/or not have all the efficiency "short-cuts" that other key words have had built in over time.
It was introduced in SQL Server 2005. How long does it take before it is not "relatively new" anymore?

For features that are "difficult" and/or don't get much attention it could be never. MERGE anyone?!? 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

TheSQLGuru (9/6/2013)
For features that are "difficult" and/or don't get much attention it could be never. MERGE anyone?!? 🙂

I see MERGE in use every now and then.

INTERSECT admittedly has some exotic feeling over it, but for this particular problem it seems like a good choice. Note that the JOIN query could serve from a DISTINCT, seems it appears that (product_id, brand_id) is not a key in the table.

However, if we generalise the problem to "show me all products that is in all brands", and there can be any number of brands, none of the options in the original post fits the bill. That's a relational division, and that is when you need Peter Larsson to tell you the most efficient solutions.

One way to write that query would be:

SELECT product_id

FROM tbl

GROUP BY product_id

HAVING COUNT(DISTINCT brand_id) = (SELECT COUNT(DISTINCT brand_id) FROM tbl)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

Just another way to do it. It should perform better than the JOIN, but I'm not sure about INTERSECT.

select product_id

from tablea a

WHERE brand_id=1

AND EXISTS(

select product_id

from tablea b

WHERE brand_id=2

AND a.product_id = b.product_id)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2