Chris
Reputation: 845
Optimizing SQL-Query using CTE
I have written the following (simplified) query:
;WITH
PreAggregated AS
(
--Group by including ClassId and MeasureId
SELECT
[ColumnA],[ColumnB],[ColumnC],[ClassId],[MeasureId]
, SUM(a.[NonAggregatedValues]) as PreAggregatedValues
FROM [TableA] a
INNER JOIN [TableB] b on b.PrimaryKey = a.ForeignKey1
INNER JOIN [TableC] c on c.PrimaryKey = a.ForeignKey2
WHERE a.ReportDate = @reportDate AND c.TechID IN ([...])
GROUP BY
[ColumnA],[ColumnB],[ColumnC],[ClassId],[MeasureId]
),
AllCombinations AS
(
--No values aggregated, just gets all possible combinations and is then left joined to [A],[B],[C],...
SELECT [ColumnA],[ColumnB],[ColumnC] FROM PreAggregated a
WHERE TechID IN ([...]) AND ReportDate = @reportDate
GROUP BY [ColumnA],[ColumnB],[ColumnC]
),
A AS
(
--Pivot for all ClassId = 5 using MeasureId 1-3
SELECT [ColumnA],[ColumnB],[ColumnC],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT [ColumnA],[ColumnB],[ColumnC],[ClassId],[MeasureId]
FROM PreAggregated
WHERE [ClassId] = 5 AND TechID IN ([...]) AND ReportDate = @reportDate) as [DATA]
PIVOT(
Sum([PreAggregatedValues])
FOR MeasureId IN ([1],[2],[3])) AS PIVOT_DATA
GROUP BY [...]
),
B AS
(
--Pivot for all ClassId = 6 using MeasureId 1-3
SELECT [ColumnA],[ColumnB],[ColumnC],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT [...]
FROM PreAggregated
WHERE [ClassId] = 6 AND TechID IN ([...]) AND ReportDate = @reportDate) as [DATA]
PIVOT(
Sum([PreAggregatedValues])
FOR MeasureId IN ([1],[2],[3])) AS PIVOT_DATA
GROUP BY [...]
),
C AS
(
--Pivot for all ClassId = 7 using MeasureId 1-3
[...]
)
SELECT
AllCombinations.[x],
AllCombinations.[y],
[...],
A.[1] [A_1],
A.[2] [A_2],
A.[3] [A_3],
B.[1] [B_1],
B.[2] [B_2],
B.[3] [B_3],
C.[1] [C_1],
C.[2] [C_2],
C.[3] [C_3],
[...]
FROM
AllCombinations
LEFT JOIN A ON AllCombinations.TechID = A.TechID
LEFT JOIN B ON AllCombinations.TechID = B.TechID
LEFT JOIN C ON AllCombinations.TechID = C.TechID
[...]
I feel this is by far not the best way to carry out the query. I´ve been trying out a lot of options and this is the most performant design I´ve come up with. Also, of course I´ve been using indexes, but it´s about the query design here.
Is there a way to optimize this? In particular:
- The pivotised sections [A][B][C],... (in total up to 7) are nearly identical and just differ by the ClassId filter. Can I merge those queries somehow?
- Is a common table expression the best way to handle this?
UPDATE:
Here´s the complete, alienated query:
CREATE OR ALTER FUNCTION [dbo].[Complete_Pivot]
(
@RDate DATETIME,
@pid INT
)
RETURNS TABLE
AS
RETURN
(
WITH
PIDs AS
(
SELECT PID, PPID FROM PTree a WHERE a.RDate = @RDate AND a.PID = @pid AND a.PID NOT IN (9359,9360,9361)
UNION ALL
SELECT child.PID, child.PPID FROM PTree child INNER JOIN PIDs parent ON child.PPID = parent.PID WHERE child.RDate = @RDate AND child.PID NOT IN (9359,9360,9361)
),
PreAggregated AS
(
SELECT
se.[RDate]
, rf.[RCID]
, rf.[RMID]
, de.[PID]
, de.[PCID]
, de.[DID]
, de.INSID
, de.INTID
, de.[DDID]
, de.[LID]
, de.[LT]
, de.[RC]
, SUM(se.[SVal]) as PreAggregatedValues
FROM [TableS] se
INNER JOIN [TableRF] rf on rf.RFID = se.RFID
INNER JOIN [TableD] de on de.DID = se.DID
WHERE se.RDate = @RDate AND de.PID IN (SELECT PID FROM PIDs)
GROUP BY
se.[RDate]
, rf.[RCID]
, rf.[RMID]
, de.[PID]
, de.[PCID]
, de.[DID]
, de.INSID
, de.INTID
, de.[DDID]
, de.[LID]
, de.[LT]
, de.[RC]
),
AllCombinations AS
(
SELECT DID, [DDID], [PID], [INSID], INTID, [LID],[LT],[RC],[RDate] FROM PreAggregated
WHERE PID IN (SELECT PID FROM PIDs) AND RDate = @RDate
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate]
),
[A] AS
(
SELECT DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM PreAggregated
WHERE RCID = 5 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA
PIVOT(
Sum(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate]
),
[B] AS
(
SELECT DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM PreAggregated
WHERE RCID = 6 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA
PIVOT(
Sum(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate]
),
[C] AS
(
SELECT DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM PreAggregated
WHERE RCID = 7 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA
PIVOT(
Sum(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate]
),
[D] AS
(
SELECT DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM PreAggregated
WHERE RCID = 8 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA
PIVOT(
Sum(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate]
),
[E] AS
(
SELECT DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM PreAggregated
WHERE RCID = 9 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA
PIVOT(
Sum(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate]
)
SELECT
AllCombinations.[RDate],
AllCombinations.PID,
AllCombinations.[DID],
AllCombinations.[INSID],
AllCombinations.INTID,
AllCombinations.[DDID],
AllCombinations.[LID],
AllCombinations.[LT],
AllCombinations.[RC],
A.[1] [A_1],
A.[2] [A_2],
A.[3] [A_3],
B.[1] [B_1],
B.[2] [B_2],
B.[3] [B_3],
C.[1] [C_1],
C.[2] [C_2],
C.[3] [C_3],
D.[1] [D_1],
D.[2] [D_2],
D.[3] [D_3],
E.[1] [E_1],
E.[2] [E_2],
E.[3] [E_3],
FROM
AllCombinations
LEFT JOIN A ON AllCombinations.DID = A.DID
LEFT JOIN B ON AllCombinations.DID = B.DID
LEFT JOIN C ON AllCombinations.DID = C.DID
LEFT JOIN D ON AllCombinations.DID = D.DID
LEFT JOIN E ON AllCombinations.DID = E.DID
)
UPDATE 2:
Find the query plan here: https://www.brentozar.com/pastetheplan/?id=r1KBQOkgi
Upvotes: 0
Views: 122
Answers (1)
Rom Eh
Reputation: 2063
Some improvements can be made to your request:
- Make use of variable tables if the function is required, or temporary table if you can switch to a stored procedure, to split cascaded CTEs,
- Avoid extra filters, if data is already filtered,
- Avoid the pattern
<field> IN (SELECT <field> FROM <table>). Replace byINNER JOINclauses or at least byEXISTS (SELECT 1 FROM <table> al WHERE al.<field> = a2.<field>) - In temporary tables only keep used fields
There is a first attempt to optimize your function code:
DECLARE @selectedPids TABLE
(
PID bigint NOT NULL,
RDate datetime NOT NULL
);
WITH recursivePID AS
(
SELECT PID,
PPID
FROM PTree a
WHERE a.RDate = @RDate AND a.PID = @pid AND a.PID NOT IN (9359,9360,9361)
UNION ALL
SELECT child.PID, child.PPID
FROM PTree child
INNER JOIN recursivePID parent ON child.PPID = parent.PID
WHERE child.RDate = @RDate AND child.PID NOT IN (9359,9360,9361)
)
INSERT INTO @selectedPids
SELECT PID,
@RDate
FROM recursivePID
GROUP BY PID;
DECLARE @aggregatedPids TABLE
(
RDate datetime NOT NULL,
RCID bigint,
RMID bigint,
PID bigint,
PCID bigint,
DID bigint,
INSID bigint,
INTID bigint,
DDID bigint,
LID bigint,
LT varchar(50),
RC varchar(50),
PreAggregatedValues bigint
);
SELECT se.[RDate]
, rf.[RCID]
, rf.[RMID]
, de.[PID]
, de.[PCID]
, de.[DID]
, de.INSID
, de.INTID
, de.[DDID]
, de.[LID]
, de.[LT]
, de.[RC]
, SUM(se.[SVal]) as PreAggregatedValues
FROM @selectedPids pi
INNER JOIN [TableD] de ON pi.PID = de.PID
INNER JOIN [TableS] se ON de.DID = se.DID AND pi.RDate = se.RDate -- index on DID AND RDate
INNER JOIN [TableRF] rf on se.RFID = rf.RFID
GROUP BY se.[RDate], rf.[RCID], rf.[RMID], de.[PID], de.[PCID], de.[DID], de.INSID, de.INTID, de.[DDID], de.[LID], de.[LT], de.[RC];
DECLARE @combinedPids TABLE
(
DID bigint,
DDID bigint,
PID bigint,
INSID bigint,
INTID bigint,
LID bigint,
LT varchar(50),
RC varchar(50),
RDate datetime NOT NULL
);
-- PID and Rdate were already filtered in above queries
INSERT INTO @combinedPids
SELECT DID, [DDID], [PID], [INSID], INTID, [LID],[LT],[RC],[RDate]
FROM @aggregatedPids ap
WHERE ESISTS(SELECT 1 FROM @selectedPids sp WHERE sp.PID = ap.PID) PID IN (SELECT PID FROM PIDs) AND RDate = @RDate
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate];
DECLARE @pivoted TABLE
(
Class varchar(1) NOT NULL,
DID bigint,
DDID bigint,
PID bigint,
INSID bigint,
INTID bigint,
LID bigint,
LT varchar(50),
RC varchar(50),
RDate datetime NOT NULL,
Aggregated1 bigint,
Aggregated2 bigint,
Aggregated3 bigint
);
-- Create a sub function to factorize code.
INSERT INTO @pivoted
SELECT 'A', DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM @aggregatedPids
WHERE RCID = 5 --AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate >> already filtered
) as DATA
PIVOT(
SUM(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])
) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate];
INSERT INTO @pivoted
SELECT 'B', DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM @aggregatedPids
WHERE RCID = 6 --AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate >> already filtered
) as DATA
PIVOT(
SUM(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])
) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate];
-- ... complete over pivot ...
INSERT INTO @pivoted
SELECT 'E', DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
FROM (
SELECT RMID, DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues]
FROM @aggregatedPids
WHERE RCID = 9 --AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate >> already filtered
) as DATA
PIVOT(
SUM(PreAggregatedValues)
FOR RMID IN ([1],[2],[3])
) AS PIVOT
GROUP BY DID, [DDID],[PID], [INSID], INTID, [LID],[LT],[RC],[RDate];
SELECT cp.[RDate],
cp.PID,
cp.[DID],
cp.[INSID],
cp.INTID,
cp.[DDID],
cp.[LID],
cp.[LT],
cp.[RC],
A.[Aggregated1] [A_1],
A.[Aggregated2] [A_2],
A.[Aggregated3] [A_3],
B.[Aggregated1] [B_1],
B.[Aggregated2] [B_2],
B.[Aggregated3] [B_3],
C.[Aggregated1] [C_1],
C.[Aggregated2] [C_2],
C.[Aggregated3] [C_3],
D.[Aggregated1] [D_1],
D.[Aggregated2] [D_2],
D.[Aggregated3] [D_3],
E.[Aggregated1] [E_1],
E.[Aggregated2] [E_2],
E.[Aggregated3] [E_3],
FROM @combinedPids cp
LEFT JOIN @pivoted A ON cp.DID = A.DID
LEFT JOIN @pivoted B ON cp.DID = B.DID
LEFT JOIN @pivoted C ON cp.DID = C.DID
LEFT JOIN @pivoted D ON cp.DID = D.DID
LEFT JOIN @pivoted E ON cp.DID = E.DID;
Upvotes: 1
Related Questions
- Joining Two CTE's with Pivots
- Optimize CTE query
- Improving Performance of CTE
- Improve performance on CTE with sub-queries
- Convert ugly T SQL query to CTE
- CTE Pivot in sql server
- Convert CTE to better performing query
- SQL CTE to improve performance
- Improve SQL CTE query performance
- Common table expression and pivot