SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


performance


performance

Author
Message
sathiyan00
sathiyan00
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34225 Visits: 13270
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sathiyan00
sathiyan00
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 475
i want the product which is there in both the brand not just in one
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34225 Visits: 13270
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
TheSQLGuru
TheSQLGuru
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: 16800 Visits: 8600
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
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34225 Visits: 13270
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
TheSQLGuru
TheSQLGuru
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: 16800 Visits: 8600
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
Erland Sommarskog
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2743 Visits: 872
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
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22228 Visits: 19362
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