SQL Server Performance

sub-view performance issue

Discussion in 'ALL SQL SERVER QUESTIONS' started by Onkar, Jul 4, 2012.

  1. Onkar New Member

    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
  2. FrankKalis Moderator

    Welcome to the forum!
    I don't think that this idea will give you much of a performance boost, because the optimizer will most likely translate BETWEEN to a >= < combination back again internally.

    Did you have a look at the execution plan already? Based on the information you've provided, it is almost impossible to tell what can be improved and where SQL Server is spending its time. And since the involved tables are considerably large, it might be beneficial if you could post the table structures and indexes and maybe the other view definition.

Share This Page