CTE Query taking too long

Richard Berry100

Hi Below query is taking 10 seconds to return 90 rows. Using cte to get the dates of the last 90 days, then summing the production for each day (Alloocated is production qty) and DateCreated is a DateTime value that the units were produced. So the output is the date of the last 90 days, and the total production for each day Can anyone point out what I am doing wrong?

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)
SELECT convert(nvarchar(10),DateValue,121) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(nvarchar(10),DateCreated,121) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(nvarchar(10),DateCreated,121))
as trk on trk.TrkDate = convert(nvarchar(10),DateValue,121)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Mycroft Holmes

I don't think you can blame the CTE, I would look into all the convert dates, are you storing your dates as strings in vektronix? Try changing all the string joins to date joins (eliminating the time component). Try creating a temp table with the 90 dates instead of the CTE if that makes no difference. I use CTEs only as a last resort and this does not require one.

Never underestimate the power of human stupidity RAH

Richard Berry100

Hi Mycroft. The dates are stored as dates in the database. How can I do the joins eliminating the time component because that is the reason I did all that converting to nvarchar(10) was to eliminate the time part of the dates

Mycroft Holmes

If you are using 2008 then cast/convert to DATE instead of DATETIME

DECLARE
@D DATETIME

SET @D = GETDATE()

SELECT @D

SELECT CONVERT(DATE,@D)

Never underestimate the power of human stupidity RAH

Richard Berry100

Thanks Mycroft = a bit faster but still slow for a mere 90 rows. Bellow returns 10000 rows in under as second - so the problem must be in the second part

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-10000,GETDATE())
)
select convert(date,DateValue) from Dates
OPTION (MAXRECURSION 32747)

This executes in under a second (This is basically the output I want, but I also want days where there was zero production hence the CTE)

select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated)

With the change to date as opposed to a datestring nvarchar(10) as you suggested, below has reduced from about 17 second to 5 seconds. But to be that seems terribly slow for 90 rows?

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)

SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated))
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Jorgen Andersson

As the dateconversion seems to be the problem probably together with a bad plan I'd recommend that you minimize the number of conversions:

WITH Datetimes AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Datetimes
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)
,dates AS
(
SELECT DISTINCT convert(date,datevalue) AS ProdDate
FROM Datetimes
)
,trk AS
(
SELECT convert(date,DateCreated) as TrkDate
,allocated
from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
)
,trkgrouped as
(
SELECT trkdate
,SUM(allocated) as Tot
FROM trk
GROUP BY trkdate
)

SELECT ProdDate
,coalesce(Tot,0)as ProdQty
FROM Dates D
left JOIN trkgrouped trk on trk.TrkDate = d.ProdDate
order by ProdDate desc
OPTION (MAXRECURSION 32747)

The purpose of the DISTINCT is to force the DatesCTE to be materialized. But if you really want to boost the performance of the query you need to add convert(date,DateCreated) to vektronix.[dbo].[Tracking] as a calculated column and put an index on it.

The reasonable man adapts himself to the world; the unreasonable man adapts the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

Richard Berry100

Thanks Jorgen Unfortunately it not my database to add columns. I tried your query, but it was also slow, however that got me thinking more about the data I am querying. The vektronix table is actually quite large already after only a few months of production (about 8 rows for each unit produced) so I tried limiting the rows from vektronix in the where clause - that worked!

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)

SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '1' and DateCreated >= dateadd(d,-90,GETDATE())
group by convert(date,DateCreated))
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue,trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Thanks for your help!