Post reply

IF EXISTS statement very slow

  • July 23, 2010 at 1:49 pm

    #1197741

    Sure, you can never guarantee what the optimizer is going to do.

    But that's a far cry from a blanket statement of "SQL does not short-circuit IFs".

    Is there a reason to refuse to allow SQL the chance to short-circuit two additional queries if it can?

    Scott Pletcher, SQL Server MVP 2008-2010

  • July 23, 2010 at 2:03 pm

    #1197752

    scott.pletcher (7/23/2010)

    Check each query separately for EXISTS, and put the most likely to be true first.

    This should allow you to get rid of the UNION (which should be UNION ALL if you intend to keep them).

    There is no need to run all the queries if the first one returns a result -- right? Or did I misunderstand something?

    IF EXISTS (

    SELECT TOP 1...

    )

    OR EXISTS (

    SELECT TOP 1...

    )

    OR EXISTS (

    SELECT TOP 1...

    )

    ...

    Thanks Scott, this is part of a validation process and records may exist perhaps once or twice a year so the majority of times all of the queries will run.

    The temp table solves another issue I have always had with EXISTS logic. Usually when the EXISTS clause is true I additionally want to do something with the data, in this case I want to email the data.

    With the temp table I have my data right there and do not have to run the query again.

Viewing 2 posts - 16 through 16 (of 16 total)

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