Improving the query performance

Please suggest some better ways to rewrite the query to improve the query performance, especially the date report parameter parts. Thank You.

ALTER PROCEDURE [dbo].[SponsorInvoiceReport]
		@ContractBU								NVARCHAR(MAX),
		@ContractSBU							NVARCHAR(MAX),
		@Sponsor								NVARCHAR(MAX),
		@QuotedBy								NVARCHAR(MAX),
		@DecisionDateFrom						DATETIME,
		@DecisionDateTo							DATETIME,
		@ActivityStartDateFrom					DATETIME,
		@ActivityStartDateTo					DATETIME,
		@ActivityEndDateFrom					DATETIME,
		@ActivityEndDateTo						DATETIME,
		@ActivitySBU							NVARCHAR(MAX),
		@ActivityIntercoSBU						NVARCHAR(MAX),
		@ContractCode							NVARCHAR(MAX),
		@GroupByCPCode							BIT
		
AS
BEGIN
     
     
SET NOCOUNT ON

DECLARE @NewDecisionDateTo DATETIME
SET @NewDecisionDateTo = DATEADD(ss,-1,DATEADD(dd,1,CAST(CAST(@DecisionDateTo AS DATE)AS DATETIME)))

DECLARE @NewActivityStartDateTo DATETIME
SET @NewActivityStartDateTo = DATEADD(ss,-1,DATEADD(dd,1,CAST(CAST(@ActivityStartDateTo AS DATE)AS DATETIME)))

DECLARE @NewActivityEndDateTo DATETIME
SET @NewActivityEndDateTo = DATEADD(ss,-1,DATEADD(dd,1,CAST(CAST(@ActivityEndDateTo AS DATE)AS DATETIME)))


DECLARE @Delimiter AS CHAR(1) =','
DECLARE @XML AS XML
	
	 ---ContractBU
	 SET @XML = CAST(('<code>'+REPLACE(@ContractBU,@Delimiter ,'</code><code>')+'</code>') AS XML)     
     CREATE TABLE  #ContractBU (ContractBUCode NVARCHAR(25))      
     INSERT INTO #ContractBU
     SELECT N.value('.', 'NVARCHAR(25)') AS ContractBUCode FROM @XML.nodes('code') AS T(N)
     
     ---ContractSBU
	 SET @XML = CAST(('<code>'+REPLACE(@ContractSBU,@Delimiter ,'</code><code>')+'</code>') AS XML)     
     CREATE TABLE  #ContractSBU (ContractSubBUCode NVARCHAR(25))      
     INSERT INTO #ContractSBU
     SELECT N.value('.', 'NVARCHAR(25)') AS ContractSubBUCode FROM @XML.nodes('code') AS T(N)
     
     ---Sponsor
	 SET @XML = CAST(('<code>'+REPLACE(@Sponsor,@Delimiter ,'</code><code>')+'</code>') AS XML)     
     CREATE TABLE  #Sponsor (SponsorCode NVARCHAR(25))      
     INSERT INTO #Sponsor
     SELECT N.value('.', 'NVARCHAR(25)') AS SponsorCode FROM @XML.nodes('code') AS T(N)
     
     ---QuotedBY
     SET @XML = CAST(('<code>'+REPLACE(@QuotedBy,@Delimiter ,'</code><code>')+'</code>') AS XML)     
     CREATE TABLE  #QuotedBy (QuotedBy NVARCHAR(25))      
     INSERT INTO #QuotedBy
     SELECT N.value('.', 'NVARCHAR(25)') AS QuotedBy FROM @XML.nodes('code') AS T(N)
     
     select QuotedBy 
     into #temp1 from #QuotedBy where QuotedBy NOT like 'C-%' 
    
     select QuotedBy 
     into #temp2 from #QuotedBy where QuotedBy like 'C-%' 
     

     
     --ActivitySBU
     SET @XML = CAST(('<code>'+REPLACE(@ActivitySBU,@Delimiter ,'</code><code>')+'</code>') AS XML)     
     CREATE TABLE  #ActivitySBU (ActivitySBU NVARCHAR(25))      
     INSERT INTO #ActivitySBU
     SELECT N.value('.', 'NVARCHAR(25)') AS ActivitySBU FROM @XML.nodes('code') AS T(N)
     
     --ActivityIntercoSBU
     SET @XML = CAST(('<code>'+REPLACE(@ActivityIntercoSBU,@Delimiter ,'</code><code>')+'</code>') AS XML)     
     CREATE TABLE  #ActivityIntercoSBU (ActivityIntercoSBU NVARCHAR(25))      
     INSERT INTO #ActivityIntercoSBU
     SELECT N.value('.', 'NVARCHAR(25)') AS ActivityIntercoSBU FROM @XML.nodes('code') AS T(N)
     
     ---ContractCode
	 SET @XML = CAST(('<code>'+REPLACE(@ContractCode,@Delimiter ,'</code><code>')+'</code>') AS XML)     
     CREATE TABLE  #Contract (ContractCode NVARCHAR(25))      
     INSERT INTO #Contract
     SELECT N.value('.', 'NVARCHAR(25)') AS ContractCode FROM @XML.nodes('code') AS T(N)
     




--- GROUP BY Activity     
IF (@GroupByCPCode = 0)
BEGIN


SELECT 	A.activityCode AS ActivityCode,
	AU.analyticalUnitName AS ActivitySBU,
	SU.subUnitName AS ActivitySubUnit,
	FCPS.financeContractPaymentScheduleCode AS CPCode,
	FCPS.financeContractPaymentScheduleName AS CPTitle,
	CAU.AnalyticalUnitName AS ContractOwnerSBU,
	CL.clientName AS Sponsor,
	FC.sponsorReference AS SponsorReference,
	FC.comment AS ContractComments,
	FCSAD.activityStartDate AS StartDate,
	FCSAD.activityEndDate AS EndDate
INTO #MainTemp
FROM Local_FinanceContracts AS FC
INNER JOIN Local_FinanceContractsPaymentsSchedules AS FCPS ON FCPS.financeContractIncId = FC.financeContractIncId
	AND FCPS.financeContractSqlId = FC.financeContractSqlId
	AND FCPS.isDeleted = 0
INNER JOIN Local_FinanceContractsPaymentsSchedulesActivities AS FCPSA ON FCPSA.financeContractPaymentScheduleIncId = FCPS.financeContractPaymentScheduleIncId
	AND FCPSA.financeContractPaymentScheduleSqlId = FCPS.financeContractPaymentScheduleSqlId
	AND FCPSA.isDeleted = 0
INNER JOIN Activities AS A ON A.activityIncId = FCPSA.activityIncId
	AND A.activitySqlId = FCPSA.activitySqlId
	AND A.isDeleted = 0
INNER JOIN Local_FinanceContractStructureActivitiesDetails AS FCSAD ON FCSAD.activityIncId = A.activityIncId
	AND FCSAD.activitySqlId = A.activitySqlId
	AND FCSAD.isDeleted = 0
INNER JOIN AnalyticalUnits CAU ON FC.AnalyticalUnitIncId = CAU.analyticalUnitINcId 
	AND FC.AnalyticalUnitsqlId = CAU.AnalyticalUNitSqlId 
	AND CAU.Isdeleted = 0
INNER JOIN BusinessUnits CBU ON CAU.businessUnitIncId = CBU.businessUnitIncId 
	AND CAU.businessUnitSqlId = CBU.businessUnitSqlId
	AND CBU.Isdeleted = 0
LEFT JOIN Local_FinanceContractStructureActivitiesDetailsAdditionalCosts AS FCAC ON FCAC.financeContractStructureActivityDetailIncId = FCSAD.financeContractStructureActivityDetailIncId
	AND FCAC.financeContractStructureActivityDetailSqlId = FCSAD.financeContractStructureActivityDetailSqlId
	AND FCAC.isDeleted = 0
LEFT JOIN AnalyticalUnits AS AU ON A.analyticalUnitWhoDoIncId = AU.analyticalUnitIncId 
	AND A.analyticalUnitWhoDoSqlId = AU.analyticalUnitSqlId 
	AND AU.isDeleted = 0 
LEFT JOIN AnalyticalUnits AS SADAU ON FCSAD.intercoAnalyticalUnitIncId = SADAU.analyticalUnitIncId 
	AND FCSAD.intercoAnalyticalUnitSqlId = SADAU.analyticalUnitSqlId 
	AND SADAU.isDeleted = 0 
LEFT JOIN SubUnits SU ON SU.subUnitIncId = A.subUnitWhoDoIncId
	AND SU.subUnitSqlId = A.subUnitWhoDoSqlId
	AND SU.isDeleted = 0x0
LEFT JOIN Clients AS CL ON FC.sponsorIncId = CL.clientIncId 
	AND FC.sponsorSqlId = CL.clientSqlId 
	AND CL.isDeleted = 0 
LEFT JOIN Operators FCOP ON FCOP.operatorIncId = FC.financeQuotationCreatedByIncId
	AND FCOP.operatorSqlId = FC.financeQuotationCreatedBySqlId
	AND FCOP.isDeleted = 0 
LEFT JOIN Contacts FCCT ON FCCT.contactIncId = FC.financeQuotationCreatedByContactIncId
	AND FCCT.contactSqlId = FC.financeQuotationCreatedByContactSqlId
	AND FCCT.isDeleted = 0 
INNER JOIN #ContractBU ON CBU.businessUnitCode = #ContractBU.ContractBUCode
INNER JOIN #ContractSBU ON CAU.analyticalUnitCode = #ContractSBU.ContractSubBUCode
INNER JOIN #Sponsor ON CL.clientCode = #Sponsor.SponsorCode
INNER JOIN #ActivitySBU ON AU.analyticalUnitCode = #ActivitySBU.ActivitySBU
INNER JOIN #ActivityIntercoSBU ON SADAU.analyticalUnitCode = #ActivityIntercoSBU.ActivityIntercoSBU
INNER JOIN #Contract ON FC.financeContractCode = #Contract.ContractCode
left JOIN #temp1 ON FCOP.operatorCode = #temp1.QuotedBy
left JOIN #temp2 ON FCCT.contactCode = #temp2.QuotedBy

WHERE (FC.decisionDate between @DecisionDateFrom and @NewDecisionDateTo)
AND (FCSAD.activityStartDate between @ActivityStartDateFrom and @NewActivityStartDateTo)
AND (FCSAD.activityEndDate between @ActivityEndDateFrom and @NewActivityEndDateTo)

----select * from #MainTemp

SELECT
	A.activitycode,
	SUM(ISNULL(FCSAD.activityOwnerRevenue,0)) AS RevenueAllocated,
	SUM(ISNULL(FCSAD.activityOwnerRevenue,0)-(ISNULL(FCSAD.absoluteDiscount,0)-(ISNULL(FCSAD.percentageDiscount,0)*(ISNULL(FCSAD.activityOwnerRevenue,0)/100)))) AS RevenueAllocatedWithDisc,
	CASE WHEN CONVERT(BIT, FCAC.isInvoiceableToClient) = 1 THEN SUM(ISNULL(FCAC.maximumPossibleValue,0)) END AS CPMaxAddnlCostsIC,
	CASE WHEN CONVERT(BIT, FCAC.isInvoiceableToClient) = 1 THEN SUM(ISNULL(FCAC.convertedAdditionalCostValuePaid,0)) END AS AddnlCostsPaidIC,
	SUM(ISNULL(FCAC.convertedAdditionalCostValuePaid,0)) AS AddnlCostsICNotDefined,
	CASE WHEN CONVERT(BIT, FCAC.isInvoiceableToClient) = 0 THEN SUM(ISNULL(FCAC.convertedAdditionalCostValuePaid,0)) END AS AddnlCostsNotIC
INTO #ADDCTemp
FROM Local_FinanceContracts AS FC
INNER JOIN Local_FinanceContractsPaymentsSchedules AS FCPS ON FCPS.financeContractIncId = FC.financeContractIncId
	AND FCPS.financeContractSqlId = FC.financeContractSqlId
	AND FCPS.isDeleted = 0
INNER JOIN Local_FinanceContractsPaymentsSchedulesActivities AS FCPSA ON FCPSA.financeContractPaymentScheduleIncId = FCPS.financeContractPaymentScheduleIncId
	AND FCPSA.financeContractPaymentScheduleSqlId = FCPS.financeContractPaymentScheduleSqlId
	AND FCPSA.isDeleted = 0
INNER JOIN Activities AS A ON A.activityIncId = FCPSA.activityIncId
	AND A.activitySqlId = FCPSA.activitySqlId
	AND A.isDeleted = 0
