Hi everyone
I am not sure why the query is taking so long to run. If I run each CTE separately then it runs in less than 2 seconds. However, when I join them they take forever to run. I stop the query after about 10 minutes. I am pretty sure I am joining them incorrectly. What am I doing wrong?
Below is code:
ALTER PROCEDURE [dbo].[QueryStockData]
AS
TRUNCATE TABLE DBO.StockData;
WITH CTE1
AS
(
SELECTT1.SYMBOL, CONVERT(DATE, T1.DATE_DATETIME) AS TRADE_DATE, SUM(T1.FIELD1 * T1.FIELD2) AS VALUE1
FROMdbo.StockDB AS T1
WHERET1.FIELD2 = T1.FIELD3 AND T1.TYPE = 'A'
GROUP BYT1.SYMBOL, CONVERT(DATE, T1.DATE_DATETIME)
),
CTE2
AS
(
SELECTT2.SYMBOL, CONVERT(DATE, T2.DATE_DATETIME) AS TRADE_DATE, SUM(T2.FIELD1 * T2.FIELD2) AS VALUE2
FROMdbo.StockDB AS T2
WHERET2.FIELD2 = T2.FIELD4 AND T2.TYPE = 'B'
GROUP BYT2.SYMBOL, CONVERT(DATE, T2.DATE_DATETIME)
),
CTE3
AS
(
SELECTT3.SYMBOL, CONVERT(DATE, T3.DATE_DATETIME) AS TRADE_DATE, SUM(T3.FIELD1 * T3.FIELD2) AS VALUE3
FROMdbo.StockDB AS T3
WHERET3.FIELD2 = T3.FIELD3 AND T3.TYPE = 'C'
GROUP BYT3.SYMBOL, CONVERT(DATE, T3.DATE_DATETIME)
),
CTE4
AS
(
SELECTT4.SYMBOL, CONVERT(DATE, T4.DATE_DATETIME) AS TRADE_DATE, SUM(T4.FIELD1 * T4.FIELD2) AS VALUE4
FROMdbo.StockDB AS T4
WHERET4.FIELD2 = T4.FIELD4 AND T4.TYPE = 'D'
GROUP BYT4.SYMBOL, CONVERT(DATE, T4.DATE_DATETIME)
)
INSERT INTO DBO.StockData
SELECTT1.SYMBOL,
T1.TRADE_DATE,
T1.VALUE1,
T2.VALUE2,
T3.VALUE3,
T4.VALUE4
FROMCTE1 AS T1
JOINCTE2 AS T2 ON T1.SYMBOL = T2.SYMBOL AND T1.TRADE_DATE = T2.TRADE_DATE
JOINCTE3 AS T3 ON T3.SYMBOL = T2.SYMBOL AND T3.TRADE_DATE = T2.TRADE_DATE
JOINCTE4 AS T4 ON T4.SYMBOL = T3.SYMBOL AND T4.TRADE_DATE = T3.TRADE_DATE
Thank you in advance!
April 9, 2023 at 5:26 am
for some reason the spacing for some of the lines are messed up. the words are missing spaces between them. hopefully this isn't an issue for anyone
April 9, 2023 at 7:55 am
It's difficult to help without seeing the actual execution plan and the DDL for StockDB (assuming that is a table rather than a database!)
For a quick fix, try the divide and conquer approach. Push the results of each of the CTEs into temp tables and then use these in the final INSERT.
and the query supplied can likely be replaced with a single one
insert into DBO.StockData
select t.SYMBOL
, t.TRADE_DATE
, t.VALUE1
, t.VALUE2
, t.VALUE3
, t.VALUE4
from (
select T4.SYMBOL
, convert(date, T4.DATE_DATETIME) as TRADE_DATE
, sum(case when T4.FIELD2 = T4.FIELD3 and T4.TYPE = 'A' then T4.FIELD1 * T4.FIELD2 else 0 end) as VALUE1
, sum(case when T4.FIELD2 = T4.FIELD4 and T4.TYPE = 'B' then T4.FIELD1 * T4.FIELD2 else 0 end) as VALUE2
, sum(case when T4.FIELD2 = T4.FIELD3 and T4.TYPE = 'C' then T4.FIELD1 * T4.FIELD2 else 0 end) as VALUE3
, sum(case when T4.FIELD2 = T4.FIELD4 and T4.TYPE = 'D' then T4.FIELD1 * T4.FIELD2 else 0 end) as VALUE4
, max(case when T4.FIELD2 = T4.FIELD3 and T4.TYPE = 'A' then 1 else 0 end) as TypeA
, max(case when T4.FIELD2 = T4.FIELD4 and T4.TYPE = 'B' then 1 else 0 end) as TypeB
, max(case when T4.FIELD2 = T4.FIELD3 and T4.TYPE = 'C' then 1 else 0 end) as TypeC
, max(case when T4.FIELD2 = T4.FIELD4 and T4.TYPE = 'D' then 1 else 0 end) as TypeD
from dbo.StockDB as T4
where (T4.FIELD2 = T4.FIELD4 and T4.TYPE in ('B', 'D'))
or (T4.FIELD2 = T4.FIELD3 and T4.TYPE in ('A', 'C'))
group by T4.SYMBOL
, convert(date, T4.DATE_DATETIME)
) t
where t.TypeA = 1
and t.TypeB = 1
and t.TypeC = 1
and t.TypeD = 1
April 9, 2023 at 1:11 pm
Temp tables should work, but for best performance be sure to create a unique clustered index on the temp tables to aid in the subsequent joins. You'll want to create the index before loading the table.
Specifically:
DROP TABLE IF EXISTS #temp1;
SELECT TOP (0) T1.SYMBOL, CONVERT(DATE, T1.DATE_DATETIME) AS TRADE_DATE, SUM(T1.FIELD1 * T1.FIELD2) AS VALUE1
INTO #temp1
FROM dbo.StockDB AS T1
WHERE 0 = 1;
ALTER TABLE #temp1 ADD UNIQUE CLUSTERED ( SYMBOL, TRADE_DATE ) WITH ( FILLFACTOR = 100 );
INSERT INTO #temp1
SELECT TT1.SYMBOL, CONVERT(DATE, T1.DATE_DATETIME) AS TRADE_DATE, SUM(T1.FIELD1 * T1.FIELD2) AS VALUE1
FROM dbo.StockDB AS T1
WHERE T1.FIELD2 = T1.FIELD3 AND T1.TYPE = 'A'
GROUP BYT1.SYMBOL, CONVERT(DATE, T1.DATE_DATETIME)
And likewise for the other 3 temp tables.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2023 at 12:39 am
Thank you both. The query works now.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply