Help me with the SYNTAX for the DELETE statement

Question

Post reply

Help me with the SYNTAX for the DELETE statement

Viewing 5 posts - 16 through 19 (of 19 total)

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

mw_sql_developer SSCoach Points: 19445 More actions · Answer 1

Luis, Yes

Reason: EXISTS is just a another subquery that runs for each row that was fetched. Subqueries take time.

The LEFT JOIN will figure out the data set just once ( of course you need to have that where clause as well ).

Anyhow the final decision is yours...

EXISTS :Makes the syntax look clear, Good for smaller data sets

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

mw112009 (9/25/2015)
Luis, Yes
Reason: EXISTS is just a another subquery that runs for each row that was fetched. Subqueries take time.
The LEFT JOIN will figure out the data set just once ( of course you need to have that where clause as well ).
Anyhow the final decision is yours...
EXISTS :Makes the syntax look clear, Good for smaller data sets

Bet you a pint you can't demonstrate this with an execution plan :blink:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

mw112009 (9/25/2015)
Luis, Yes
Reason: EXISTS is just a another subquery that runs for each row that was fetched. Subqueries take time.
The LEFT JOIN will figure out the data set just once ( of course you need to have that where clause as well ).
Anyhow the final decision is yours...
EXISTS :Makes the syntax look clear, Good for smaller data sets

I'm sorry, but your assumptions are wrong. When using EXISTS (or IN), SQL Server Engine will normally use a semi join (or anti semi join) which will run faster than a normal join because it only cares for the rows on the main table. Depending on the physical operation used (Merge, hash or nested loops) the subquery can be executed just once or multiple times.

This is explained in the articles posted which have actual code that you can reproduce to confirm their statements. If you share a way in which you can prove that the LEFT JOIN is faster than the EXISTS, please do to spread the knowledge.

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

ChrisM@Work (9/25/2015)
mw112009 (9/25/2015)
Luis, Yes
Reason: EXISTS is just a another subquery that runs for each row that was fetched. Subqueries take time.
The LEFT JOIN will figure out the data set just once ( of course you need to have that where clause as well ).
Anyhow the final decision is yours...
EXISTS :Makes the syntax look clear, Good for smaller data sets
Bet you a pint you can't demonstrate this with an execution plan :blink:

Indeed. It's actually the LEFT JOIN/IS NULL that typically has to do more work, since it has to return all the rows from the join and only then filter out the unmatched rows. The anti semi join used by NOT EXISTS will avoid the need for that filter after the join.

Of course, all that's nicely documented in those links I posted. They have gone unread, methinks 🙂

Cheers!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

mw_sql_developer

SSCoach

Points: 19445

September 26, 2015 at 7:58 am

#1829701

OK, agreed!

Thanks for the HeadsUp!

Good work....