Index used for rotation
New to SQL. I have a non-indexed table of about 20 million records that has one row for each task, I need one row for each task, each row of the task contains the task and the dates of the tasks. I am using a rod to accomplish this, which works. I have huge tempdb growth which is problematic. I need help setting up this query. When I look at the execution plan, I see about 80% because of the table scan. I believe indexing the original table will help, but I'm not sure how to index.
SELECT Job
,TSK_CD + 'F' AS TaskCode
,CAST(FORECAST_DATE AS SMALLDATETIME) AS TaskDate
FROM Task_Details AS FcstDateQuery
WHERE FORECAST_DATE IS NOT NULL
UNION
SELECT Job
,TSK_CD + 'A' AS TaskCode
,CAST(ACTUAL_DATE AS SMALLDATETIME) AS TaskDate
FROM Task_Details AS ActDateQuery
WHERE ACTUAL_DATE IS NOT NULL
) AS TaskDateQuery
PIVOT(MAX(TaskDate) FOR TaskCode IN (
[Code1F]
,[Code1A]
,[Code2F]
,[Code2A]
,[Code3F]
,[Code3A]
<... a bunch of other task codes>
)) AS Piv
The original table has columns
Job
,TSK_CD
,FORECAST_DATE
,ACTUAL_DATE
The summary table has:
Job Code1F Code1A Code2F Code2A Code3F Code3A....
I'd be grateful for pointers on how to index the original table or improve the query. The original table is static data (changes once a day by clicking me). I am using a query to create a target table.
source to share
Coverage Index:
create nonclustered index ix_Task_Details__Job_Tsk_Cd_cover)
on dbo.Task_Details (Job,Tsk_Cd)
include (Forecast_Date,ActualDate)
You can try conditional aggregation instead pivot()
:
select
Job
, [Code1F] = max(case when Tsk_Cd='Code1' then ForeCast_Date end)
, [Code1A] = max(case when Tsk_Cd='Code1' then Actual_Date end)
, [Code2F] = max(case when Tsk_Cd='Code2' then ForeCast_Date end)
, [Code2A] = max(case when Tsk_Cd='Code2' then Actual_Date end)
, [Code3F] = max(case when Tsk_Cd='Code3' then ForeCast_Date end)
, [Code3A] = max(case when Tsk_Cd='Code3' then Actual_Date end)
from dbo.Task_Details
group by Job
source to share