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
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.