September 1, 2014 at 1:58 pm
Hi All
Looking to improve performance of the following code.
It basically generates future days for each dog. So there is a dog table and a day table with every day.
These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.
All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.
My query analyzing isn't the best in the world and would appreciate some advise on how to make the last query as fast as possible.
IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS
(
SELECT CAST('2010-01-01' as date) as [Date]
UNION ALL
SELECT DATEADD(DAY,1,[Date]) as [Date]
FROM [Dates]
WHERE DATEADD(DAY,1,[Date]) < '2020-01-01'
)
INSERT INTO dbo.[AllDates]
SELECT *
FROM Dates
OPTION (MAXRECURSION 0)
IF OBJECT_ID('dbo.[Dogs]', 'U') IS NOT NULL
DROP TABLE dbo.[Dogs]
CREATE TABLE dbo.[Dogs] (
[DogId] INT IDENTITY(1,1) not null PRIMARY KEY,
[Born] date not null,
[Died] date null
)
CREATE INDEX IX_DoggieDaysJoin on dbo.[Dogs](DogId,Born,[Died])
;WITH DogCTE AS
(
SELECT 1 as [Num]
UNION ALL
SELECT [Num] + 1 as [Num]
FROM DogCTE
WHERE [Num] < 30000
)
INSERT INTO dbo.Dogs
SELECT '2010-01-01' as [Born],
'2019-01-01' as [Died]
FROM DogCTE
OPTION (MAXRECURSION 0)
IF OBJECT_ID('dbo.[DoggieDays]', 'U') IS NOT NULL
DROP TABLE dbo.DoggieDays
CREATE TABLE dbo.DoggieDays (
[Date] DATE not null,
[DogId] INT not null,
CONSTRAINT PK_DoggieDays PRIMARY KEY ([Date],[DogId])
)
--CREATE INDEX IX_DoggieDaysJoin2 on dbo.DoggieDays([DogId])
DECLARE @START1 as DATETIME
DECLARE @END1 AS DATETIME
DECLARE @START2 as DATETIME
DECLARE @END2 AS DATETIME
SET @START1 = GETDATE()
INSERT INTO dbo.DoggieDays([Date],DogId)
SELECT
da.[Date],
do.DogId
FROM dbo.[Dogs] do
JOIN dbo.AllDates da
on da.[Date] between do.Born and do.Died
and da.[Date] between '2012-01-01' and '2015-01-01'
LEFT JOIN dbo.DoggieDays dd
on da.[Date] = dd.[Date]
and do.DogId = dd.DogId
WHERE dd.DogId is null
SET @END1 = GETDATE()
SET @START2 = GETDATE()
INSERT INTO dbo.DoggieDays([Date],DogId)
SELECT
da.[Date],
do.DogId
FROM dbo.[Dogs] do
JOIN dbo.AllDates da
on da.[Date] between do.Born and do.Died
and da.[Date] between '2012-01-01' and '2015-01-01'
LEFT JOIN dbo.DoggieDays dd
on da.[Date] = dd.[Date]
and do.DogId = dd.DogId
WHERE dd.DogId is null
SET @END2 = GETDATE()
SELECT datediff(SECOND,@START1,@END1) AS [RUN1],
datediff(SECOND,@START2,@END2) AS [RUN2]
September 1, 2014 at 2:28 pm
Quick thought, redesign the recursive iteration approach, it is a real performance killer, use a tally table instead.
😎
September 1, 2014 at 2:58 pm
Hi thanks for the advise but its only for setting up this example. In my live system the data is already setup. It's only the last 2 queries i am tuning.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply