April 16, 2025 at 2:12 pm
Hi everyone,
I'm working on optimizing a DELETE query used in a data cleanup process, and I’d like your expert opinion on a refactored version I’m considering. Here's the current version
DELETE R
FROM #RESULTS_DEM AS R
INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM
WHERE NOT EXISTS (
SELECT TOP 1 1
FROM @LIST_STA_DEM AS W
WHERE (
(W.COD_TRA_CLI = '010100' OR W.COD_TRA_CLI = DEM.COD_TRA_CLI)
AND W.STA_DEM = DEM.STA_DEM
)
UNION ALL
SELECT TOP 1 1
FROM @LIST_STA_DEM AS W
INNER JOIN dbo.DT_INT AS D ON D.COD_TRA = W.COD_TRA_CLI
WHERE D.NUM_DEM = DEM.NUM_DEM
AND W.STA_DEM = DEM.STA_DEM
)
I’m thinking of replacing it with the following version, which I find cleaner and potentially more performant:
DELETE R
FROM #RESULTS_DEM AS R
INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM
WHERE NOT EXISTS (
SELECT 1
FROM @LIST_STA_DEM AS W
WHERE
(W.COD_TRA_CLI = '010100' OR W.COD_TRA_CLI = DEM.COD_TRA_CLI)
AND W.STA_DEM = DEM.STA_DEM
)
AND NOT EXISTS (
SELECT 1
FROM @LIST_STA_DEM AS W
INNER JOIN dbo.DT_INT AS D ON D.COD_TRA = W.COD_TRA_CLI
WHERE
D.NUM_DEM = DEM.NUM_DEM
AND W.STA_DEM = DEM.STA_DEM
)
My goals:
Improve readability and possibly performance
Avoid UNION ALL inside subqueries, as I just need two independent existence checks
Ensure the logic remains equivalent
Do you think this new version is functionally identical to the original?
Any thoughts on potential performance differences or how SQL Server might interpret this change?
Thanks a lot in advance for your feedback!
April 17, 2025 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy