Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generating Missing Dates and Numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, February 6, 2008 7:20 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, December 24, 2013 4:42 AM Points: 460, Visits: 2,523
 GSquared,I agree that a number table should be the first choice. The approach presented in this article is useful for cases when you do not want to use a number table.Thanks for sharing the performance data. That was really helpful. .
Post #452188
 Posted Wednesday, February 6, 2008 7:26 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, December 24, 2013 4:42 AM Points: 460, Visits: 2,523
 Great tip Jeff. Thank you for sharing this. I remember this discussion is coming up for the second time. I appreciate your comments and am glad to learn something new everytime I read your posts :) .
Post #452194
 Posted Wednesday, February 6, 2008 7:27 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 9:28 AM Points: 151, Visits: 210
 Agreed
Post #452195
 Posted Wednesday, February 6, 2008 7:30 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 9:28 AM Points: 151, Visits: 210
 Superaltive article. Thanks.
Post #452197
 Posted Wednesday, February 6, 2008 7:33 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:19 PM Points: 42,047, Visits: 39,434
 GSquared (2/6/2008)Yet again, I have to say this is clever, but a Numbers table is better.Yes, if you for some reason need to create a list of sequential numbers on the fly, this CTE is probably the fastest means of doing so. It certainly is at least A fast means of doing so. But having an actual Numbers table, with a clustered index on it, is MUCH better.(Yes, this is the third or fourth time I've written on this exact subject. But for whatever reason, it keeps coming up.)Spot on, G... didn't see your post as I was writting mine. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #452202
 Posted Wednesday, February 6, 2008 7:53 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 You can also find missing dates with a Numbers table more easily than is outlined in the article.To set up the test:`create table MissingDates (Date datetime primary key)goinsert into dbo.missingdates(date)select dateadd(second, subtime.number, subdate.date)from (select dateadd(day, number, '1/1/08') as Date from common.dbo.Numbers where number <= 100) SubDatecross join (select number from common.dbo.BigNumbers where number <= (3600 * 24) - 1) SubTimegodelete from dbo.missingdateswhere date between '1/12/08' and '1/13/08'godelete from dbo.missingdateswhere date between '1/30/08' and '1/31/08'`This time, I tried my usual Numbers table method:`select dateadd(day, number, '1/1/08')from common.dbo.numbersleft outer join dbo.missingdates on date >= dateadd(day, number, '1/1/08') and date < dateadd(day, number + 1, '1/1/08')where date is nullnumber between 0 and 31`------------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(2 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'MissingDates'. Scan count 32, logical reads 5638, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Numbers'. Scan count 3, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 688 ms, elapsed time = 687 ms.------------------Then I tried the method recommended in the article:`SELECT CAST('2008-01-01' AS DATETIME) + Number-1 FROM dbo.GetNumbers(1, 30)where CAST('2008-01-01' AS DATETIME) + Number-1 not in (select cast(convert(varchar(100), date, 101) as datetime) from dbo.missingdates)`(The cast-convert is necessary because without that, it will simply detect missing midnights, not whole missing days.)I killed that process after it had been running for 3 minutes. Not sure how long it would have taken in all.Converted it to:`select dateadd(day, number, '1/1/08')from dbo.getnumbers(1,31)left outer join dbo.missingdates on date >= dateadd(day, number-1, '1/1/08') and date < dateadd(day, number, '1/1/08')where date is null`Which is pretty much identical to the query used with the Numbers table.----------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(2 row(s) affected)Table '#28D10FF3'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'MissingDates'. Scan count 31, logical reads 5418, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 673 ms, elapsed time = 366 ms.-----------------This time, the total elapsed time was shorter, but the CPU time was nearly identical. One less scan count on the MissingDates table, and the server was able to split the process across more than 1 CPU (the computer I'm testing this on is a dual-core Pentium D).Some advantage to the CTE, but in a loaded up server, it won't make much difference. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #452217
 Posted Wednesday, February 6, 2008 10:25 AM
 Right there with Babe Group: General Forum Members Last Login: Monday, June 1, 2015 1:22 PM Points: 776, Visits: 1,207
 Weird, your CTE code took 16 seconds (twice) on my SQL Serverthat is not VERY VERY fast. Anyway, we don't have the need for it, I was just curious16 seconds on a SQL Server on VMWare, and 16 seconds on a physical SQL Server (8 cpu, 24GB RAM)`WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT N FROM NUM WHERE N <= 1000000;` SQLServerNewbieMCITP: Database Administrator SQL Server 2005
Post #452350
 Posted Wednesday, February 6, 2008 10:26 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, June 5, 2012 12:03 PM Points: 117, Visits: 163
 What do folks think about adding directly to a datetime and allowing implicit conversions to take place?`CREATE FUNCTION dbo.DateRange( @start DATETIME, @length INT ) RETURNS @range TABLE ( dateOf datetime PRIMARY KEY )ASBEGIN-- Populate the output table of dates using a number table INSERT INTO @range ( dateOf ) SELECT @start + n AS dateOf FROM ( SELECT (n1.n +n10.n +n100.n +n1000.n) AS n FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS n1 CROSS JOIN (SELECT 0 AS n UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS n10 CROSS JOIN (SELECT 0 AS n UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900) AS n100 CROSS JOIN (SELECT 0 AS n UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000 UNION SELECT 4000 UNION SELECT 5000 UNION SELECT 6000 UNION SELECT 7000 UNION SELECT 8000 UNION SELECT 9000) AS n1000 WHERE (n1.n +n10.n +n100.n +n1000.n) BETWEEN 0 AND @length - 1 ) AS numbers ORDER BY n ASC; RETURN;END`
Post #452352
 Posted Wednesday, February 6, 2008 10:37 AM
 Grasshopper Group: General Forum Members Last Login: Friday, April 20, 2012 8:30 AM Points: 19, Visits: 25
 I prefer this simple and elequent way to generate numbers.I doesn't generate unnecessary numbers and is easy to understandCREATE FUNCTION dbo.GetSequence( @Start BIGINT, @End BIGINT, @Increment BIGINT)RETURNS @ret TABLE(Number BIGINT)ASBEGIN WITH seq(num) as ( select @Start union all select num + @Increment from seq where num + @Increment <= @End ) INSERT INTO @ret(Number) Select * From SeqEND
Post #452357
 Posted Wednesday, February 6, 2008 1:48 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 6:26 AM Points: 7,504, Visits: 17,941
 Jeff/Jacob:first - Jacob - very nice. Good solid method for handling a common request.Jeff - I'll chalk this up to being the forever contrarian... But the comparison gets more interesting when you "right-size" the Itzik method. Notice what one tiny little change does (since we KNOW how many results we want....)`SET STATISTICS TIME ONGODECLARE @BitBucket INT--=============================================================================PRINT REPLICATE('=',100)PRINT 'Itzek''s method:';WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L0 AS B),--131072 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT @BitBucket = N FROM NUM WHERE N <= 1000000;--=============================================================================PRINT REPLICATE('=',100)PRINT 'Jeff Moden''s Method'; WITH cTally AS(----------------------------------------------------------------------------- --==== High performance CTE equivalent of a Tally or Numbers table SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY t1.ID) AS N FROM Master.sys.SysColumns t1 CROSS JOIN Master.sys.SysColumns t2)-----------------------------------------------------------------------------SELECT @BitBucket = N FROM cTally --Do your outer join with table being checked herePRINT REPLICATE('=',100)`Can you spot the difference (it's in bold....hehe)? Of course - we could debate why we're fighting so hard to reclaim 200ms or less, but - what's the fun in that? ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #452433

 Permissions