which one is better

Question

Post reply

which one is better

dr_csharp

SSCommitted

Points: 1765
More actions
May 31, 2010 at 6:52 am

#221685

hi friends

i have faced to two select command with same results. i want to know which one is faster and has better performance?

Select orderId,orderDate From Orders As o Where exists ( Select od.orderId From orderDetails as od Where o.orderId=od.orderId AND od.unitPrice>3 )

And the second is :

Select orderId,orderDate From orders Inner join orderDetails On orders.orderId=orderDetails.orderId Where orderDetails.unitePrice>3

Thanks 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

If you test them out, which is faster? Do note that they are not necessarily equivalent queries

These may be of interest -

http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

GilaMonster (5/31/2010)

If you test them out, which is faster? Do note that they are not necessarily equivalent queries

These may be of interest -

http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

thanks, it was helpful.

do u know any references to understand these differences scientifically ?

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 3

Gopi Muluka

SSCarpal Tunnel

Points: 4594

May 31, 2010 at 7:10 am

#1174493

I think both the queries will not give same results, Please check again specially for the case of (ONE -> MANY), If a single order may have multiple detail records then both will give different results

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 4

Gopi Muluka

SSCarpal Tunnel

Points: 4594

May 31, 2010 at 7:12 am

#1174494

You may be interested in following article

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

Gopi Muluka

SSCarpal Tunnel

Points: 4594

May 31, 2010 at 7:16 am

#1174495

change second query to below to match the resultsets

Select Distinct orderId,orderDate



From  orders

Inner join orderDetails

On orders.orderId=orderDetails.orderId

Where orderDetails.unitePrice>3

In this case both will have same kind of performance;-)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Gopi Muluka (5/31/2010)

change second query to below to match the resultsets

Select Distinct orderId,orderDate
From orders Inner join orderDetails On orders.orderId=orderDetails.orderId Where orderDetails.unitePrice>3

In this case both will have same kind of performance;-)

'

Hell no they won't. Distinct is an expensive operation, adding a distinct in to the join is near guaranteed to make it slower than the exists if it wasn't already.

The distinct will make them return the same data, not perform the same.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

dr_csharp (5/31/2010)

do u know any references to understand these differences scientifically ?

My explanations weren't scientific enough? Did you read through the posts referenced in the first of those links?

What do you still want to know?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 8

GilaMonster (5/31/2010)

dr_csharp (5/31/2010)

do u know any references to understand these differences scientifically ?

My explanations weren't scientific enough? Did you read through the posts referenced in the first of those links?

What do you still want to know?

no it was really helpful and enough,i said scientific to know if exist any references that learn me the parameters and what happen in background !

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Sorry, still not completely clear what you're looking for? Background on the query operators? On how the queries are processed? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 10

GilaMonster (5/31/2010)

Sorry, still not completely clear what you're looking for? Background on the query operators? On how the queries are processed? Something else?

yes, i mean both about background on the query operators and the way that queries are processed. (i like to know the concepts. )

the links that you suggested was intuitive and there were no causality, it just rely on comparisons and i wanna understand why these results occur ?

Thanks

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

Hmm...

Try the latter 3 books in the Inside SQL Server 2005 series - T-SQL Querying, T-SQL Programming and Query Tuning and Optimisation. Also Grant's e-book (available here) http://qa.sqlservercentral.com/articles/books/65831

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

GilaMonster (5/31/2010)

Hmm...

Try the latter 3 books in the Inside SQL Server 2005 series - T-SQL Querying, T-SQL Programming and Query Tuning and Optimisation. Also Grant's e-book (available here) http://qa.sqlservercentral.com/articles/books/65831

:w00t: You know you're about to have it when an expert suggests you to read 4 books to answer a single question :hehe:.