Trying to make this faster using not exists

• krypto69

  SSChampion

  Points: 13622

  More actions

  November 12, 2026 at 2:11 pm

  #5091737

  Hi

  I have this query that is slow during the 'not in' part of the statement:

          LEFT JOIN dbo.benefitplan BP ON BP.benefitplan_id = ci.benefitplan_id
          LEFT JOIN dbo.benefitplan_type pt ON pt.benefitplan_type_id = BP.benefitplan_type_id
          WHERE k.BatchSeq BETWEEN '1' AND '1000000'
  AND ci.claim_procedure_id NOT IN (35381320,35381310,35381304,35381305,35381311,35381306,35345478,35381307,35381293,35381308,35392585,
  35392587,35381298,35381321,35345407,35381324,35392582,35381326,35381299,35381325,35381295,35381323,35345410,35381297,
  35336186,35381314,35345409,35381319,35392584,35336187,35336218,35336217,35381312,35381294,35381303,35381296,35381318,
  35381300,35381327,35381315,35381317,35336185,35381309,35381322,35392586,35381313,35345408,35392583,35345477,35381316)
           where k.BatchSeq BETWEEN '1' AND '1000000'

  So I thought I could use 'not exists' to speed thing up:

   

     where k.BatchSeq BETWEEN '1' AND '1000000'  
     
  AND not exists
     
     (select ci.claim_procedure_id from claim_procedure ci where ci.claim_procedure_id IN (35381320,35381310,35381304,35381305,35381311,35381306,35345478,35381307,35381293,35381308,35392585,
  35392587,35381298,35381321,35345407,35381324,35392582,35381326,35381299,35381325,35381295,35381323,35345410,35381297,
  35336186,35381314,35345409,35381319,35392584,35336187,35336218,35336217,35381312,35381294,35381303,35381296,35381318,
  35381300,35381327,35381315,35381317,35336185,35381309,35381322,35392586,35381313,35345408,35392583,35345477,35381316))
          
          
  
  

   

  But I am getting zero records back. It's making me crazy (crazier)..what am I doing wrong here?