Aparanjith
asked on
I have a query which is taking more than 2 hours to run, can anyone solve this?
/* This is the particular code, which is basically a data of column name [Updated bucket], it has lot of sun queries in it, and it is taking more than 3 hours to run because the particular table PACT.dbo.SQS_EDW_Source z has 6 million records in it. Little confused how to get the performance improve. Please help me out.
Thanks,
Ranjit
,
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
)
THEN
(SELECT DISTINCT b.Mailing
FROM PACT.dbo.SQS_Mailed_TINs_010614 b
WHERE z.PROV_TAX_ID = b.PROV_TIN
)
--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')
THEN 'Top Objecting Systems'
--**Other Objecting Hospitals**
WHEN 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.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.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'
-- --**Under Review with CoC**
--WHEN z.PROV_TAX_ID IN
-- (SELECT
-- a.PROV_TAX_ID
-- FROM
-- PACT.dbo.[SQS_CoC_Potential_Mail_List] a
-- -- LEFT JOIN PACT.dbo.SQS_Bucketed_Details_SMG b
-- -- on a.PROV_TAX_ID = b.PROV_TAX_ID
-- -- where b.actual_sequestered_amt IS not null
-- --GROUP BY
-- -- a.PROV_TAX_ID
-- --having SUM(b.Actual_Sequestered_Amt) <> 0
-- )
-- THEN 'Under Review with CoC'
-- ----**Outstanding Contracts for Review**
--when SUM(z.sequest_amt)<1000 then 'Less Than $1k Potential Exposure'
--when sum(z.sequest_amt)<2000 then '1k-2k Potential Exposure'
--when sum(z.sequest_amt)<3000 then '2k-3k Potential Exposure'
--when sum(z.sequest_amt)<4000 then '3k-4k Potential Exposure'
--when sum(z.sequest_amt)<5000 then '4k-5k Potential Exposure'
--when sum(z.sequest_amt)<6000 then '5k-6k Potential Exposure'
--when sum(z.sequest_amt)<7000 then '6k-7k Potential Exposure'
--when sum(z.sequest_amt)<8000 then '7k-8k Potential Exposure'
--when sum(z.sequest_amt)<9000 then '8k-9k Potential Exposure'
--when sum(z.sequest_amt)<10000 then '9k-10k Potential Exposure'
--when sum(z.sequest_amt)<20000 then '10k-20k Potential Exposure'
--when sum(z.sequest_amt)<30000 then '20k-30k Potential Exposure'
--when sum(z.sequest_amt)<40000 then '30k-40k Potential Exposure'
--when sum(z.sequest_amt)<50000 then '40k-50k Potential Exposure'
--else 'Greater than 50k Potential Exposure'
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)')
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)')
THEN 'Amendment Mailed (4th Wave)'
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
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 OBJECTOR NOT PICKED UP EARLIER'
else 'Market Review/Preparing to Mail'
END AS [Updated Bucket]Thanks,
Ranjit
lcohan
In SSMS under the Query Menu option there is a Estimated Execution Plan and can you select that option having your query open? This should show you at least if there is/are some missing indexes that will help improve the execution time.
Also I would convert ALL the IN statements with DISTINCT into EXISTS statements without the Distinct.
Hard to say without seeing the makeup of the table, index etc
I'd say if the fields you are using for selection in the large table are not indexed then add indexes for them. From what I can see you need to look at the fields such as
PROV_TAX_ID
LCLM_RSTMT_TREND_CAT_CD
etc
Wayne
I'd say if the fields you are using for selection in the large table are not indexed then add indexes for them. From what I can see you need to look at the fields such as
PROV_TAX_ID
LCLM_RSTMT_TREND_CAT_CD
etc
Wayne
ASKER
I did but as the column, can contain null values, its not showing up for indexes, it is all about the table PACT.dbo.SQS_EDW_Source z which has 6 million records in it. And it has been used for 3 or 4 times in the query. I do not have idea, how to do it, can you help me out
You can add indexes for fields that allow Null, you just can't set unique keys.
What indexes are on that big table?
Wayne
What indexes are on that big table?
Wayne
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Check to see if there is an index on
PACT.dbo.SQS_EDW_Source
z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI') AND z.SYSTEMNAME columns.
Also in the two other places where this table is used I would put the SELECT from the other smaller table first then JOIN with PACT.dbo.SQS_EDW_Source on the z.PROV_TAX_ID column.
Also make sure an index exists on z.PROV_TAX_ID column.
PACT.dbo.SQS_EDW_Source
z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI') AND z.SYSTEMNAME columns.
Also in the two other places where this table is used I would put the SELECT from the other smaller table first then JOIN with PACT.dbo.SQS_EDW_Source on the z.PROV_TAX_ID column.
Also make sure an index exists on z.PROV_TAX_ID column.
ASKER
Thanks for the query
ASKER
Hey guys I am having problem only with this part of the code, where we have all cases and when tried using WHEN EXISTS it only selected few cases and ignored rest of them. Can someone help me in rewriting this part of code in a different way, which can improve the performance and also selects all the cases to the [Updated bucket] column, Need help....
, CASE
WHEN z.SRC_PAR_CD IN ('E','O','S','W')
THEN 'Nonpar Waiver'
-- --Is Puerto Rico of Lifesynch
WHEN z.PROV_TAX_ID IN
(SELECT DISTINCT a.PROV_TAX_ID
FROM .dbo.SQS_NonPar_PR_LS_TINs a
WHERE a.Bucket <> 'Nonpar'
)
THEN
(SELECT DISTINCT a.Bucket
FROM .dbo.SQS_NonPar_PR_LS_TINs a
WHERE a.PROV_TAX_ID = z.PROV_TAX_ID)
--**Amendment Mailed**
WHEN z.PROV_TAX_ID IN
(SELECT DISTINCT b.PROV_TIN
FROM .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))
and z.Hosp_Ind = 'P'
THEN
(SELECT DISTINCT b.Mailing
FROM .dbo.SQS_Mailed_TINs_010614 b
WHERE z.PROV_TAX_ID = b.PROV_TIN
)
-- --**Amendment Mailed Wave 3-5**
WHEN z.PROV_TAX_ID In
(SELECT DISTINCT
qz.PROV_TIN
FROM
[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))
and z.Hosp_Ind = 'P'
THEN 'Amendment Mailed (3rd Wave)'
WHEN z.PROV_TAX_ID IN
(SELECT DISTINCT
qz.PROV_TIN
FROM
[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))
and z.Hosp_Ind = 'P'
THEN 'Amendment Mailed (4th Wave)'
WHEN z.PROV_TAX_ID IN
(SELECT DISTINCT
qz.PROV_TIN
FROM
[SQS_Mailed_TINs] qz
where qz.Mailing = 'Amendment Mailed (5th Wave)'
and not exists (select * from dbo.sqs_objector_TINs t where qz.PROV_TIN = t.prov_tin))
and z.Hosp_Ind = 'P'
THEN 'Amendment Mailed (5th Wave)'
-- --**Top Objecting Systems**
WHEN z.SYSTEMNAME IN
('ADVENTIST HEALTH SYSTEM','ASCENSION HEALTH ALLIANCE','AULTMAN HEALTH FOUNDATION')
THEN 'Top Objecting Systems'
WHEN z.PROV_TAX_ID IN
(SELECT DISTINCT
h.PROV_TAX_ID
FROM
#HIHO_Records h
INNER JOIN .dbo.SQS_Provider_Tracking obj
ON h.PROV_TAX_ID = obj.TIN
AND obj.[Objector?] = 'Top Objector'
WHERE z.PROV_TAX_ID = h.PROV_TAX_ID
OR h.SMG_ID IS NOT NULL
)and z.Hosp_Ind = 'H'
THEN 'Top Objecting Systems'
-- --**Other Objecting Hospitals**
WHEN (z.PROV_TAX_ID IN
(SELECT DISTINCT
h.PROV_TAX_ID
FROM
#HIHO_Records h
INNER JOIN .dbo.SQS_Provider_Tracking obj
ON h.PROV_TAX_ID = obj.TIN
AND obj.[Objector?] = 'Objector'
WHERE z.PROV_TAX_ID = h.PROV_TAX_ID
OR h.SMG_ID IS NOT NULL
)and z.Hosp_Ind = 'H')
THEN 'Other Objecting Hospitals'
-- --**Objecting Physicians**
WHEN (z.PROV_TAX_ID IN
(SELECT DISTINCT
obj.TIN
FROM .dbo.SQS_Provider_Tracking obj
WHERE obj.[Objector?] in ('Objector','Top Objector')
and z.PROV_TAX_ID = obj.TIN
)
and z.Hosp_Ind = 'P')
THEN 'Objecting Physicians'
--****Rejecting Hospitals****
WHEN (z.PROV_TAX_ID IN
(SELECT DISTINCT
h.PROV_TAX_ID
FROM
#HIHO_Records h
INNER JOIN .dbo.SQS_Provider_Tracking obj
ON h.PROV_TAX_ID = obj.TIN
AND obj.[Objector?] = 'Rejector'
WHERE z.PROV_TAX_ID = h.PROV_TAX_ID
OR h.SMG_ID IS NOT NULL
)and z.Hosp_Ind = 'H')
THEN 'Rejecting Hospitals'
--****Rejecting Physciains****
WHEN
(z.PROV_TAX_ID IN
(SELECT DISTINCT
obj.TIN
FROM .dbo.SQS_Provider_Tracking obj
WHERE z.PROV_TAX_ID = obj.TIN
AND obj.[Objector?] = 'Rejector')
and z.Hosp_Ind = 'P')
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
h.PROV_TAX_ID
FROM
#HIHO_Records h
WHERE
(z.PROV_TAX_ID = h.PROV_TAX_ID)
OR h.SMG_ID IS NOT NULL)
and z.Hosp_Ind = 'H'
THEN 'Non-Objecting Hospitals'
-- **Outstanding Contracts for Review**
WHEN z.PROV_TAX_ID IN
(SELECT DISTINCT
qz.PROV_TIN
FROM
[SQS_Mailed_TINs] qz
where qz.Mailing = 'Non-Objecting Bilateral Physicians'
AND z.PROV_TAX_ID = qz.PROV_TIN)
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'
AND z.prov_tax_id = p.prov_tax_id)
THEN 'Non-Objecting Bilateral Physicians'
WHEN z.PROV_TAX_ID IN
(SELECT DISTINCT
qz.PROV_TIN
FROM
[SQS_Mailed_TINs] qz
where qz.Mailing = 'More Research Needed'
AND qz.PROV_TIN = z.PROV_TAX_ID)
THEN 'More Research Needed'
WHEN z.PROV_TAX_ID IN (SELECT DISTINCT qz.PROV_TIN FROM [SQS_Mailed_TINs] qz where qz.Mailing = 'Objector' AND qz.PROV_TIN = z.PROV_TAX_ID)
THEN 'ERROR'
else 'Market Review/Preparing to Mail'
END AS [Updated Bucket]