CODEWITHSUNDEEP
sqlsql-server
user3904868
user3904868

Reputation:

Multiple OUTER APPLY getting slow, best approach to improve query speed

I'm trying to do lots of in table data collation where single record needs to collate data based on the same table but with previous records by date.

Currently I have 6 OUTER APPLY which is taking approx 3 minutes to run for 1 date. I will probably need 50+ calculated fields and multiple dates so this is beginning to look unworkable.

Is there a better way to do this to improve query speed?

DECLARE @Date datetime;
SET @Date = '2018-01-01';

SELECT * --Not real Select, set as * to simplify

-- Following subquery normally contains methods to clean data 
FROM (SELECT t1.* FROM  (SELECT cleanFields1.* FROM Control AS cleanFields1 
WHERE cleanFields1.[QDate] = @Date) AS t1) t1 

-- Calculated Data

OUTER APPLY (
    SELECT COUNT(*) AS ProductCountMonth
    FROM Control t6
    WHERE t6.[ProductName] = t1.[ProductName]
    AND t6.[QDate] < t1.[QDate]
    AND MONTH(t6.[QDate]) = MONTH(t1.[QDate])
) t6

OUTER APPLY (
    SELECT COUNT(*) AS ProductMatchMonth
    FROM Control t7
    WHERE t7.[ProductName] = t1.[ProductName]
    AND t7.[QDate] < t1.[QDate]
    AND t7.[Issue] = '1'
    AND MONTH(t7.[QDate]) = MONTH(t1.[QDate])
) t7

OUTER APPLY (
    SELECT COUNT(*) AS ProductCountArea
    FROM Control t8
    WHERE t8.[ProductName] = t1.[ProductName]
    AND t8.[QDate] < t1.[QDate]
    AND t8.[AreaName] = t1.[AreaName]
) t8

OUTER APPLY (
    SELECT COUNT(*) AS ProductMatchArea
    FROM Control t9
    WHERE t9.[ProductName] = t1.[ProductName]
    AND t9.[QDate] < t1.[QDate]
    AND t9.[Issue] = '1'
    AND t9.[AreaName] = t1.[AreaName]
) t9

OUTER APPLY (
    SELECT COUNT(*) AS ProductCountPType
    FROM Control t10
    WHERE t10.[ProductName] = t1.[ProductName]
    AND t10.[QDate] < t1.[QDate]
    AND t10.[PType] = t1.[PType]
) t10

OUTER APPLY (
    SELECT COUNT(*) AS ProductMatchPType
    FROM Control t11
    WHERE t11.[ProductName] = t1.[ProductName]
    AND t11.[QDate] < t1.[QDate]
    AND t11.[Issue] = '1'
    AND t11.[PType] = t1.[PType]
) t11

EDIT:

SQLFiddle: http://sqlfiddle.com/#!18/9541d/1

Desired Output: enter image description here

Upvotes: 3

Views: 3163

Answers (3)

Sean Lange
Sean Lange

Reputation: 33571

You can eliminate all those cross applys which will greatly help performance. Also you should avoid things like '1' when Issue is an int. You should use 1.

In this case I used a cte to show how you can isolate the rows you want returned. From that is just some conditional aggregation.

DECLARE @Date datetime = '2018-01-01';

with CurrentRows as
(
    select *
    from Control c
    where c.QDate = @Date
)

select cr.*
    , ProductCountMonth = sum(case when MONTH(c.QDate) = MONTH(cr.QDate) then 1 else 0 end)
    , ProductMatchMonth = sum(case when MONTH(c.QDate) = MONTH(cr.QDate) AND c.Issue = 1 then 1 else 0 end)
    , ProductCountArea = sum(case when c.AreaName = cr.AreaName then 1 else 0 end)
    , ProductMatchArea = sum(case when c.Issue = 1 and c.AreaName = cr.AreaName then 1 else 0 end)
    , ProductCountPType = sum(case when c.PType = cr.PType then 1 else 0 end)
    , ProductMatchPType = sum(case when c.PType = cr.PType and c.Issue = 1 then 1 else 0 end)
from CurrentRows cr
join Control c on c.QDate < cr.QDate and c.ProductName = cr.ProductName
group by cr.QDate
    , cr.ProductName
    , cr.AreaName
    , cr.PType
    , cr.Issue
order by cr.AreaName

Upvotes: 2

JamieD77
JamieD77

Reputation: 13949

In your example, you can combine the 6 outer apply's into 3 and cut the execution time down some.

SELECT * --Not real Select, set as * to simplify

FROM (SELECT t1.* FROM  (SELECT cleanFields1.* FROM Control AS cleanFields1 
WHERE cleanFields1.[QDate] = '2018-01-01') AS t1) t1 

OUTER APPLY (
    SELECT  COUNT(*) ProductCountMonth,
            COUNT(CASE WHEN t2.Issue = 1 THEN 1 END) ProductMatchMonth
    FROM    Control t2
    WHERE   t2.ProductName = t1.ProductName
    AND     t2.[QDate] < t1.[QDate]
    AND     MONTH(t2.[QDate]) = MONTH(t1.[QDate])

) t2

OUTER APPLY (
    SELECT  COUNT(*) ProductCountArea,
            COUNT(CASE WHEN t3.Issue = 1 THEN 1 END) ProductMatchArea
    FROM    Control t3
    WHERE   t3.ProductName = t1.ProductName
    AND     t3.[QDate] < t1.[QDate]
    AND     t3.AreaName = t1.AreaName

) t3

OUTER APPLY (
    SELECT  COUNT(*) ProductCountPType,
            COUNT(CASE WHEN t4.Issue = 1 THEN 1 END) ProductMatchPType
    FROM    Control t4
    WHERE   t4.ProductName = t1.ProductName
    AND     t4.[QDate] < t1.[QDate]
    AND     t4.PType = t1.PType

) t4

This uses a case expression in your Match counts to determine if Issue = 1.

Upvotes: 0

uzi
uzi

Reputation: 4146

I suggest you to change your outer apply's to window functions. Must be faster. But your SQL version must be at least 2012

DECLARE @Date datetime;
SET @Date = '2018-01-01';

select
    *
from (
    SELECT 
        cleanFields1.*
        , row_number() over (partition by ProductName, month(QDate) order by QDate) - 1 AS ProductCountMonth
        , sum(iif(Issue = 1, 1, 0)) over (partition by ProductName, month(QDate) order by QDate) AS ProductMatchMonth
        , row_number() over (partition by ProductName, AreaName order by QDate) - 1 AS ProductCountArea
        , sum(iif(Issue = 1, 1, 0)) over (partition by ProductName, AreaName order by QDate) AS ProductMatchArea
        , row_number() over (partition by ProductName, PType order by QDate) - 1 AS ProductCountPType
        , sum(iif(Issue = 1, 1, 0)) over (partition by ProductName, PType order by QDate) AS ProductMatchPType
    FROM 
        Control AS cleanFields1
) t
where
    QDate = @Date

Upvotes: 0

Related Questions