Minor code change will it make a difference.

  •  

    Please let me know your thoughts on the below. Updated a small portion of the code. Just want to make sure it will give the same result.

    AND EXISTS (SELECT *

    FROM tbTask

    JOIN tbX_Dim_LatestVersion X ON X.X_ID = OD_X_ID

    AND X.X_Id = @X_ID

    WHERE Origin_ID = FI_ID)

    IS the below code does the same thing as well..

    AND EXISTS (SELECT 1 -- Updated to 1.

    FROM tbTask

    JOIN tbX_Dim_LatestVersion X ON X.X_ID = OD_X_ID

    AND X.X_Id = @X_ID

    WHERE Origin_ID = FI_ID)

  • Same result, confirmed.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I'm curious.  What was the reason you made this change, and what kind of differences in the results were you expecting?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  •  

    Not a big performance improvement however replacing * where ever not needed. Since tb's have billions of records and '*' can or maybe a bottleneck...if anything the code tries to find in ~billions records.

  • That is not true.  You can verify this by looking at the execution plans.  You are doing a lot of work for nothing.

    If you are using EXISTS, the select portion of the exists is evaluated last.  Since the columns, or lack of them, are not needed to satisfy the results of the query, they are not used.

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Sure, I will have a look at the execution plans. Thank you!

  • Replacing SELECT * with a list of the columns you want to return would usually improve performance (unless you listed every column), but in this case it has no effect. The column list to be returned for the SELECT statement in an EXISTS are ignored. As soon as it seems a matching row in the EXISTS subquery it has all it needs to evaluate whether a child row exists.

    As an demonstration, this code runs successfully:

    SELECT SCHEMAS.SCHEMA_ID
    FROM SYS.SCHEMAS
    WHERE EXISTS (
    SELECT 1/0
    FROM SYS.TABLES AS TABLES_SUB
    WHERE TABLES_SUB.SCHEMA_ID = SCHEMAS.SCHEMA_ID
    )

Viewing 7 posts - 1 through 6 (of 6 total)

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