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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply