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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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