March 14, 2009 at 2:31 pm
Bob Hovious (3/14/2009)
Jeff, two things.1. Some code is missing from your first example. But I presume you were going to cross join syscolumns to itself. I already sent Rob similar code to generate a million rows fast.
2. Our production databases run almost 24-7. I see lots of transactions that fall just before midnight. I'm not really getting how your WHERE clause in the second example would help me isolate the very last row.
1. It would be better if you posted such code here instead of PM'ing it. That way, all may learn from it.
Still 1. Scroll down. 😉
2. Ahhhh... my bad... my very bad. I don't know why, but somehow I surmised that the rows we all marked at the 30 minute mark. Obviously, that won't work here. My sincere appologies.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2009 at 8:11 pm
Sorry I didn't post my code. There's nothing secret about it, but Rob requested it in PM and I respected his request. I was just about to submit it as a script, since the only sample data generation script I could find published here was based on a while loop. When I finish typing it up, I'll run it by you if that's okay. You might get a laugh out of the first few paragraphs.
By the way, I tried scrolling earlier and couldn't. I come back now and can get the last three lines. My bad.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 14, 2009 at 9:32 pm
Bob Hovious (3/14/2009)
Sorry I didn't post my code. There's nothing secret about it, but Rob requested it in PM and I respected his request. I was just about to submit it as a script, since the only sample data generation script I could find published here was based on a while loop. When I finish typing it up, I'll run it by you if that's okay. You might get a laugh out of the first few paragraphs.By the way, I tried scrolling earlier and couldn't. I come back now and can get the last three lines. My bad.
No problem... I just never honor requests for code by PM because it deprives the good folks trying to silently follow the thread to learn something. Heh... I take that back... I will honor a request for code by PM if the request is wrapped in the right kind of paper with the right kind of numbers on it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2009 at 10:51 pm
Rob Fisk (3/13/2009)[hr...Tally table example would be great. PM me since we have already crowded this topic somewhat...
Please do not derail threads by PM'ing stuff like this. Crowded threads are the Goal of this site.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 15, 2009 at 4:59 pm
Here is the code I should have posted earlier.
-----------------------------------------------------------------------------------------------
-- code to generate temporary sample data table for testing purposes
-----------------------------------------------------------------------------------------------
declare @words table (wordID int primary key, word varchar(20))
insert into @words
select 1, 'apple' union all
select 2, 'benediction' union all
select 3, 'cat' union all
select 4, 'dog' union all
select 5, 'effervescent' union all
select 6, 'fox' union all
select 7, 'grandiosity' union all
select 8, 'harmony' union all
select 9, 'indigo' union all
select 10, 'jackal'
;with Tally (N) AS
(SELECT TOP 1000000 ROW_NUMBER() over (order by sc1.id)
FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2)
,randomNess as
( select isnull(N,-1000000000) as rowID
,ABS(CHECKSUM(NEWID())) % 101 as integerColumn
,cast(ABS(CHECKSUM(NEWID())) % 10000/100.00 as numeric(5,2)) as numericColumn
,dateadd(dd, datediff(dd,0, getdate()),0) - ABS(CHECKSUM(NEWID())) % 1000 as startDate
from Tally
)
select rowID, integerColumn, numericColumn,startDate
,startDate + ABS(CHECKSUM(NEWID())) % 100 as endDate
,word
,cast(UPPER(substring(word,3,1)) as char(1)) as category
into #temp
from randomness
-- join @words on wordID = 10 - right(integerColumn,1)
cross apply (select word from @words where wordID = 10 - right(integerColumn,1)) as ts;
-- AFTERWARDS, build clustered index, and secondary indexes, if any
ALTER TABLE #temp
ADD CONSTRAINT PK_#TEMP PRIMARY KEY CLUSTERED ( rowID ) WITH (FILLFACTOR = 100)
-- CREATE UNIQUE NONCLUSTERED INDEX [IX_#TEMP_startDate] ON #temp( startDate )
select top 100 *
from #temp
order by rowID desc
--drop table #temp
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2009 at 3:33 pm
In this example, with the Tally table I would now generate my test data using
INSERT @myTable
SELECTDATEADD(mi,30 * N, @startDate),'ABC',N+50,N
FROM dbo.Tally
WHERE N < 365 * 96 --assuming 2 entries a day for roughly 2 years
To replace the while loop:
WHILE @startDate < '02 Jan 2010'
BEGIN
INSERT @myTable
VALUES(@startDate,'ABC',@counter+50,@counter)
SET @startDate = DATEADD(mi,30,@startDate)
SET @counter = @counter +1
END
After reading around a bit I have opted for a tally table starting at 0
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
March 16, 2009 at 3:53 pm
For convenience, you can also just calculate up to a specified stop date.
(You could just say
WHERE DATEADD(mi,30 * N, @startDate) < '1/1/2009'
but the code below runs a hair faster
)
declare @startDate datetime
declare @stopDate datetime
declare @halfHours int
set @startDate = '1/1/2007'
set @stopDate = '1/1/2009'
set @halfhours = 2 * datediff(hh,@startDate,@stopDate)
SELECT DATEADD(mi,30 * N, @startDate) as xdate ,'ABC' as xstring,N+50 as xAmt, N
FROM dbo.Tally
WHERE N < @halfHours
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 4:58 pm
Bob, you are an example to us all with your attention to detail and eeking the last milisecond per year out of database solutions.
I've considered myself pretty good at SQL and have been working with it for years self taught but I am (re)learning a lot from the trends that your, and a few others', solutions tend to follow. Particularly the optimised way of doing things but also the quick tricks for calculating ranges that are obvious once you see them but easily overlooked due to being stuck in our ways.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
March 18, 2009 at 5:50 pm
Rob, I thank you for the compliment, but I must redirect the credit where it truly lies.
I thought I was fairly good at SQL until I started spending a good amount of time reading and then participating in forums here at SSC. I've learned solid techniques and a lot of perspective from a great many different people here. Jeff Moden in particular really forced me to sharpen my attention to detail.
All I do is steal good code and try to emulate the people I admire.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply