July 4, 2012 at 5:05 am
All,
I have a view (vw_detail) which is called from Crystal report. This view in turn calls another view with some where and order by clauses. The report takes almost 10 mins to run. As I am new to sql server, I am struggling to tune this particular query:
Query:
ALTER VIEW dbo.vw_EOBDetail_EOD
AS
SELECT 1
FROM dbo.PayorHeader
LEFT OUTER JOIN dbo.PayorDetail
ON dbo.PayorHeader.Pclaim_number = dbo.PayorDetail.Pclaim_number
LEFT OUTER JOIN dbo.tbl_ProcedureCodeCrosswalk
ON dbo.PayorHeader.Administrator = dbo.tbl_ProcedureCodeCrosswalk.TPAID
AND dbo.PayorDetail.PProcedureCode >= dbo.tbl_ProcedureCodeCrosswalk.Low_HCPCS
AND dbo.PayorDetail.PProcedureCode <= dbo.tbl_ProcedureCodeCrosswalk.High_HCPCS
AND LEN(RTRIM(dbo.PayorDetail.PProcedureCode)) > 4
LEFT OUTER JOIN (SELECT dbo.SubPayorDetail.Pclaim_Number
, dbo.SubPayorDetail.ProcedureSequenceNumber,
MAX(CASE WHEN SubPayorDetail.SubProcedure = 1 THEN SubPayorDetail.PercentageCovered ELSE ' ' END) AS PercentageCovered_1,
MAX(CASE WHEN SubPayorDetail.SubProcedure = 2 THEN SubPayorDetail.PercentageCovered ELSE ' ' END) AS PercentageCovered_2,
MAX(CASE WHEN SubPayorDetail.SubProcedure > 2 THEN SubPayorDetail.PercentageCovered ELSE ' ' END) AS PercentageCovered_3,
MAX(CASE WHEN SubPayorDetail.SubProcedure = 1 THEN SubPayorDetail.BenefitPayable ELSE 0 END) AS Paid_Level_1,
MAX(CASE WHEN SubPayorDetail.SubProcedure = 2 THEN SubPayorDetail.BenefitPayable ELSE 0 END) AS Paid_Level_2,
MAX(CASE WHEN SubPayorDetail.SubProcedure > 2 THEN SubPayorDetail.BenefitPayable ELSE 0 END) AS Paid_Level_3
FROM dbo.PayorHeader AS PayorHeader_2
INNER JOIN dbo.SubPayorDetail
ON PayorHeader_2.Pclaim_number = dbo.SubPayorDetail.Pclaim_Number
GROUP BY dbo.SubPayorDetail.Pclaim_Number, dbo.SubPayorDetail.ProcedureSequenceNumber) AS SUB
ON dbo.PayorDetail.Pclaim_number = SUB.Pclaim_Number
AND dbo.PayorDetail.Procedure_sequence_number = SUB.ProcedureSequenceNumber
INNER JOIN (SELECT Pclaim_number
, MAX(CASE WHEN LEFT(BankAccountControl, 2) = 'NC' THEN 1 ELSE 0 END) AS NC_Flag
FROM dbo.PayorHeader AS PayorHeader_1
GROUP BY Pclaim_number) AS NCF
ON dbo.PayorHeader.Pclaim_number = NCF.Pclaim_number
LEFT OUTER JOIN dbo.tbl_Cycle
ON dbo.PayorHeader.EOBPreparedDate = dbo.tbl_Cycle.Run_Date
LEFT OUTER JOIN dbo.tbl_Employer
ON dbo.PayorHeader.Administrator = dbo.tbl_Employer.TPAID
AND dbo.PayorHeader.Pgroup_number = dbo.tbl_Employer.GroupID
LEFT OUTER JOIN dbo.tbl_tmp_ReportParameters
ON dbo.PayorHeader.ECHOSOFT_BatchID = dbo.tbl_tmp_ReportParameters.Parm_Value
AND dbo.tbl_tmp_ReportParameters.Report_Name = 'Import_Acknowledgement'
LEFT OUTER JOIN dbo.tbl_HRA_Accumulators
ON dbo.PayorHeader.Administrator = dbo.tbl_HRA_Accumulators.TPAID
AND dbo.PayorHeader.Pgroup_number = dbo.tbl_HRA_Accumulators.Groupid
AND dbo.PayorHeader.Pcertificate_number = dbo.tbl_HRA_Accumulators.InsuredID
We think we have proper indexes in place and below is the count of the tables:
SubPayorDetail--68647272
PayorHeader--33685960
PayorDetail--67459244
tbl_ProcedureCodeCrosswalk--0
tbl_Cycle--0
tbl_Employer--0
tbl_tmp_ReportParameters--1
My first ideas is to remove AND dbo.PayorDetail.PProcedureCode >= dbo.tbl_ProcedureCodeCrosswalk.Low_HCPCS
AND dbo.PayorDetail.PProcedureCode <= dbo.tbl_ProcedureCodeCrosswalk.High_HCPCS
with between operator.
Any thoughts?
Onkar
July 10, 2012 at 1:55 am
Duplicate post (please do not do that). Direct all replies here: http://www.sqlservercentral.com/Forums/Topic1325182-360-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply