Post reply

which query is performance wise best?

  • April 17, 2008 at 12:53 am

    #189166

    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

    #803489

    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

    #803490

    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

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

  • April 18, 2008 at 8:00 am

    #804360

    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

    #804370

    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.

Viewing 5 posts - 1 through 4 (of 4 total)

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