Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


performance


performance

Author
Message
sathiyan00
sathiyan00
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sathiyan00
sathiyan00
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 475
i want the product which is there in both the brand not just in one
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
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
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 866
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search