Delete Statement: Why is this better? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Delete Statement: Why is this better?

Author

Topic

souLTower
Starting Member

39 Posts

Posted - 2008-11-19 : 11:26:17

Good day Forum.

In the app I'm working on I have seen the following delete statement:


DELETE from tblFOO where fooKey IN
(
  Select F.fooKey from tblFoo F inner join tblBar B 
  ON F.abc = B.abc

  inner join tblBaz B2 ON B.abc = B2.abc

  inner join tblGamma G ON B2.abc = G.abc
  WHERE (list of conditions)
)

I thought this would be less optimal than the following:


DELETE F FROM 
tblFoo F inner join tblBar B ON F.abc = B.abc

inner join tblBaz B2 ON B.abc = B2.abc

inner join tblGamma G ON B2.abc = G.abc
WHERE (list of conditions)

So I plugged both into query analyzer and the first one evaluates to 40%, the second to 60%.

Why would this be?

Thanks

God Bless

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 11:30:18

The percentages in Query Analyzer depends on many other things.
Check with SQL Profiler instead.

E 12°55'05.63"
N 56°04'39.26"

souLTower
Starting Member

39 Posts

Posted - 2008-11-19 : 11:34:46

So I should be comparing reads, cpu time, etc right?

God Bless

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 14:00:54

Yes.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources