I've got a query I'm trying to run that runs a long time (~ 1 hour) and doesn't produce the right results. I'd like some recommendations on how to optimize this (we're going to need to run it several times a day), as well as some ideas on what could be going wrong. My current code is listed below.
I'm trying to get a full list of days from @StartDate to the current day, the amount of data migrated on each day, and the number of items migrated on the day. The day should be displayed even if there was nothing migrated on that day. The @temp table works and I can SELECT from it and get the right results. When I add the SELECT as written, though, it runs for over an hour and doesn't return the desired results.
Declare @StartDate datetime = '2015-10-01'
Declare @EndDate datetime = CAST(SYSDATETIME() as date)
declare @temp Table
(
DayDate datetime
);
WHILE @StartDate <= @EndDate
begin
INSERT INTO @temp (DayDate) VALUES (@StartDate);
SET @StartDate = Dateadd(Day,1, @StartDate);
end ;
SELECT TOP 10
CAST(t.DayDate AS date) [Date],
SUM(cr.MESSAGE_SIZE) [AmtMigrated],
COUNT(cr.MESSAGE_SIZE) [ItemsMigrated]
FROM CROSS_REFERENCE cr
RIGHT JOIN @temp t
ON t.DayDate = cr.MIGRATION_DATE_TIME
GROUP BY CAST(t.DayDate AS date)
ORDER BY CAST(t.DayDate AS date) DESC
Change your join clause to:
ON cr.MIGRATION_DATE_TIME >= t.DayDate AND cr.MIGRATION_DATE_TIME < (t.DayDate+1)
The idea is to allow any indexing on the larger table to still be used, but to make sure that you're accounting for the range of timestamps in that day.
Change @temp to a regular temp table and make DayDate the primary key (so it's indexed).
Add an index to CROSS_REFERENCE.MIGRATION_DATE_TIME if one doesn't already exist. You may also want to look into COLUMNSTORE INDEXes for CROSS_REFERENCE to help aggregate that data more quickly.