asked on

This query is running for more than 45 minutes, can any one help

This is the query i am running its the query to get the data from different tables to one column which is [Updated bucket], now I have added indexes to the columns whose tables has most number of records. As it is updating every week, probably every day I am not using 'WITH (NOLOCK)'. Can any one tell the best way to reduce the execution time?

,CASE
            --Is nonpar waiver
            WHEN z.SRC_PAR_CD IN ('E','O','S','W')
            THEN 'Nonpar Waiver'
  
      --**Amendment Mailed**
        WHEN z.PROV_TAX_ID IN
                 (SELECT DISTINCT b.PROV_TIN
            FROM PACT.dbo.SQS_Mailed_TINs_010614 b
                 where not exists (select * from dbo.sqs_objector_TINs t where b.PROV_TIN = t.prov_tin))
            THEN 
                  (SELECT DISTINCT b.Mailing 
                  FROM PACT.dbo.SQS_Mailed_TINs_010614 b
                  WHERE z.PROV_TAX_ID = b.PROV_TIN
                  )
		--**Amendment Mailed Wave 3 and 4**
		WHEN z.PROV_TAX_ID In
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Amendment Mailed (3rd Wave)'
				and not exists (select * from dbo.sqs_objector_TINs t where qz.PROV_TIN = t.prov_tin))
		THEN 'Amendment Mailed (3rd Wave)'
		
		
		 WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Amendment Mailed (4th Wave)'
				and not exists (select * from dbo.sqs_objector_TINs t where qz.PROV_TIN = t.prov_tin))
		THEN 'Amendment Mailed (4th Wave)' 
		
            --Is Puerto Rico of Lifesynch
            WHEN z.PROV_TAX_ID IN
                  (SELECT DISTINCT a.PROV_TAX_ID 
                  FROM PACT.dbo.SQS_NonPar_PR_LS_TINs a
                  WHERE a.Bucket <> 'Nonpar'
                  )
            THEN 
                  (SELECT DISTINCT a.Bucket
                  FROM PACT.dbo.SQS_NonPar_PR_LS_TINs a
                  WHERE a.PROV_TAX_ID = z.PROV_TAX_ID)

            --**Top Objecting Systems**
            WHEN z.SYSTEMNAME IN 
                  ('ADVENTIST HEALTH SYSTEM','ASCENSION HEALTH ALLIANCE','AULTMAN HEALTH FOUNDATION','BANNER HEALTH SYSTEM','BERT FISH MEDICAL CENTER','BETHESDA MEMORIAL HOSPITAL','BJC HEALTHCARE','BLOUNT MEMORIAL HOSPITAL','BOCA RATON REGIONAL HOSPITAL','CAROMONT HEALTH SYSTEM','CATHOLIC HEALTH INITIATIVES','CATHOLIC HEALTHCARE PARTNERS','CHRISTUS HEALTH',/*'CLEVELAND CLINIC HEALTH SYSTEM',*/'COLUMBUS REGIONAL HEALTHCARE SYSTEM','COMMUNITY HEALTH SYSTEMS, INC','COXHEALTH','HCA','HEALTH MANAGEMENT ASSOCIATES','HUNTSVILLE HOSPITAL HEALTH SYSTEM','INTEGRIS HEALTH','JUPITER MEDICAL CENTER','LEE MEMORIAL HEALTH SYSTEM','MARTIN MEMORIAL HEALTH SYSTEM','MERCY','MT SINAI MEDICAL CENTER (MIAMI)','MUNROE REGIONAL MEDICAL CENTER','NORMAN REGIONAL HEALTH SYSTEM','NORTHSIDE HEALTH SYSTEM','SHANDS HEALTHCARE','SISTERS OF MERCY - SPRINGFIELD, MO','SSM HEALTH CARE','ST LUKES HEALTH SYSTEM','SUMMA HEALTH SYSTEM','SUSQUEHANNA HEALTH SYSTEM','TBD -- TRINITY HEALTH - CATHOLIC HEALTH EAST','UNIVERSITY OF MISSOURI HEALTH SYSTEM','UNIVERSITY OF NEW MEXICO HOSPITALS','UNIVERSITY OF UTAH HEALTH CARE')
            THEN 'Top Objecting Systems' 

            --**Other Objecting Hospitals**
            WHEN (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI') and z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        PACT.dbo.SQS_EDW_Source z
                  WHERE 
                        z.PROV_TAX_ID IN 
                              (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
                                                WHERE obj.[Objector?] = 'Yes' 
                                         --       AND obj.[TINs Provided By:] <> 'Unknown'--PACT.dbo.SQS_Objectors_01202014b
                              )
                              AND z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                             -- AND z.SEQUEST_AMT < 31000
                            -- AND z.SYSTEMNAME <> 'NULL'
                  ))
            THEN 'Other Objecting Hospitals'
       
  --      --**Objecting Physicians**
        WHEN 
                ( z.LCLM_RSTMT_TREND_CAT_CD NOT IN ('HO','HI') and  z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              z.PROV_TAX_ID
                        FROM 
                              PACT.dbo.SQS_EDW_Source z
                        WHERE 
                              z.PROV_TAX_ID IN 
                                    (SELECT DISTINCT obj.TIN 
                                    FROM PACT.dbo.SQS_Provider_Tracking obj --PACT.dbo.SQS_Objectors_01202014b
                                    WHERE obj.[Objector?] = 'Yes'
                               --     AND obj.[TINs Provided by:] <> 'Unknown'
                                    )
                        AND z.LCLM_RSTMT_TREND_CAT_CD NOT IN ('HO','HI')
                        ))
            THEN 'Objecting Physicians'   
            
            
--**********ALL OBJECTORS SHOULD HAVE BEEN BUCKETED AT THIS POINT IN THE QUERY**********


--      --**Non-Objecting Hospitals**
            WHEN (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI') and z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        PACT.dbo.SQS_EDW_Source z
                  WHERE 
                        z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                        --AND z.SEQUEST_AMT < 31000
                      --  AND z.SYSTEMNAME <> 'NULL'
                  ))
            THEN 'Non-Objecting Hospitals'            
        
  
  
  --		**Outstanding Contracts for Review**
		 WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Non-Objecting Bilateral Physicians')
		Then 'Non-Objecting Bilateral Physicians'
		
			When  z.prov_tax_id in 
			(select distinct 
				p.prov_tax_id
				from dbo.SQS_CoC_Potential_Mail_List p
				where p.amendmentrights <> 'Unilateral')
		THEN 'Non-Objecting Bilateral Physicians'
		
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'More Research Needed')
		THEN 'More Research Needed'
		
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Objector')
		THEN 'ERROR'
		
       else 'Market Review/Preparing to Mail'
    
    END AS [Updated Bucket]

Open in new window

Thanks,
Ranjit

Jerry Miller

You have 8 or 10 select distinct clauses working, that is what is taking so long.

In the places where you are using SELECT DISTINCT simply to determine if a record exists, you can use SELECT TOP 1. Then once it finds a match, it moves on and doesn't continue to sort the table.

For example in lines 6-11:
WHEN z.PROV_TAX_ID IN
(SELECT TOP 1 b.PROV_TIN
FROM PACT.dbo.SQS_Mailed_TINs_010614 b
where not exists (select * from dbo.sqs_objector_TINs t where b.PROV_TIN = t.prov_tin))
THEN