How can i optimize the query

sam2929
sam2929 used Ask the Experts™
on
Hi,
How can i optimize below query:

SELECT DISTINCT
o.OrderNo AS 'Service Order Identifier'
	, usr.FirstName+' '+usr.LastName  AS ‘Order Analyst'
	, o.OrderType AS 'Provisioning Type'
	, soi.Action AS 'AccessLink'
	, o.CurrentStatus AS 'SO Ticket Status'
	, o.OrderEndDate AS 'Close Date'
	, o.OrderSentDate AS 'SO Submitted Date'
	, (SELECT MIN([WorkingDays].[fu_GetTargetWorkingDay] (5,1,oCreated.Created,20,'DD'))
		FROM [GcNet].[Trn.ServiceOrder] AS oCreated
		WHERE oCreated.Id = o.Id
		AND o.Created >= @StartDate AND o.Created <= @EndDate
		AND oCreated.SmFormStatusId = 3) AS 'SOA Date'
	, [WorkingDays].[fu_GetTargetWorkingDay] (5,1,ordResp.SORDueDate, 20,'DD')AS 'SOR DD'
	, ordResp.created AS 'SOR Date'
	, (SELECT CurrentStatusChangedDate
		FROM [GcNet].[Trn.ServiceOrder] AS oSusp
		WHERE oSusp.Id = o.Id
		AND o.Created >= @StartDate  AND o.Created <= @EndDate
		AND CurrentStatus = 'Suspension'
		) AS 'SO Suspension Date'
	, (SELECT CurrentStatusChangedDate
		FROM [GcNet].[Trn.ServiceOrder] AS oCan
		WHERE oCan.Id = o.Id
		AND o.Created >= @StartDate  AND o.Created <= @EndDate
		AND CurrentStatus = 'Cancelled'
		) AS 'SO Cancelled Date'
	, notice.CompletionDate AS 'WCN Date'
FROM [dbo].[Trn.ServiceOrder] AS o
	JOIN [dbo].[Trn.ServiceOrderItem] AS soi ON soi.OrderId = o.Id
	JOIN [dbo].[Trn.ServiceOrderItemAttribute] AS att ON att.ServiceOrderItemId = soi.Id
	JOIN [dbo].[Trn.OrderResponse] AS ordResp ON ordResp.OrderId = o.Id
	LEFT JOIN [dbo].[Trn.CompletionNotice] AS notice ON notice.OrderId= o.Id
	JOIN [dbo].[Trn.OrderResponse] AS ordRes ON ordRes.OrderID = o.ID
	JOIN [dbo].[Trn.RegisterUser] AS usr ON usr.ID = o.ModifiedBy
WHERE o.OrderTypeId IN (8,10)
	AND o.Created >= @StartDate AND o.Created <= @EndDate

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
First of all: The most common escape characters for object names containing special characters like white spaces or hyphens are the square brackets. Then your single quotes are not correctly used, caused you mixed in some acutes.

The biggest problem is imho the function call. I guess it's not a inline function.

But I would start with addressing the date range query, which is used multiple times. Using a temporary table is imho the best approach. Maybe a CTE is also sufficient. E.g.

WITH ServiceOrdersFiltered
AS ( SELECT o.*
     FROM   [GcNet].[Trn.ServiceOrder] o
     WHERE  o.Created >= @StartDate
            AND o.Created <= @EndDate )
SELECT DISTINCT o.OrderNo AS [Service Order Identifier] ,
                usr.FirstName + ' ' + usr.LastName AS [Order Analyst] ,
                o.OrderType AS [Provisioning Type] ,
                soi.Action AS AccessLink ,
                o.CurrentStatus AS [SO Ticket Status] ,
                o.OrderEndDate AS [Close Date] ,
                o.OrderSentDate AS [SO Submitted Date] ,
                (   SELECT MIN([WorkingDays].[fu_GetTargetWorkingDay](5, 1, oCreated.Created, 20, 'DD'))
                    FROM   ServiceOrdersFiltered oCreated
                    WHERE  oCreated.Id = o.Id
                           AND o.Created >= @StartDate
                           AND o.Created <= @EndDate
                           AND oCreated.SmFormStatusId = 3 ) AS [SOA Date] ,
                [WorkingDays].[fu_GetTargetWorkingDay](5, 1, ordResp.SORDueDate, 20, 'DD') AS [SOR DD] ,
                ordResp.created AS [SOR Date] ,
                (   SELECT CurrentStatusChangedDate
                    FROM   ServiceOrdersFiltered oSusp
                    WHERE  oSusp.Id = o.Id
                           AND CurrentStatus = 'Suspension' ) AS [SO Suspension Date] ,
                (   SELECT CurrentStatusChangedDate
                    FROM   ServiceOrdersFiltered oCan
                    WHERE  oCan.Id = o.Id
                           AND CurrentStatus = 'Cancelled' ) AS [SO Cancelled Date] ,
                notice.CompletionDate AS [WCN Date]
FROM   ServiceOrdersFiltered o
       JOIN [dbo].[Trn.ServiceOrderItem] soi ON soi.OrderId = o.Id
       JOIN [dbo].[Trn.ServiceOrderItemAttribute] att ON att.ServiceOrderItemId = soi.Id
       JOIN [dbo].[Trn.OrderResponse] ordResp ON ordResp.OrderId = o.Id
       LEFT JOIN [dbo].[Trn.CompletionNotice] notice ON notice.OrderId = o.Id
       JOIN [dbo].[Trn.OrderResponse] ordRes ON ordRes.OrderID = o.ID
       JOIN [dbo].[Trn.RegisterUser] usr ON usr.ID = o.ModifiedBy
WHERE  o.OrderTypeId IN ( 8, 10 );

Open in new window

And, of course, check your indices. Do they support your query? Or asked differently: Do you have scans instead of seeks in your actual query plan?
Top Expert 2014

Commented:
Although it should be optimized by the query engine, you can change instances of this:
o.Created >= @StartDate AND o.Created <= @EndDate

Open in new window

to this:
o.Created Between @StartDate AND @EndDate

Open in new window

and see if it helps.

You have two choices when it comes to the correlated query columns.
* CTEs
* Add them as data sources in your FROM clause.  This is especially true for the MIN() [SOA Date] column.

Please post your Explain result and your indexes on these tables.

You didn't show it in your question text, so what are @StartDate and @EndDate?  It is possible that your poor performance is the result of parameter sniffing.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
1) The DISTINCT is going to cause a full sort all the output.  That will be a big performance drag.

2) If you almost (or almost always) specify a created date range when reading the [Trn.ServiceOrder] table, then you need to cluster that table by "Created".  
Please show index definitions for ALL indexes currently on that table.

3) You are doing 3 separate lookups to the [Trn.ServiceOrder] table; you could consolidate those into a single lookup query.

SELECT DISTINCT
o.OrderNo AS 'Service Order Identifier'
      , usr.FirstName+' '+usr.LastName  AS 'Order Analyst'
      , o.OrderType AS 'Provisioning Type'
      , soi.Action AS 'AccessLink'
      , o.CurrentStatus AS 'SO Ticket Status'
      , o.OrderEndDate AS 'Close Date'
      , o.OrderSentDate AS 'SO Submitted Date'
      , Lookups.'SOA Date'
      , [WorkingDays].[fu_GetTargetWorkingDay] (5,1,ordResp.SORDueDate, 20,'DD')AS 'SOR DD'
      , ordResp.created AS 'SOR Date'
      , Lookups.'SO Suspension Date'
      , Lookups.'SO Cancelled Date'
      , notice.CompletionDate AS 'WCN Date'
FROM [dbo].[Trn.ServiceOrder] AS o
      OUTER APPLY (
         SELECT 
              MIN(CASE WHEN SmFormStatusId = 3 THEN [WorkingDays].[fu_GetTargetWorkingDay] (5,1,Created,20,'DD') END) AS 'SOA Date',
              MAX(CASE WHEN CurrentStatus = 'Suspension' THEN CurrentStatusChangedDate END) 
                  AS 'SO Suspension Date',
              MAX(CASE WHEN CurrentStatus = 'Cancelled' THEN CurrentStatusChangedDate END) 
                  AS 'SO Cancelled Date'
            FROM [GcNet].[Trn.ServiceOrder]
            WHERE Id = o.Id
            AND Created >= @StartDate  AND Created <= @EndDate
            AND (SmFormStatusId = 3 OR (CurrentStatus IN ('Cancelled', 'Suspension')))
      ) AS Lookups
      JOIN [dbo].[Trn.ServiceOrderItem] AS soi ON soi.OrderId = o.Id
      JOIN [dbo].[Trn.ServiceOrderItemAttribute] AS att ON att.ServiceOrderItemId = soi.Id
      JOIN [dbo].[Trn.OrderResponse] AS ordResp ON ordResp.OrderId = o.Id
      LEFT JOIN [dbo].[Trn.CompletionNotice] AS notice ON notice.OrderId= o.Id
      JOIN [dbo].[Trn.OrderResponse] AS ordRes ON ordRes.OrderID = o.ID
      JOIN [dbo].[Trn.RegisterUser] AS usr ON usr.ID = o.ModifiedBy
WHERE o.OrderTypeId IN (8,10)
      AND o.Created >= @StartDate AND o.Created <= @EndDate

Open in new window

The DISTINCT seems to be used because you don't know the data model...
What are Primary and Foreign keys in your tables? What relations are between tables?

I would guess the Id column in  [dbo].[Trn.ServiceOrder] is the PK.
I would also guess the Id column in  [GcNet].[Trn.ServiceOrder]  is the PK. Or is it just incorrect design?
If I am not mistaken then you don't need all the hara-kiri with subselects and you also don't need to join  the  [dbo].[Trn.OrderResponse]  table twice. It multiplies rows on output.

And we can find more... Why is the [dbo].[Trn.ServiceOrderItemAttribute] table involved in the query? It (again) just multiplies rows on output.

So please disclose the relations between the tables then the help will give you much higher value.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial