April 17, 2008 at 12:53 am
Hi All,
I want to know which query is best one from the below queries,
Delete firstt from firstt t , secondt N
where t.Id = N.Id
[OR]
delete from firstt where id in(select id from secondt)--5,25
[OR]
Delete firstt from firstt t inner join secondt N
on t.Id = N.Id
Thanks and Regards,
N.Prabhakaran
April 17, 2008 at 1:32 am
Either of these. It really comes down to how many records there are in the two tables.
DELETEt
FROMFirstt AS t
WHEREEXISTS (SELECT * FROM Secondt AS y WHERE y.ID = t.ID)
DELETEt
FROMFirstt AS t
INNER JOINSecondt AS y ON y.ID = t.ID
N 56°04'39.16"
E 12°55'05.25"
April 17, 2008 at 1:33 am
The first and the third are equivalent, though the first is using an older form of joins that is not recommended any more.
The 2nd and 3rd should be equivalent. The optimiser's smart enough to usually resolve an IN into an inner join (or vis versa)
Best way to know which is faster - test them out.
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
April 18, 2008 at 8:00 am
Try using Explain Plan or Show Plan to show how the optimizer plans on executing the queries. There's a good chance that your 2 queries (1 and 3 are semantically the same, so I'm counting them as 1 query) will have the same plan.
Thanks,
Greg
April 18, 2008 at 8:08 am
You'd have to test on your system, as mentioned, and I'd check the execution plans on both, but I'd lean towards #1.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply