Tune execution performance

Transact-SQL
1

I need to speed up a query that is executed in a web application where I can't afford that it takes always 15 seconds on every postback. Each of the 4 subqueries is instantly executed, but the result that combines all the 4 takes too much time. I don't know if the "with" structure I choose is to blame? I post the structure so if anybody sees something critical that slows down speed please tell me.

with 
activities as 
    (SELECT OPPORTUNITYID,
            row_number() over(partition by QUOTATIONID order by Enddatetime desc) as ord
     FROM Table1
         left outer join Table2
         inner join Table3
         inner join Table4
     WHERE ...),

quotes as 
    (SELECT QuotationID,
            row_number() over(partition by QuotationID order by QUOTATIONDOCNUM desc) as ord
     FROM Table5
     WHERE ...),

opportunity as
    (SELECT OPPORTUNITYID
     FROM Table4
         left outer join Table3 
     WHERE  ...),

COE as 
    (SELECT OPPORTUNITYID,
            stuff((select ', ' + a.PRODFAMILYGRPID from Table6 a WHERE ... for xml path('')),1,1,'') AS COE
     FROM Table6
     WHERE ...
     GROUP BY OPPORTUNITYID)

SELECT ...
FROM opportunity
    left outer join quotes on quotes.QuotationID = opportunity.QUOTATIONID and quotes.ord = 1
    left outer join activities on opportunity.QUOTATIONID =activities.QUOTATIONID and activities.ord = 1
    left outer join COE on opportunity.OPPORTUNITYID = COE.OPPORTUNITYID
ORDER BY ...

Table3 has almost a million logical reads, Table5 400.000 if this does give any hint.
Martin

2

First, you must add a table alias to every column. You have to realize we know NOTHING about your tables.

For example, in the "activities" CTE, in which table(s) is(are) OPPORTUNITYID, QUOTATIONID and Enddatetime? If they're all in table1, for example, then the other tables don't need to appear in the query.

Also, the exact query, including all WHERE conditions and JOIN conditions, is critical to tuning the query. As are full table definitions, particularly all index defs.

3

Thank you for your answer. I know I let you know little about the tables. Posting the whole query won't change much though, I guess. The joins are all necessary - I just added symbolically some column, and I use the correct table alias on every column. And I pointed out the operations I suppose are more critical:
I do need the row_number () twice which I would like to avoid but couldn't find another way of fetching just each top item of a record stack, grouped by a column.
My experience with tuning usually was playing around a bit with the structure of the query, until almost by miracle it performs smoothly. But this time I don't have any luck.

4

With absolutely NOTHING to go in the way of details, I can't possibly help you. I don't do "miracle" tuning, I look at queries and indexes, and use SQL knowledge to remove unnecessary parts of queries too.

5

Have you looked at the execution plan for your query? Would it be possible to post that?

6 
   (52 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SMMACTIVITIES'. Scan count 0, logical reads 5423, physical reads 0, read-ahead reads 81, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALESQUOTATIONTABLE'. Scan count 52, logical reads 957228, physical reads 18, read-ahead reads 6800, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SMMACTIVITYPARENTLINKTABLE'. Scan count 52, logical reads 28912, physical reads 1, read-ahead reads 236, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SMMOPPORTUNITYTABLE'. Scan count 53, logical reads 316, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CUSTQUOTATIONJOUR'. Scan count 52, logical reads 400192, physical reads 2, read-ahead reads 199, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WK_CRMBUDGETDETAILS'. Scan count 68, logical reads 140, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 7800 ms,  elapsed time = 12419 ms.

Here goes the query:

with activities as 
			   (SELECT smmActivities.ACTIVITYNUMBER,
			           smmActivities.Purpose, 
			           smmActivities.Closed, 
			           smmActivities.Enddatetime, 
			           smmActivities.ResponsibleEmployee, 
			           SALESQUOTATIONTABLE.OPPORTUNITYID,
			           SALESQUOTATIONTABLE.QUOTATIONID,
					   row_number() over(partition by QUOTATIONID order by Closed, Enddatetime desc) as ord
				FROM smmActivityParentLinkTable
					left outer join smmActivities on smmActivityParentLinkTable.ACTIVITYNUMBER = smmActivities.ACTIVITYNUMBER and smmActivities.DATAAREAID = 'ES75'
					inner join SALESQUOTATIONTABLE on smmActivityParentLinkTable.REFRECID = SALESQUOTATIONTABLE.RECID and SALESQUOTATIONTABLE.DATAAREAID = 'ES75'
					inner join smmOpportunityTable on SALESQUOTATIONTABLE.OPPORTUNITYID = smmOpportunityTable.OPPORTUNITYID and smmOpportunityTable.DATAAREAID = 'ES75'
				WHERE smmActivityParentLinkTable.DATAAREAID = 'ES75' and smmActivityParentLinkTable.REFTABLEID = '1967' and year(smmActivities.createddatetime) >2013),
	 quotes as 
	 			(SELECT LEFT(QUOTATIONDOCNUM,11) as QuotationID,
					    Quotationdate,
					    Quotationamount,
					    CAST(RIGHT(QUOTATIONDOCNUM, LEN(QUOTATIONDOCNUM)-12) as INT) as Revision,
					    SALESADMINISTRATOR,
	                    row_number() over(partition by LEFT(QUOTATIONDOCNUM,11) order by QUOTATIONDOCNUM desc) as ord,
	                    SALESBALANCE
                 FROM CustQuotationJour
				 WHERE DATAAREAID ='ES75' and Year(Quotationdate)>2013),
	opportunity as
				(SELECT smmOpportunityTable.OPPORTUNITYID,
					   smmOpportunityTable.SUBJECT,
					   smmOpportunityTable.ESTIMATEDREVENUE,
					   smmOpportunityTable.OPENEDBY,
					   smmOpportunityTable.WK_RATINGVALUE, 
					   smmOpportunityTable.WK_BUSRELACCOUNT,
					   smmOpportunityTable.WK_PROCESSSTAGENUMBER,
					   smmOpportunityTable.WK_PROGRESSPERCENTVALUE, 
					   smmOpportunityTable.WK_QUALITYPERCENTVALUE,
					   smmOpportunityTable.WK_BUSINESSTYPEID,
					   smmOpportunityTable.PARTYID,
					   smmOpportunityTable.WK_CUSTACCOUNT,
					   smmOpportunityTable.WK_KEYBUYINGFACTORS,
					   CASE WHEN smmOpportunityTable.Status = 1 THEN 'active'
					   		WHEN smmOpportunityTable.Status = 2 THEN 'postponed'
					   		WHEN smmOpportunityTable.Status = 3 THEN 'confirmed'
					   		WHEN smmOpportunityTable.Status = 4 THEN 'cancelled'
					   		WHEN smmOpportunityTable.Status = 5 THEN 'lost' ELSE 'shit happened' END as Status,
					   SALESQUOTATIONTABLE.QUOTATIONID,
					   SALESQUOTATIONTABLE.QUOTATIONNAME, 
					   SALESQUOTATIONTABLE.ReceiptDateRequested,
					   SALESQUOTATIONTABLE.WK_SALESQUOTATIONTOTALAMOUNT,
					   SALESQUOTATIONTABLE.SalesResponsible as ASM,
					   SALESQUOTATIONTABLE.CreatedBY,
 					   CASE WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 0 THEN 'Created'
                            WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 1 THEN 'Sent'
                            WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 2 THEN 'Confirmed'
                            WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 3 THEN 'Lost'
                            WHEN SALESQUOTATIONTABLE.QUOTATIONSTATUS = 4 THEN 'Cancelled' END as StatusQuo

				FROM smmOpportunityTable
					left outer join SALESQUOTATIONTABLE on smmOpportunityTable.OPPORTUNITYID = SALESQUOTATIONTABLE.OPPORTUNITYID and SALESQUOTATIONTABLE.DATAAREAID = 'ES75'
				WHERE smmOpportunityTable.DATAAREAID = 'ES75' and smmOpportunityTable.WK_BUSINESSTYPEID = 'EPC'),

		COE as 
			   (SELECT OPPORTUNITYID,
					   stuff((select ', ' + a.PRODFAMILYGRPID
					          from WK_CRMBUDGETDETAILS a
							  WHERE a.DATAAREAID = 'ES75' and a.OPPORTUNITYID = WK_CRMBUDGETDETAILS.OPPORTUNITYID 
							  for xml path('')),1,1,'') AS COE
				FROM WK_CRMBUDGETDETAILS
				WHERE DATAAREAID = 'ES75'
				GROUP BY OPPORTUNITYID)

	            SELECT opportunity.*,					   
				       activities.ACTIVITYNUMBER,
					   activities.Purpose,
					   activities.Closed, 
					   activities.Enddatetime, 
					   activities.ResponsibleEmployee,
					   quotes.Revision,
	                   quotes.Quotationdate,
	                   quotes.Salesbalance,	                   
					   COALESCE(quotes.SALESADMINISTRATOR, opportunity.CreatedBY) as PM,
					   COE.COE

				from opportunity
					left outer join quotes on quotes.QuotationID = opportunity.QUOTATIONID and quotes.ord = 1
					left outer join	activities on opportunity.QUOTATIONID =activities.QUOTATIONID and activities.ord = 1
					left outer join COE on opportunity.OPPORTUNITYID = COE.OPPORTUNITYID
                ORDER BY ReceiptDateRequested desc

I don't believe in miracles either... that was just to say that I don't know too much about indexing. I know it's important for the performance but I could not change indexing anyway as I only have access rights to these databases. They belong to the ERP of our company so I presume they are correctly indexed.

7

ups... spotted an unnecessary join in activities, but that has no impact.
(eliminated inner join smmOpportunityTable in activities)

what had a mayor impact was changing the join from "left outer join" to "inner join" in the "opportunity" CTE. The execution was immediate. That must be a clue

(somewhat later)

Ok, the miracle occurred again and I really really would love to understand this. The same trick helped me already several times: I just add some unnecessary logical expression that doesn't change the result of the query and it is executed immediately. I declared a parameter @test = '*' and placed it in the where clause of the 'opportunity' CTE.

WHERE (smmOpportunityTable.DATAAREAID = 'ES75' and smmOpportunityTable.WK_BUSINESSTYPEID = 'EPC') or @test <> '*')

With this modification the execution plan must have been changed for good. Why? You tell me :smile: