Refactoring a DELETE query using NOT EXISTS — need expert feedback

• abdalah.mehdoini

  Ten Centuries

  Points: 1023

  More actions

  April 16, 2025 at 2:12 pm

  #4579971

  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!

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  April 17, 2025 at 3:10 pm

  #4580541

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.