INNER JOIN Local_FinanceContractStructureActivitiesDetails AS FCSAD ON FCSAD.activityIncId = A.activityIncId
	AND FCSAD.activitySqlId = A.activitySqlId
	AND FCSAD.isDeleted = 0
INNER JOIN AnalyticalUnits CAU ON FC.AnalyticalUnitIncId = CAU.analyticalUnitINcId 
	AND FC.AnalyticalUnitsqlId = CAU.AnalyticalUNitSqlId 
	AND CAU.Isdeleted = 0
INNER JOIN BusinessUnits CBU ON CAU.businessUnitIncId = CBU.businessUnitIncId 
	AND CAU.businessUnitSqlId = CBU.businessUnitSqlId
	AND CBU.Isdeleted = 0
LEFT JOIN Local_FinanceContractStructureActivitiesDetailsAdditionalCosts AS FCAC ON FCAC.financeContractStructureActivityDetailIncId = FCSAD.financeContractStructureActivityDetailIncId
	AND FCAC.financeContractStructureActivityDetailSqlId = FCSAD.financeContractStructureActivityDetailSqlId
	AND FCAC.isDeleted = 0
LEFT JOIN AnalyticalUnits AS AU ON A.analyticalUnitWhoDoIncId = AU.analyticalUnitIncId 
	AND A.analyticalUnitWhoDoSqlId = AU.analyticalUnitSqlId 
	AND AU.isDeleted = 0 
LEFT JOIN AnalyticalUnits AS SADAU ON FCSAD.intercoAnalyticalUnitIncId = SADAU.analyticalUnitIncId 
	AND FCSAD.intercoAnalyticalUnitSqlId = SADAU.analyticalUnitSqlId 
	AND SADAU.isDeleted = 0 
LEFT JOIN SubUnits SU ON SU.subUnitIncId = A.subUnitWhoDoIncId
	AND SU.subUnitSqlId = A.subUnitWhoDoSqlId
	AND SU.isDeleted = 0x0
LEFT JOIN Clients AS CL ON FC.sponsorIncId = CL.clientIncId 
	AND FC.sponsorSqlId = CL.clientSqlId 
	AND CL.isDeleted = 0 
LEFT JOIN Operators FCOP ON FCOP.operatorIncId = FC.financeQuotationCreatedByIncId
	AND FCOP.operatorSqlId = FC.financeQuotationCreatedBySqlId
	AND FCOP.isDeleted = 0 
LEFT JOIN Contacts FCCT ON FCCT.contactIncId = FC.financeQuotationCreatedByContactIncId
	AND FCCT.contactSqlId = FC.financeQuotationCreatedByContactSqlId
	AND FCCT.isDeleted = 0 
INNER JOIN #ContractBU ON CBU.businessUnitCode = #ContractBU.ContractBUCode
INNER JOIN #ContractSBU ON CAU.analyticalUnitCode = #ContractSBU.ContractSubBUCode
INNER JOIN #Sponsor ON CL.clientCode = #Sponsor.SponsorCode
INNER JOIN #ActivitySBU ON AU.analyticalUnitCode = #ActivitySBU.ActivitySBU
INNER JOIN #ActivityIntercoSBU ON SADAU.analyticalUnitCode = #ActivityIntercoSBU.ActivityIntercoSBU
INNER JOIN #Contract ON FC.financeContractCode = #Contract.ContractCode
left JOIN #temp1 ON FCOP.operatorCode = #temp1.QuotedBy
left JOIN #temp2 ON FCCT.contactCode = #temp2.QuotedBy

WHERE (FC.decisionDate between @DecisionDateFrom and @NewDecisionDateTo)
AND (FCSAD.activityStartDate between @ActivityStartDateFrom and @NewActivityStartDateTo)
AND (FCSAD.activityEndDate between @ActivityEndDateFrom and @NewActivityEndDateTo)
GROUP BY A.activitycode,FCAC.isInvoiceableToClient
ORDER BY A.activitycode

----select * from #ADDCTemp

	------Final SELECT ststement
	SELECT DISTINCT
	 T1.ActivityCode,
	 T1.ActivitySBU,
	 T1.ActivitySubUnit,
	 T1.CPCode,
	 T1.CPTitle,
	 T1.ContractOwnerSBU,
	 T1.Sponsor,
	 T1.SponsorReference,
	 CAST(T1.ContractComments AS NVARCHAR(MAX)) AS ContractComments,
	 T1.StartDate,
	 T1.EndDate,
	 '' AS StatusCheck,
	 T2.RevenueAllocated,
	 T2.RevenueAllocatedWithDisc,
	 '' AS TotalInvoiceValue,
	 '' AS TotalInvoiceValueSentInv,
	 T2.CPMaxAddnlCostsIC,
	 T2.AddnlCostsPaidIC,
	 '' AS RevenueAddnlCostsIC,
	 '' AS RevenueAddnlCostsSentIC,
	 T2.AddnlCostsICNotDefined,
	 T2.AddnlCostsNotIC
	FROM #MainTemp T1
	left JOIN #ADDCTemp T2
	ON T1.activitycode = T2.activityCode
	ORDER BY T1.activitycode


	DROP TABLE #MainTemp
	DROP TABLE #ADDCTemp
	
END

END

Is there any better way for this part of the query ie. the WHERE clause having the Date query parameter

WHERE (FC.decisionDate between @DecisionDateFrom and @NewDecisionDateTo)
AND (FCSAD.activityStartDate between @ActivityStartDateFrom and @NewActivityStartDateTo)
AND (FCSAD.activityEndDate between @ActivityEndDateFrom and @NewActivityEndDateTo)

Should be fine, provided that you have indexes covering the appropriate columns

That said, I usually don't like BETWEEN for this because of the ambiguity of the "end point", especially when either the Column of the @Parameters, or both, are DateTIME datatype and may contain a time element.

My preference is for:

    FC.decisionDate >= @DecisionDateFrom 
and FC.decisionDate <  @NewDecisionDateTo

where @NewDecisionDateTo is set to one-day-after the endpoint.

e.g.

    FC.decisionDate >= '20161001'
and FC.decisionDate <  '20161101'

rather than

    FC.decisionDate BETWEEN '20161001' AND '20161031'

which will exclude any value of FC.decisionDate dated 31-Oct-2016 and which also contains a time element. Also beware that DATETIME is accurate to only multiple-milliseconds, so

SELECT CONVERT(datetime, '20161031 23:59:59.999')

actually gives 2016-11-01 00:00:00.000 :frowning: so an endpoint that includes the maximum possible millisecond value is fragile when using combinations of SmallDateTime, DateTime and DateTime2 - which all have different precisions.

I have added this piece of code in the beginning, to take the time element into account when using BETWEEN in the query. Hope you have seen it.

I hadn't done, no, but that is an example of what I was referring to. To calculate your endpoint you are adding one day, and then subtracting one second, to get you "inclusive endpoint", but any time element that is on the last day and some milliseconds after 23:59:59.000 will be outside your range (using BETWEEN), thus I suggest that using a LESS THAN test of 1-day-after your endpoint would be safer.

Thank You for the feedback, Kristen.

I think I should modify the query this way:

WHERE (FC.decisionDate >= @DecisionDateFrom and FC.decisionDate < @DecisionDateTo)

As @NewDecisionDateTo is '20161031 23:59:59.000'. So I am avoiding it

Please correct me if I am wrong.

Create local variables for all your input parameter and use local variables throughout the proc. Your SP performance will improve.

O/P is doing that:

or did I miss something perhaps?

Yes, that's fine.

I think that @NewDecisionDateTo needs to be '20161101 00:00:00.000' (rather than '20161031 23:59:59.000') - i.e. "Anything with DecisionDate BEFORE 01-Nov-2016"

I am talking about parameter sniffing.

Yup, got that, but the O/P appears to already be using @parameters in their WHERE clause, as per the example i gave. Unless I missed something?

Most of your parameters have been converted to NVARCHAR. Do the underlying tables have NVARCHAR as the datatype for the join columns? If not, then any joins will cause full table or full index scans no matter what indexing you used because of the datatype order of precedence.

Also, you're using DISTINCT in your queries to overcome duplicate returns. If you do, indeed return duplicates, then you have one or more accidental CROSS JOINs in the form of many-to-many joins that need to be resolved by splitting up your monster joins to eliminate that problem using proper "Divide'n'Conquer" techniques.

1 Like