How can i optimize the query

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

sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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?
aikimarkCommented:
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.
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.