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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 21, 2019 at 5:22 pm
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/
October 21, 2019 at 5:37 pm
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.
October 21, 2019 at 5:45 pm
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/
October 21, 2019 at 5:47 pm
Sure, I will have a look at the execution plans. Thank you!
October 22, 2019 at 6:23 am
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