July 4, 2012 at 11:47 pm
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 5, 2012 at 2:11 am
Does the view really start with SELECT 1 FROM dbo.PayorHeader or is this a simplification?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 5, 2012 at 2:17 am
Its just a simplification. In reality this view selects many columns.
-Onkar
July 5, 2012 at 2:20 am
onkarnath (7/5/2012)
Its just a simplification. In reality this view selects many columns.-Onkar
Can you please post the actual plan as an attached .sqlplan file? Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 5, 2012 at 2:20 am
Hi.
If you would be so kind as to read the second link in my signature on posting code, we would required DDL of the tables views indexes and sample data, then if you could read the link on posting performance problems so we can take a look at your execution plan it would help us out in diagnosing your problem a little better.
July 8, 2012 at 4:30 am
Nesting views, calling a view from within a view, is generally a bad practice. It leads to poor performance since, in most cases, not all the columns or tables referenced by the view are needed in the query. In general, you should build each query to retrieve the data needed. Even if this means replicating certain joins in multiple locations. You'll get much better performance overall if you do that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply