Solved

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

Posted on 2014-03-18
39
282 Views
Last Modified: 2014-04-03
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
0
Comment
Question by:Aparanjith
  • 18
  • 9
  • 8
  • +1
39 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39937546
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
0
 

Author Comment

by:Aparanjith
ID: 39937589
So you mean, I need to replace every place with TOP 1 where I have Distinct right??
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39937714
Only in the places where you are using SELECT DISTINCT simply to determine if a record exists. Like in lines 8,18,28,38,54,60,73,94. There are more below those lines, but you get the idea.

When you are using distinct in the 'z.PROV_TAX_ID IN' clause, it looks through the entire table looking for the distinct rows that match. Selecting TOP 1 only returns one row and if it finds ones stops searching.

You may need to leave it in place on rows like 43-46. It really depends on your data on these.

SELECT DISTINCT a.Bucket
                  FROM PACT.dbo.SQS_NonPar_PR_LS_TINs a
                  WHERE a.PROV_TAX_ID = z.PROV_TAX_ID)
0
 

Author Comment

by:Aparanjith
ID: 39937817
I am not able to figure it out, I changed in some places and runned the query, it took more than the usual, can you please specify in the code were to keep them, where not to.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39938158
Try it this way:

,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 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
                  (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 TOP 1
                        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 TOP 1
                        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 TOP 1 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 TOP 1
                        z.PROV_TAX_ID
                  FROM
                        PACT.dbo.SQS_EDW_Source z
                  WHERE
                        z.PROV_TAX_ID IN
                              (SELECT TOP 1 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 TOP 1
                              z.PROV_TAX_ID
                        FROM
                              PACT.dbo.SQS_EDW_Source z
                        WHERE
                              z.PROV_TAX_ID IN
                                    (SELECT TOP 1 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 TOP 1
                        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 TOP 1
                        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 TOP 1
                        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 TOP 1
                        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 TOP 1
                        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]
0
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 83 total points
ID: 39938173
Actually you should probably remove the  TOP and go with Select 1. I think it will be more efficient. Run it both ways to see.

,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 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
                  (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 1
                        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 1
                        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 1 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 1
                        z.PROV_TAX_ID
                  FROM
                        PACT.dbo.SQS_EDW_Source z
                  WHERE
                        z.PROV_TAX_ID IN
                              (SELECT 1 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 1
                              z.PROV_TAX_ID
                        FROM
                              PACT.dbo.SQS_EDW_Source z
                        WHERE
                              z.PROV_TAX_ID IN
                                    (SELECT 1 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 1
                        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 1
                        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 1
                        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 1
                        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 1
                        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]
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39938700
Looks like a dup to me too. In fact using 'When EXISTS' from the other question would most likely give you a better result than my solution.
0
 

Author Comment

by:Aparanjith
ID: 39939523
No it is not the dup, because, the case statement I am using here and in the other question is slightly different. That is why I have reposted the question. Because in this query there are sub queries within the query, so I am confused where to put the Where exists. Can you please provide the solution with 'WHERE EXISTS'. As I am new to sql server, I am little confused where to put them exactly.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39939601
The query is a little different, so the WHEN EXISTS probably doesn't apply since you are searching to see if the PROV_TAX_ID is in  the sub queries.
0
 

Author Comment

by:Aparanjith
ID: 39939655
Exactly, and when I use Select Top 1 or Select 1, it is taking more time than, usual execution. Can you tell me any other solution, as it is not working??
0
 

Author Comment

by:Aparanjith
ID: 39939748
Can anyone respond to this, as we are trying to get the exact values and also and more logic to the query coming days, i need someone who can show me the appropriate way, as its only on a particular column [updated bucket], where its taking time, because when I ran the query removing this column query, it got executed in 3 minutes. Can anyone tell me the way to fix it, as running the query more than an hour is not an appropriate way right? I have two big tables in this query one is dbo.sqs_EDW_source_SMG which has 6 million records and I have added index on Prov_tax_id for it, and another table has 1million records which is [PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] and I have added index on Prov_tin column. Do you want me to add any other indexes on the columns, which can run query faster. Please help me out.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39939884
What part do you remove that speeds up the query? We should focus there. You can always request attention on a question and hopefully get more people involved.
0
 

Author Comment

by:Aparanjith
ID: 39939945
I mean its a very big code, I have shared only the query which is taking time that is the query which I shared. The query which I shared is for [Update Bucket] column. The rest of the query is for other columns which is running fine. If I add the query which I have shared with you guys which is, [Updated bucket] column, then it is taking longer time.
So all the problem is in the query which I have shared with you.
0
 

Author Comment

by:Aparanjith
ID: 39940071
Do I need to add any indexes or re modify query with something else? Please help me out anyone
0
 

Author Comment

by:Aparanjith
ID: 39941298
Can any one atleast help me in re writing the query into joins, as may be the sub queries taking longer for the query. Can anyone please help me out?
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39941303
Try commenting sections out and see where the problem lies. I would start by commenting this section since you said that one of the tables is 6 million and the other 1 million records. It has to be in the way you are joining the tables. Have you tried to run an Execution Plan to see what the total cost of the query is?

http://technet.microsoft.com/en-us/library/ms178071%28v=sql.105%29.aspx


--**Amendment Mailed**
        WHEN z.PROV_TAX_ID IN
                 (SELECT 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
                  (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 1
                        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 1
                        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)'
0
 

Author Comment

by:Aparanjith
ID: 39941338
My manager is saying to use the joins instead of sub queries and also suggested to write the indexes on columns which can improve the performance. But Did not know where to go. Struck in between to solve the code.  And coming to the code, the case of "Amendment 3rd and 4th wave itself taking 30 minutes to run".
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39941369
I like your manager. Make better use of joins (in the from clause).

You are currently using "correlated subqueries" within the select clause and, as a general rule, this is the least efficient way to use subqueries. Such correlated subqueries are often the cause of poor performance; and there are 18 of them, all held in just a single case expression.

However you have chosen to provide us with just that single case expression, I would want to see the entire query before suggesting any improvements.
0
 

Author Comment

by:Aparanjith
ID: 39941451
Sure PortletPaul,
                        Here is the entire code what we are working on, have a look, and we are basically facing performance issue with [updated bucket] column.
USE PACT
Go

--******Create Back-up of SQS_Bucket_Details_SMG and PACT.dbo.SQS_TINtoSystem**********
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQS_Bucketed_Details_SMG_Backup]') AND type in (N'U'))
DROP TABLE [dbo].[SQS_Bucketed_Details_SMG_Backup_SMG]
GO
Select *
INTO PACT.dbo.SQS_Bucketed_Details_SMG_Backup_SMG
From PACT.dbo.SQS_Bucketed_Details_SMG_on_SMG

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQS_TINtoSystem_Backup]') AND type in (N'U'))
DROP TABLE [dbo].[SQS_TINtoSystem_Backup]
GO
Select * 
INTO PACT.dbo.SQS_TINtoSystem_Backup
from PACT.dbo.SQS_TINtoSystem


--****Create sqs_objector_TINs (Objections and Rejections)****
Drop table pact.dbo.sqs_objector_TINs
select distinct a.TIN as Prov_TIN
Into pact.dbo.sqs_objector_TINs
from pact.dbo.sqs_provider_tracking a
where a.[Objector?] in ('Top Objector','Objector','Rejector')

/*********** Query for SQS_TINtoSyst***********/
DROP TABLE PACT.dbo.SQS_TINtoSystem
select      distinct 
      b.SRC_PROV_ID
      --,case 
      --      when a.SYSTEM_NAME is null 
      --      then 
      --            case 
      --                  when a.CTRCT_GRP_NAME is null 
      --                  then a.PROV_SMG_NAME 
      --                  else a.CTRCT_GRP_NAME 
      --            end 
      --      else a.SYSTEM_NAME 
      --end as SYSTEM_NAME
      ,COALESCE(a.SYSTEM_NAME, a.CTRCT_GRP_NAME, a.PROV_SMG_NAME) AS SYSTEM_NAME
INTO  Pact.Dbo.SQS_TINtoSystem
from 
      PARE.dbo.EDW_PROD_HOSPITAL_MASTER a 
      Inner Join PARE.dbo.EDW_PROD_HOSPITAL_ID_XREF b 
            on a.SMG_ID = b.SMG_ID 
   --   Inner Join Pact.dbo.SQS_EDW_Source q 
			--on b.SRC_PROV_ID = q.PROV_TAX_ID
		where b.SRC_PLATFORM_CD = 'TX' 
      and a.SMG_ID in 
            ( 
            select distinct 
            SMG_ID 
            from PARE.dbo.EDW_PROD_HOSPITAL_ID_XREF 
            where SRC_PLATFORM_CD = 'TX' 
            and SRC_PROV_ID in (select q.PROV_TAX_ID from PACT.dbo.SQS_EDW_Source q) 
            );

/************** Query for SQS_Bucketed_Details_SMG*****************/
DROP TABLE PACT.dbo.SQS_Bucketed_Details_SMG_on_smg
--Create temp table
SELECT z.SYSTEMNAME
	,Z.PROV_TAX_ID
	,z.PROVIDERNAME
	,z.STATECODE
	,z.SRC_PAR_CD
	,z.SEQUEST_AMT
	,Z.LCLM_RSTMT_TREND_CAT_CD
	,Z.SMG_ID
INTO  #SQS_EDW_SOURCE_WithSMG 
FROM PACT.dbo.SQS_EDW_SOURCE_WithSMG z
WHERE YEAR(Z.Incurred_Mth)=2013

-- Create Temp table Q
select distinct 
			x.TIN,
			case when max(x.IND) = 'NYN' 
				then 'Standard' 
            when max(x.IND) = 'YNN' 
				then 'Express' 
            when max(x.IND) = 'NNY' 
				then 'Non_Standard' else 'Mixed' 
           end as INDdesc
Into #Q
FROM
	(SELECT distinct 
				a.tin, 
                MAX(a.express) + MAX(a.StandardInd) + MAX(NonstandardIND) as IND
            from 
				(select r.TIN,
					case when MAX(r.Express) like 'Y%' then 'Y' else 'N' end As Express, 
                    case when MAX(r.Standard) = 'Y' then 'Y' else 'N' end As StandardInd, 
                    case when MAX(r.[Non-Standard]) = 'Y' then 'Y' else 'N' end AS NonstandardIND
                FROM PACT.DBO.SQS_Objectors_01032014 r 
                GROUP BY r.TIN) a
            group by a.TIN) x
        group by x.TIN

--Create Temp table F

Select * 
INTO  #F
FROM(
	 SELECT distinct g.prov_tax_id 
			,g.system_name
			,g.provider
			,case when g.reimburse_mixed = 'Y' then 'Mixed' 
				when g.reimburse_express = 'Y' then 'Express' 
				when g.reimburse_standard = 'Y' then 'Standard' 
				when g.reimburse_NonStandard = 'Y' then 'NonStandard'
			end as IND_Desc
			,g.Time_Period_for_Dispute
			,case when g.Renewal_Date = 'N' and g.Expiration_Date = 'N' 
				then 'Unclear'
			when g.Renewal_Date = 'N' and g.Expiration_Date <> 'N' 
				then 'Termination'
			when g.Renewal_Date <> 'N' and g.Expiration_Date = 'N' 
				then 'Evergreen'
			when g.Renewal_Date <> 'N' and g.Expiration_Date <> 'N' 
				then 'Termination' 
				else 'Unknown' 
			end as 'Renew_Term_Ind'
			,g.Renewal_Date
		FROM 
			(select distinct 
				bb.PROV_TAX_ID1 as prov_tax_id  
				,aa.* 
			from 
				[PACT].[dbo].[Top_600_Hospitals3] aa
				left join pare.dbo.EDW_PROD_HOSPITAL_MASTER bb 
					on --a.CTRCT_GRP_NAME = b.CTRCT_GRP_NAME
					aa.Provider = bb.PROV_SMG_NAME
					--    and (a.SYSTEM_NAME = b.SMG_SYS_NAME or a.SYSTEM_NAME = b.SYSTEM_NAME)
					--and a.ADDR_LINE1 = b.ADDR_LINE1
					and aa.STATE_CD = bb.STATE_CD
					--and a.ZIP_CD = b.ZIP_CD
					and aa.City1 = bb.CITY_NAME                                                                
			where aa.SYSTEM_NAME <> 'SEE ABOVE') g
		where g.system_name <> 'SEE ABOVE') h   
	where h.ind_Desc is not null

---------------------------------Load Dataset into Temp table---------------

SELECT 
	z.SYSTEMNAME
	--,Case when ZXC.[Subsystem Name] <> 'NULL' Then zxc.[SubSystem Name] 
		--else NULL 
		--End AS SubSystemName
		, CASE
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT zxc.TIN
			FROM PACT.dbo.SQS_Provider_Tracking zxc
			WHERE zxc.[SubSystem Name] <> 'NULL'
			)
		THEN
			(SELECT DISTINCT [Subsystem Name]
			FROM PACT.dbo.SQS_Provider_Tracking zxc 
			WHERE z.PROV_TAX_ID = zxc.TIN) 
	End As SubSYSTEMNAME
	,z.PROVIDERNAME
	,z.STATECODE
	,z.PROV_TAX_ID
	,z.SRC_PAR_CD
	,SUM(z.SEQUEST_AMT) Actual_Sequestered_Amt
	
	,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 WITH (NOLOCK )
                 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','SVILLE HOSPITAL HEALTH SYSTEM','INTEGRIS HEALTH','JUPITER MEDICAL CENTER','LEE MEMORIAL ')
            THEN 'Top Objecting Systems' 
            
            WHEN (z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        #SQS_EDW_SOURCE_WithSMG z
                  WHERE 
                        z.PROV_TAX_ID IN 
                              (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
                                                WHERE obj.[Objector?] = 'Top Objector' 
                                         --       AND obj.[TINs Provided By:] <> 'Unknown'--PACT.dbo.SQS_Objectors_01202014b
                              )
                              AND (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                              OR z.SMG_ID is not null)
                             -- AND z.SEQUEST_AMT < 31000
                            -- AND z.SYSTEMNAME <> 'NULL'
                  ))
            THEN 'Top Objecting Systems'
            
            
            --**Other Objecting Hospitals**
            WHEN (z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        #SQS_EDW_SOURCE_WithSMG z
                  WHERE 
                        z.PROV_TAX_ID IN 
                              (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
                                                WHERE obj.[Objector?] = 'Objector' 
                                         --       AND obj.[TINs Provided By:] <> 'Unknown'--PACT.dbo.SQS_Objectors_01202014b
                              )
                              AND (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                              OR z.SMG_ID is not null)
                             -- AND z.SEQUEST_AMT < 31000
                            -- AND z.SYSTEMNAME <> 'NULL'
                  ))
            THEN 'Other Objecting Hospitals'
       
  --      --**Objecting Physicians**
        WHEN 
                (z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              z.PROV_TAX_ID
                        FROM 
                              #SQS_EDW_SOURCE_WithSMG 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?] in ('Objector','Top Objector')
                               --     AND obj.[TINs Provided by:] <> 'Unknown'
                                    )
                        AND (z.LCLM_RSTMT_TREND_CAT_CD NOT IN ('HO','HI') OR z.SMG_ID IS null)
                        ))
            THEN 'Objecting Physicians'   
            
            
            
         --****Rejecting Hospitals****
         WHEN (z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        #SQS_EDW_SOURCE_WithSMG z
                  WHERE 
                        z.PROV_TAX_ID IN 
                              (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
                                                WHERE obj.[Objector?] = 'Rejector' 
                                         --       AND obj.[TINs Provided By:] <> 'Unknown'--PACT.dbo.SQS_Objectors_01202014b
                              )
                              AND (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                              OR z.SMG_ID is not null)         ))
            THEN 'Rejecting Hospitals'
            
            
          --****Rejecting Physciains****
            WHEN 
                (z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              z.PROV_TAX_ID
                        FROM 
                              #SQS_EDW_SOURCE_WithSMG 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?] = 'Rejector'
                               --     AND obj.[TINs Provided by:] <> 'Unknown'
                                    )
                        AND (z.LCLM_RSTMT_TREND_CAT_CD NOT IN ('HO','HI') OR z.SMG_ID IS null)
                        ))
            THEN 'REjecting Physicians'   
            
            
            
--**********ALL OBJECTORS SHOULD HAVE BEEN BUCKETED AT THIS POINT IN THE QUERY**********


--      --**Non-Objecting Hospitals**
            WHEN (z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        #SQS_EDW_SOURCE_WithSMG z
                  WHERE 
                        (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI') OR z.SMG_ID Is not null)
                        --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]


	,COALESCE(q.INDdesc, f.IND_desc) AS INDdesc
    ,f.Time_Period_for_Dispute
    ,f.Renew_Term_Ind
    ,f.Renewal_Date
    ,z.SMG_ID
    ,'' AS OrderedRank
INTO  PACT.DBO.SQS_Bucketed_Details_SMG_on_SMG
From #SQS_EDW_SOURCE_WithSMG z
      
	--left join select * from PACT.dbo.SQS_Provider_Tracking zxc order by zxc.[subsystem name], zxc.tin
	--		ON z.PROV_TAX_ID = zxc.TIN 
			--WHERE zxc.[SubSystem Name] <> 'NULL'
	left join #F f ON f.PROV_TAX_ID = z.PROV_TAX_ID
			AND z.SYSTEMNAME = f.SYSTEM_NAME
			AND z.PROVIDERNAME = f.Provider
	Left join #Q q ON z.PROV_TAX_ID = q.TIN
	GROUP BY z.SYSTEMNAME
		--,Z.[SubsystemName]
		,z.PROVIDERNAME
		,z.STATECODE
		,z.PROV_TAX_ID
		,z.SRC_PAR_CD
		,q.INDdesc
		,f.IND_Desc
		,f.Time_Period_for_Dispute
		,f.Renew_Term_Ind
		,f.Renewal_Date
		,z.SMG_ID
		,z.LCLM_RSTMT_TREND_CAT_CD

/************************** Drop temp tables*********************/
DROP TABLE #SQS_EDW_SOURCE_WithSMG

DROP TABLE #Q

DROP TABLE #F
      /***********************************************************/

Open in new window


This is the entire code we have, we have created some temp tables, and SQS_EDW_SOURCE_WithSMG is the table which has 6 million records and I have created non clustered indexes on Month,Tax_id and SMG_ID on this table. Can you please help me out with the code.?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Aparanjith
ID: 39942613
Need help? Can anyone help me out..

I am attaching the execution plan here, please have a look. And let me know what can i do with it.
Execution-Plan.sqlplan
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 417 total points
ID: 39943974
I may not be able to get back to you immediately I'm afraid due to work and family commitments.

You have provided a query full of "--" comments, I can only assume that when this occurs that sql is to be ignored.

Firstly I would seriously suggest that you erase the word "distinct" from your vocabulary. You use it way too often and not very wisely. See
Some Simple SQL Rules to Live By and look for "DISTINCT is *usually* bad"
A good rule of thumb -- if you need a distinct list of values in a single column or two, DISTINCT is the way to go.  But if your results "don't look right" or you see some duplicate rows and can't figure out why, do NOT just add DISTINCT to your SELECT to "fix it" !!  Step back, look at your joins, and re-write your query properly.  Even worse, I've seen people simply add DISTINCT to all their SELECT's right from the start, to preemptively "avoid duplicates".  Not good.  (Side note: If you are a DISTINCT abuser, try adding meaningful primary keys to your tables).
Why I Hate DISTINCT
The effects of DISTINCT in a SQL query


"Distinct" is costly, use it sparingly.


If you are doing a "group by" NEVER also include "distinct". Look at how table @Q is created, Explain why you feel distinct is need after the very first group by? (tip: it isn't. distinct is entirely redundant with a group by)


Why do you create pact.dbo.sqs_objector_TINs then do nothing with it afterwards? That table isn't reused anywhere I can see.

Consider this bit:
--Create Temp table F

Select * 
INTO  #F
FROM(
	 SELECT distinct g.prov_tax_id 
			,g.system_name
			,g.provider
			,case when g.reimburse_mixed = 'Y' then 'Mixed' 
				when g.reimburse_express = 'Y' then 'Express' 
				when g.reimburse_standard = 'Y' then 'Standard' 
				when g.reimburse_NonStandard = 'Y' then 'NonStandard'
			end as IND_Desc
			,g.Time_Period_for_Dispute
			,case when g.Renewal_Date = 'N' and g.Expiration_Date = 'N' 
				then 'Unclear'
			when g.Renewal_Date = 'N' and g.Expiration_Date <> 'N' 
				then 'Termination'
			when g.Renewal_Date <> 'N' and g.Expiration_Date = 'N' 
				then 'Evergreen'
			when g.Renewal_Date <> 'N' and g.Expiration_Date <> 'N' 
				then 'Termination' 
				else 'Unknown' 
			end as 'Renew_Term_Ind'
			,g.Renewal_Date
		FROM 
			(select distinct 
				bb.PROV_TAX_ID1 as prov_tax_id  
				,aa.* 
			from 
				[PACT].[dbo].[Top_600_Hospitals3] aa
				left join pare.dbo.EDW_PROD_HOSPITAL_MASTER bb 
					on --a.CTRCT_GRP_NAME = b.CTRCT_GRP_NAME
					aa.Provider = bb.PROV_SMG_NAME
					--    and (a.SYSTEM_NAME = b.SMG_SYS_NAME or a.SYSTEM_NAME = b.SYSTEM_NAME)
					--and a.ADDR_LINE1 = b.ADDR_LINE1
					and aa.STATE_CD = bb.STATE_CD
					--and a.ZIP_CD = b.ZIP_CD
					and aa.City1 = bb.CITY_NAME                                                                
			where aa.SYSTEM_NAME <> 'SEE ABOVE') g
		where g.system_name <> 'SEE ABOVE') h   
	where h.ind_Desc is not null

Open in new window

Why have you used an unnecessary inner subquery aa? tip: never do "select distinct *" that's potentially v.bad.

AVOID repeating the same subqueries...

e.g.

Search "PACT.dbo.SQS_Provider_Tracking" (9 hits in 1 file)
  new  3 (9 hits)
	Line 24: from pact.dbo.sqs_provider_tracking a
	Line 152: 			FROM PACT.dbo.SQS_Provider_Tracking zxc
	Line 157: 			FROM PACT.dbo.SQS_Provider_Tracking zxc 
	Line 224:                               (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
	Line 244:                               (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
	Line 265:                                     FROM PACT.dbo.SQS_Provider_Tracking obj --PACT.dbo.SQS_Objectors_01202014b
	Line 283:                               (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
	Line 302:                                     FROM PACT.dbo.SQS_Provider_Tracking obj --PACT.dbo.SQS_Objectors_01202014b
	Line 376: 	--left join select * from PACT.dbo.SQS_Provider_Tracking zxc order by zxc.[subsystem name], zxc.tin

Open in new window



There is much to re-consider.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 417 total points
ID: 39944400
big table scan and insertHi, found a few minutes and saw the above. It looks simple enough but is it worthwhile and is it as efficient as it might be?

You are using a function in the where clause that appears to be robbing you of any index benefits:

WHERE YEAR(Z.Incurred_Mth)=2013

you should avoid using functions like this if it is possible. I can only guess what is in that field but a "sargable" version of that where clause is likely to be something like this:

WHERE Z.Incurred_Mth >= '20130101' and  Z.Incurred_Mth < '20140101'

then assuming there is an index on Incurred_Mth that should be "sargable"
see: http://en.wikipedia.org/wiki/Sargable


Further to this, why create a temp table of  nearly 6 million records? Where is the advantage to this particularly as there are no indexes on tat temp table.
0
 

Author Comment

by:Aparanjith
ID: 39944405
I am using indexes on that table, for incurred month, prov tax I'd and smg id
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39944509
>>"I am using indexes on that table"

Not according to the execution plan, it is doing a table scan - did you look at that image?
Plus, it scans 5.8 million rows and inserts 5.8 million rows, so that where clause (causing the table scan) doesn't seem to achieve much.

Quite frankly this whole query needs so much rework I don't think I can continue with it as I am out this evening and won't have time on the weekend.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39946668
Please don't take this the wrong way, but you really need to hire a competent SQL developer to re-factor that whole query.  To put it bluntly it is a mess and will require somewhere between 6 to 8 hours by someone with experience to resolve.
0
 

Author Comment

by:Aparanjith
ID: 39946863
Yes that is what I am trying to do, the place where I work, they do not have the table structure in a proper way, they just dumped the tables from Access to sql server, and they do not have primary keys nor indexes in them. I recently joined here, and they gave me this query to solve, previously it worked for 3 hours now as I added some indexes on columns it went for 40 minutes. But who wants a version of query which is running for 40 minutes. And as I am a person hired for sql they are expecting me do it, but I am unable to do that as I am new to sql and this is my second sql job.
                                                                                       I do not know anyone in other way I could not afford them to hire and pay, that is the reason I have joined as a member in Expert Exchange. It helped me a lot in times where I need it. But need some real help here?????
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 417 total points
ID: 39947062
We are volunteers, you cannot reasonably expect us to completely rewrite this query for you as an answer to a question. Additionally, you have provided no table details, no sample data, no expected results. There is very little preparation to the question.

I was contemplating making further suggestions BUT you would still end-up with a horrible overall query and I didn't want to appear to have endorsed such a mess. Even worse, making "patches" to this query might even make you believe such patches are good or reasonable practice. It's also hard to make suggestions if you aren't skilled enough to implement them.

One thing to note is the repeated re-calling of #SQS_EDW_SOURCE_WithSMG which appears to be so utterly silly e.g.
            WHEN (z.PROV_TAX_ID IN
                  (SELECT DISTINCT
                        z.PROV_TAX_ID
                  FROM 
                        #SQS_EDW_SOURCE_WithSMG z
                  WHERE 
                        z.PROV_TAX_ID IN 
                              (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
                                                WHERE obj.[Objector?] = 'Top Objector' 
                                         --       AND obj.[TINs Provided By:] <> 'Unknown'--PACT.dbo.SQS_Objectors_01202014b
                              )
                              AND (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                              OR z.SMG_ID is not null)
                             -- AND z.SEQUEST_AMT < 31000
                            -- AND z.SYSTEMNAME <> 'NULL'
                  ))
            THEN 'Top Objecting Systems'

Open in new window

That block contains this logic:
test that  z.PROV_TAX_ID is IN (a huge list of z.PROV_TAX_ID)

You are already inside #SQS_EDW_SOURCE_WithSMG so why on earth are you then going on to test if a field of that table is IN that table?

plus: using IN() within a where clause is in itself a potential performance problem and it certainly is when the table has ~6 million rows.

---

I'll repeat this piece of advice:

Forget you ever heard of "distinct"; it is the enemy in the query
0
 

Author Comment

by:Aparanjith
ID: 39947154
Thanks for the valuable comments...
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 417 total points
ID: 39948031
here's a classic example of how NOT to use distinct, lines 74 through 97 of the query at ID: 39941451
-- Create Temp table Q
select distinct 
			x.TIN,
			case when max(x.IND) = 'NYN' 
				then 'Standard' 
            when max(x.IND) = 'YNN' 
				then 'Express' 
            when max(x.IND) = 'NNY' 
				then 'Non_Standard' else 'Mixed' 
           end as INDdesc
Into #Q
FROM
	(SELECT distinct 
				a.tin, 
                MAX(a.express) + MAX(a.StandardInd) + MAX(NonstandardIND) as IND
            from 
				(select r.TIN,
					case when MAX(r.Express) like 'Y%' then 'Y' else 'N' end As Express, 
                    case when MAX(r.Standard) = 'Y' then 'Y' else 'N' end As StandardInd, 
                    case when MAX(r.[Non-Standard]) = 'Y' then 'Y' else 'N' end AS NonstandardIND
                FROM PACT.DBO.SQS_Objectors_01032014 r 
                GROUP BY r.TIN) a
            group by a.TIN) x
        group by x.TIN

Open in new window

See line 95 and again at line 96 "GROUP BY r.TIN"

What does "GROUP BY" do?

Answer: It reduces the output to one row for each unique value of r.TIN

If you have a unique list of TIN from a group by, what will SELECT DISTINCT do?

Answer: nothing at all useful, it is a complete waste

Action remove both uses of "distinct" in that portion of the query (lines 86 and 75 of the original)
0
 

Author Comment

by:Aparanjith
ID: 39948039
Thanks paul, and I already removed distinct in places where it does not needed now. And mostly figuring out of how to work on my case logic of [updated bucket] column. As there I am facing problem, in execution plan it is taking only 1 minute to get data into all temp tables, but its taking real time in case logic of update bucket. I am concentrating on that part.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 417 total points
ID: 39948148
Yes those case expressions is where the greatest expense is right now, but what you want to do with those case expressions is to take out the subqueries and replace that logic with joined derived tables. This will radically improve performance IMHO

e.g.
SELECT
      ...
    , CASE
      /*
                  WHEN (z.PROV_TAX_ID IN
                        (SELECT DISTINCT 
                              z.PROV_TAX_ID
                        FROM 
                              #SQS_EDW_SOURCE_WithSMG z
                        WHERE 
                              z.PROV_TAX_ID IN 
                                    (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
                                                      WHERE obj.[Objector?] = c 
                                               --       AND obj.[TINs Provided By:] <> 'Unknown'--PACT.dbo.SQS_Objectors_01202014b
                                    )
                                    AND (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                                    OR z.SMG_ID is not null)
                                   -- AND z.SEQUEST_AMT < 31000
                                  -- AND z.SYSTEMNAME <> 'NULL'
                        ))
                  THEN 'Top Objecting Systems'
      */
-- replace that with:
          WHEN obj.[Objector?] = 'Top Objector' 
             AND (z.LCLM_RSTMT_TREND_CAT_CD IN ('HO', 'HI') OR z.SMG_ID IS NOT NULL)
          THEN 'Top Objecting Systems'
--
      END AS [Updated Bucket]

INTO PACT.DBO.SQS_Bucketed_Details_SMG_on_SMG
FROM #SQS_EDW_SOURCE_WithSMG z

/* this is a suggested  join */
      LEFT JOIN (
            SELECT DISTINCT /* distinct might not be needed here, don't know*/
                  TIN
                , [Objector?]
            FROM PACT.dbo.SQS_Provider_Tracking
      ) obj
            ON z.PROV_TAX_ID = obj.TIN

      LEFT JOIN #F f
            ON f.PROV_TAX_ID = z.PROV_TAX_ID
                  AND z.SYSTEMNAME = f.SYSTEM_NAME
                  AND z.PROVIDERNAME = f.Provider
      LEFT JOIN #Q q
            ON z.PROV_TAX_ID = q.TIN

Open in new window

If you look at all those subqueries in that massive case expression there is huge repetition! Here you should be trying to exploit joins to eradicate that repetition.
0
 

Author Comment

by:Aparanjith
ID: 39975245
I've requested that this question be deleted for the following reason:

I did not get the answer. Sorry
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39970877
I did not get the answer. Sorry
As we told you repeatedly this is not how EE works.  We are volunteers here, we will not do your work for you and a question like this requires about 6-8 hours to resolve adequately.

You will find this site a great resource if you follow the EE Guidelines, failing to do that and you will not get the results you expect.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39970925
I think you got plenty of answers and guidance - you weren't too keen on listening to those it seems. Pity. When we work together through these issues you not only get the solution you asked for but learn a great deal more by being involved.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39973938
Ranjit,

The sad part about it is that the only one you are hurting is yourself.  The next time you ask a question here, members may think twice about contributing in view of your lack of appreciations and feedback in this question.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39974930
PortletPaul and Anthony Perkins are both excellent in SQL I have seen many of their solutions on EE. That is why when they started answering I deferred to them. I am a programmer with a lot of SQL knowledge, but these two are way ahead of me. You should listen to their advice.
0
 

Author Comment

by:Aparanjith
ID: 39975025
Guys, not a problem from your side, it was just a reason i have stated to delete my question. I know that people who contribute here have more than intermediate knowledge, as i am just a rookie I did not got the solution for this. But, I know i have got many of the solutions from EE, and mainly portlet paul and Anthony helped me a lot in my other questions. I am totally convinced, not a problem from your side, I admit.
0
 

Author Comment

by:Aparanjith
ID: 39975085
And one more thing, as the steps told by Paul, such as not to use distinct and to use joins instead of sub queries and many more, i have followed each and every step what you have told to me paul, but the time frame which took like 40 mins before came to 20 mins later after i changed the steps which you have told me. It's not that i am not following what you say, I totally respect to your words what you say, and rely completely on them. It is totally my fault, that being a rookie i opted for heavy loaded job. I agree that you are here to just volunteer us, but not to do my work. With respect to all the members of EE specially Portlet Paul and Anthony, I follow all the steps what they post to my questions. Well thank you for your support guys.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 36
Auditing with Temporal Tables 4 18
Convert SP in a format for debugging 7 11
SQL Server 2012 Express to Full 5 16
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now