Query efficiency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query efficiency

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2012-05-12 : 12:27:50

[code]

Using the IN statement

select SlipSales.*
from SlipSales
where SlipSales.Id in (select DocumentSales.SlipSalesId from DocumentSales /* where filter goes here */ )

Using the INNER JOIN statement

select SlipSales.*
from SlipSales
inner join (
select DocumentSales.SlipSalesId
from DocumentSales
/* where filter goes here */
) as header
on header.SlipSalesId = SlipSales.Id
[/code]

From the above query, which of the two is more efficient? Thanks in advance.
.
.
.
.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 14:50:29

quote:
Originally posted by jonasalbert20

Using the IN statement

select SlipSales.*
from SlipSales
where SlipSales.Id in (select DocumentSales.SlipSalesId from DocumentSales /* where filter goes here */ )

Using the INNER JOIN statement

select SlipSales.*
from SlipSales
inner join (
select DocumentSales.SlipSalesId
from DocumentSales
/* where filter goes here */
) as header
on header.SlipSalesId = SlipSales.Id

From the above query, which of the two is more efficient? Thanks in advance.
.
.
.
.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...



By Looking at the execution plan of both of the query you can get to about the total batch time taken by the each of the query.

Yo can also implement this by  EXISTS operator which is faster than I IN operator.Also it's depend  on data also.

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 21:18:29

depending on index availability inner join should have upper hand. Also EXISTS as specified by Vijay will perform better than IN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2012-05-13 : 03:29:01

quote:
Originally posted by visakh16

depending on index availability inner join should have upper hand. Also EXISTS as specified by Vijay will perform better than IN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

That's a BIG myth.A optimizer is now smart enough to understand the a correlated sub-queries either using IN and EXISTS is one and the same.
Both the queries will use a Nested Loop join.

In fact in some cases a foreign key constraint along with NOT NULL can actually eliminate the physical join itself if data from the primary key table is required to check existence of the row.

After Monday and Tuesday even the calendar says W T F ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources