Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

performance Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 11:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
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
Post #1492037
Posted Friday, September 6, 2013 1:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 13,275, Visits: 11,062
Why not just do the following?

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





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1492058
Posted Friday, September 6, 2013 3:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
i want the product which is there in both the brand not just in one
Post #1492113
Posted Friday, September 6, 2013 3:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 13,275, Visits: 11,062
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1492121
Posted Friday, September 6, 2013 6:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 4,400, Visits: 6,261
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 at GMail
Post #1492184
Posted Friday, September 6, 2013 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 13,275, Visits: 11,062
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1492200
Posted Friday, September 6, 2013 10:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 4,400, Visits: 6,261
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 at GMail
Post #1492324
Posted Friday, September 6, 2013 3:40 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:20 PM
Points: 807, Visits: 725
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)



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1492441
Posted Friday, September 6, 2013 4:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:33 PM
Points: 3,771, Visits: 8,460
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1492457
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse