optimize complex sql query

Question

I am using azure sql server database. I have written one sql query to generate reprot. Here it is:

;WITH cte AS
(
  SELECT ProjectID, CreatedDateUTC, ProductID, LicenseID, BackgroundID from Project p 
  WHERE  CAST(p.CreatedDateUTC AS DATE) >= @StartDate and CAST(p.CreatedDateUTC AS DATE) <= @EndDate 
  and IsBackgroundUsed = 1
  and s7ImageGenerated = 1  and p.SiteCode in ('b2c' )
)
SELECT ProjectID , CreatedDateUTC,
(SELECT BackgroundName from Background b WHERE b.BackgroundID = cte.BackgroundID) AS BackgroundName,
(SELECT Name FROM Product pr WHERE pr.ProductID = cte.ProductID) AS ProductName,
Case WHEN LicenseID is null THEN 'Standard' ELSE (SELECT LicenseName from License l WHERE l.LicenseID = cte.LicenseID) END AS CLA,
(SELECT PurchaseFG from Product_background pb WHERE pb.BackgroundID = cte.BackgroundID and pb.ProductId = cte.productID) AS PurchaseFG,
(SELECT FGcode from Product pr WHERE pr.ProductID = cte.ProductID) AS ProductFGCode,
--(Select dbo.[getProjectFGCodeByBackground](cte.ProductID, cte.BackgroundID)) AS FGCode,
'' AS ERPOrderNumber,
0 AS DesignQuanity
from cte
WHERE (SELECT count(*) from Approval.OrderDetail od WHERE od.ProjectID = cte.ProjectID) = 0

Is there any way to optimize this query. Timeout issue comes. I have written this query in store procedure and calling that store procedure using linq entity framework.

Earlier i have used join but it's more slow down so tried with sub query. Worked more then one year now not working.

Hey, you could start by adding the estimated and actual execution plan. You can use [this](https://www.brentozar.com/pastetheplan/) to paste the plan XML into and then add them to your question. — Rigerta, Feb 20 '18 at 09:39
you can replace the select count on the where clause by a "where exists ..." , if the table approval.orderdetail is big then this change will make a significant improve in performance — Jayvee, Feb 20 '18 at 09:48
The normal way would be to use joins (as you tried already) AND use proper indexing of columns used in the `ON` clause and in the `WHERE` clause. You are using a `CAST()` in your where clause inside the `CTE` which probably won't let you use an index, even if you had one on the CreatedDateUTC because of [this](https://stackoverflow.com/questions/1724325/function-call-in-where-clause) — Rigerta, Feb 20 '18 at 10:42

Jayvee · Answer 1 · 2018-02-20T10:41:57.067

1

This will definitely improve the performance, especially if the table Approval.OrderDetail is large:

...WHERE not exists
(SELECT 1 from Approval.OrderDetail od WHERE od.ProjectID = cte.ProjectID)

edited Feb 20 '18 at 10:41

answered Feb 20 '18 at 10:21

Jayvee

10,670
3
29
40

@Jayvee good idea. we can use not exists. but still need some other improvement. If you have any other solution please share – jpatel Feb 20 '18 at 10:36

score 1 · Answer 2 · answered Feb 20 '18 at 10:42

Writing a sub-select for every single field is a terrible way to retrieve data, as you'll likely end up with a lot of Loop Joins which have terrible performance over large data sets.

Your original JOIN method is the way to go, but you need to ensure you have appropriate indexes on your joining columns.

You can also replace the WHERE clause, with a LEFT JOIN and IS NULL combination

LEFT JOIN Approval.OrderDetail od
ON od.ProjectID = p.ProjectID
...
AND od.ProjectID IS NULL;

or a NOT EXISTS (although that is more likely to have to SCAN a wider range of rows for each row returned by the main query).

WHERE NOT EXISTS 
(SELECT 1 FROM Approval.OrderDetail od WHERE od.ProjectID = cte.ProjectID)

In either case, make sure your Project table is appropriately indexed on (IsBackgroundUsed, s7ImageGenerated, SiteCode, CreatedDate) and that all joins are appropriately indexed.

I'd also question whether you actually need to cast your CreatedDateUTC fields to DATE types?

A possible simplification could be:

SELECT
    p.ProjectID,
    p.CreatedDateUTC,
    b.BackgroundName,
    pr.Name,
    IIF(p.LicenseID IS NULL, 'Standard', l.LicenseName) AS CLA,
    pb.PurchaseFG,
    pr.FGCode AS ProductFGCode,
    '' AS ERPOrderNumber,
    0 AS DesignQuantity
FROM Project p
LEFT JOIN Approval.OrderDetail od
ON od.ProjectID = p.ProjectID
LEFT JOIN Background b
ON b.BackgroundID = p.BackgroundID
LEFT JOIN Product pr 
ON pr.ProductID = p.ProductID
LEFT JOIN License l
ON l.LicenseID = p.LicenseID
LEFT JOIN Product_Background pb
ON pb.BackgroundID = p.BackgroundID
AND pb.ProductID = p.ProductID
WHERE p.CreatedDateUTC >= @StartDate AND p.CreatedDateUTC <= @EndDate
  AND p.IsBackgroundUsed = 1
  AND p.s7ImageGenerated = 1 
  AND p.SiteCode = 'b2c'
  AND od.ProjectID IS NULL;

@Paparazzi OD cannot produce multiple rows if we're checking for the JOINing key being NULL. It can only produce no rows, like a NOT EXISTS. — Mark Sinkinson, Feb 21 '18 at 12:29

score 0 · Answer 3 · answered Feb 20 '18 at 11:15

WHERE  CAST(p.CreatedDateUTC AS DATE) >= @StartDate and CAST(p.CreatedDateUTC AS DATE) <= @EndDate

make this SARGAble ,create non clustered index on CreatedDateUTC

Suppose this is the parameter ,

 declare @StartDate datetime='2018-02-01'
 declare @EndDate datetime='2018-02-28'

Then,

set @EndDate=dateadd(second,-1,dateadd(day,1,@EndDate))

now you can safely use do this,

WHERE  p.CreatedDateUTC  >= @StartDate and p.CreatedDateUTC  <= @EndDate

I think,@Mark Sinkinson query will work ok than sub query.( I will try NOT EXISTS clause once)

Use INNER JOIN if possible. Hope you are using Store Procedure and calling the SP.

Create index on all joins columns.

Since your sub query is working fine output wise without TOP 1 so it appear that all tables have ONE to ONE relation with Project .

CREATE NONCLUSTERED INDEX IX_Project ON project (
    CreatedDateUTC
    ,IsBackgroundUsed
    ,s7ImageGenerated
    ,SiteCode
    ) include (ProductID,LicenseID,BackgroundID);

Hope projectID is already Clustered Index.

paparazzo · Answer 4 · 2018-02-21T12:30:47.397

Might not be much faster but easier to read for me.

You should be able to adjust @StartDate and @EndDate and not have to cast to date.

Have an index on all join and where conditions.

If those are FK you should be able to use an inner join (and should).

SELECT P.ProjectID , P.CreatedDateUTC,
       b.BackgroundName,
       pr.Name AS ProductName, 
       isnull(l.LicenseName, 'Standard') as CLA,
       pb.PurchaseFG,
       pr.FGcode AS ProductFGCode,
       '' AS ERPOrderNumber,
       0  AS DesignQuanity
 from Project p 
 left join Background b 
   on b.BackgroundID  = p.BackgroundID 
 left join Product pr
   on pr.ProductID    = p.ProductID 
 left join License l 
   on l.LicenseID     = p.LicenseID 
 left join Product_background pb 
   on pb.BackgroundID = p.BackgroundID 
  and pb.ProductId    = p.productID
 left join Product pr 
   on pr.ProductID    = p.ProductID
WHERE CAST(p.CreatedDateUTC AS DATE) >= @StartDate 
  and CAST(p.CreatedDateUTC AS DATE) <= @EndDate 
  and p.IsBackgroundUsed = 1
  and p.s7ImageGenerated = 1  
  and p.SiteCode = 'b2c'
  and not exists (SELECT 1 
                  from Approval.OrderDetail od 
                  WHERE od.ProjectID = p.ProjectID)

This isn't the same as `LicenseName` could be NULL without `LicenseID` being NULL. Also `NOT EXISTS`, not `NOT EXITS` — Mark Sinkinson, Feb 21 '18 at 12:21

optimize complex sql query

4 Answers4