Refactoring a DELETE query using NOT EXISTS — need expert feedback

  • 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!

  • 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