January 13, 2009 at 8:59 am
Grant Fritchey (1/13/2009)
Jack Corbett (1/13/2009)
Where I am at right now, part-time contract for 2 more weeks, there is one "minor" system on 2005, the rest are 2000 except for the Quality system which is 7.0 still. Of course this is what happens when you treat your IT staff like dirt, make them the first part of layoffs, and drive them all to find new jobs, even for less pay. One guy took a 50% pay cut to get out.Holy cow. That is some serious desperation to take a 50% pay cut. Has consulting there been entertaining.
Hey, when you are at a 24 x7 X 365 operation with 2 IT staff on call every other week you'll do just about anything.
Since this is where I used to work, funny how basically all the old staff does some contracting here now, it is amusing to say the least. I basically just fight fires. They won't spend to upgrade and I don't fight them on it. I am here because I had to come north to help out my in-laws for a few months and could use the cash to cover expenses so I wasn't interested in any major projects.
They have discussed upgrading the quality system, but that is probably a $50,000+ expenditure between flying me up, having one of the other guys come in to upgrade his custom code, paying the vendor for their time on the minor mods from them, and buying SQL Server and hardware. They are at least 1 major version behind on the software so it will take bringing it up in test, then migrating. Ironically they could have done it over Christmas week had they planned ahead because the mill shut down operations that week.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 23, 2009 at 9:19 am
Jeff Moden (5/28/2007)
Heh... looking at it the wrong way...
Original budget/deadline was wrong because you didn't bid right... Client necessarily changes scale... wants to know why idiots wrote code that wasn't scalable... all future contracts lost because client thinks people who don't have their best interest at heart wrote the code.
And, wanna tell be why the following would take more than 2 minutes? It's still documented , scalable, nasty fast, and it still uses setbased thinking... customer happy, inteviewer happy, boss happy, and my peers don't get the work because I had the for-thought to write code anticipating a change...
--===== Limit the number of rows to be built
SET ROWCOUNT 1000000
--===== Create and populate the table on the fly
SELECT IDENTITY(INT,1,1) AS RowNum,
CAST(NULL AS VARCHAR(10)) AS DesiredResult
INTO #Nums
FROM Master.dbo.SysColumns sc1 WITH (NOLOCK),
Master.dbo.SysColumns sc2 WITH (NOLOCK)
--===== Restore the number of rows to return to normal
-- Note: Can replace with TOP @variable in SQL 2k5
SET ROWCOUNT 0
--===== Produce the desired results according to the
-- requirements
SELECT CASE
WHEN RowNum % 15 = 0 THEN 'BizzBuzz' --Divisible by 15
WHEN RowNum % 3 = 0 THEN 'Bizz' --Divisible by 3
WHEN RowNum % 5 = 0 THEN 'Buzz' --Divisible by 5
ELSE CAST(RowNum AS VARCHAR(10))
END AS DesiredResult
FROM #Nums
ORDER BY RowNum
Ok Jeff. I was doing this challenge with my co-workers today and my CIO smoked your time. Your solution was better in that the CPU utilization was only for the first 3 seconds then it dropped down to about 3% but your solution took 22 seconds. My CIO came up with one that took 12 seconds with the CPU hovering around 20% during the whole run.
So, a bit of a trade-off. Check his out.
declare @x int, @r3 int, @r5 int
select @x = 1
select @r3 = 1
select @r5 = 1
while @x <= 1000000
begin
if (@r3=0 and @r5 = 0)
print 'BizzBuzz'
else if (@r3 = 0)
print 'Bizz'
else if (@r5 = 0)
print 'Buzz'
else
print @x
select @r3 = case when @r3 = 2 then 0 else @r3 + 1 end
select @r5 = case when @r5 = 4 then 0 else @r5 + 1 end
select @x = @x + 1
end
Not having to run the division operation saves a bunch. Very cool.... The people I work with make my head hurt... It really is great to have that challenge though.:hehe:
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 23, 2009 at 10:17 am
Heh... Ok... Game on! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2009 at 10:35 am
Piece of cake... no time to finish it, but you get the idea.
declare @x int, @r3 int, @r5 int
select @x = 1
select @r3 = 1
select @r5 = 1
while @x <= 1000000
begin
if (@r3=0 and @r5 = 0)
print 'BizzBuzz'
else if (@r3 = 0)
print 'Bizz'
else if (@r5 = 0)
print 'Buzz'
else
print @x
select @r3 = case when @r3 = 2 then 0 else @r3 + 1 end
select @r5 = case when @r5 = 4 then 0 else @r5 + 1 end
select @x = @x + 1
end
--13 secs
declare @x int, @r3 int, @r5 int
select @x = 1
select @r3 = 1
select @r5 = 1
while @x <= 1000000
begin
if (@r3=0 and @r5 = 0)
print 'BizzBuzz'
else if (@r3 = 0)
print 'Bizz'
else if (@r5 = 0)
print 'Buzz'
else
print @x
select @r3 = case when @r3 = 2 then 0 else @r3 + 1 end, @r5 = case when @r5 = 4 then 0 else @r5 + 1 end, @x = @x + 1
end
--7 seconds
January 23, 2009 at 10:44 am
Jeff I hope you Proud changed you code a bit and and still using Setbased.
Pumps on my machine 7secs
SELECT TOP 1000000
CASE
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 THEN 'Bizz'
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 5 = 0THEN 'Buzz'
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 AND ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 THEN 'BizzBuzz'
ELSE CAST(ROW_NUMBER() OVER(ORDER BY GETDATE()) as VARCHAR(10))
END
FROM Tally a, Tally b
P.S Ninja's_RGR'us yours takes 16 secs on my machine I would be interested to see what mine does on your machine?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 23, 2009 at 11:01 am
I think you need to take a look at your code, Chris. It has the same test twice on the last part of the Case statement, and it will never reach that leg of the test, because it will stop at step 1.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 11:28 am
I must be missing something on DavidB's solution, because I copy-and-pasted it into Management Studio, and killed it when it hit the 1-minute mark, at which point it had printed up to about halfway through (it was in the upper 500-thousands).
I tested something similar to Jeff's, and it finished in 6 seconds.
Here's what I used (uses 2005+ features):
-- Set output to "text"
;with
Numbers (Number, Mod3, Mod5, Mod15) as
(select top 1000000
cast(row_number() over (order by t1.object_id) as varchar(10)),
row_number() over (order by t1.object_id)%3,
row_number() over (order by t1.object_id)%5,
row_number() over (order by t1.object_id)%15
from sys.all_objects t1
cross join sys.all_objects t2)
select
case
when Mod15 = 0 then 'BizzBuzz'
when Mod5 = 0 then 'Buzz'
when Mod3 = 0 then 'Bizz'
else Number
end
from Numbers;
With a Numbers table already in place, I got the run-time down to 3 seconds:
;with
Numbers (Number, Mod3, Mod5, Mod15) as
(select
cast(number as varchar(10)),
number%3,
number%5,
number%15
from dbo.numbers)
select
case
when Mod15 = 0 then 'BizzBuzz'
when Mod5 = 0 then 'Buzz'
when Mod3 = 0 then 'Bizz'
else Number
end
from Numbers;
That version works in SQL 2000, but assumes you already have a Numbers table that goes from 1 to 1-million.
With generating a temp table first, it took 1 minute and 9 seconds to complete:
if object_id(N'tempdb..#Numbers') is not null
drop table #Numbers
create table #Numbers (
Number int identity primary key,
Placeholder bit);
insert into #Numbers (Placeholder)
select top 1000000 null
from sys.all_objects t1
cross join sys.all_objects t2
-- Set output to text
;with
Numbers (Number, Mod3, Mod5, Mod15) as
(select
cast(number as varchar(10)),
number%3,
number%5,
number%15
from #Numbers)
select
case
when Mod15 = 0 then 'BizzBuzz'
when Mod5 = 0 then 'Buzz'
when Mod3 = 0 then 'Bizz'
else Number
end
from Numbers;
To make this work in SQL 2000, you'd have to use something other than sys.all_objects, but anything that will generate the right number of rows is good enough.
To give it a fair shake, I then ran the loop version to completion, and it took 2 minutes and 18 seconds.
declare @x int, @r3 int, @r5 int
select @x = 1
select @r3 = 1
select @r5 = 1
while @x <= 1000000
begin
if (@r3=0 and @r5 = 0)
print 'BizzBuzz'
else if (@r3 = 0)
print 'Bizz'
else if (@r5 = 0)
print 'Buzz'
else
print @x
select @r3 = case when @r3 = 2 then 0 else @r3 + 1 end
select @r5 = case when @r5 = 4 then 0 else @r5 + 1 end
select @x = @x + 1
end
Since my third solution, creating a temp Numbers table and populating it, then querying that, is pretty much the same as Jeff's, I don't see how it can be defeated by the loop solution since it took less than half the time to run as on my machine.
The one difference that might exist is that I changed the output to Text, instead of returning a million-row recordset through the network and into RAM. But that's fair, because the loop method doesn't do that either.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 11:48 am
Christopher Stobbs (1/23/2009)
Jeff I hope you Proud changed you code a bit and and still using Setbased.Pumps on my machine 7secs
SELECT TOP 1000000
CASE
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 THEN 'Bizz'
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 5 = 0THEN 'Buzz'
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 AND ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 THEN 'BizzBuzz'
ELSE CAST(ROW_NUMBER() OVER(ORDER BY GETDATE()) as VARCHAR(10))
END
FROM Tally a, Tally b
P.S Ninja's_RGR'us yours takes 16 secs on my machine I would be interested to see what mine does on your machine?
It runs in less than 1 second but it takes 15 seconds to get the data on the network and SHOW in the result window!
January 23, 2009 at 12:32 pm
GSquared (1/23/2009)
I must be missing something on DavidB's solution, because I copy-and-pasted it into Management Studio, and killed it when it hit the 1-minute mark, at which point it had printed up to about halfway through (it was in the upper 500-thousands).I tested something similar to Jeff's, and it finished in 6 seconds.
Here's what I used (uses 2005+ features):
-- Set output to "text"
;with
Numbers (Number, Mod3, Mod5, Mod15) as
(select top 1000000
cast(row_number() over (order by t1.object_id) as varchar(10)),
row_number() over (order by t1.object_id)%3,
row_number() over (order by t1.object_id)%5,
row_number() over (order by t1.object_id)%15
from sys.all_objects t1
cross join sys.all_objects t2)
select
case
when Mod15 = 0 then 'BizzBuzz'
when Mod5 = 0 then 'Buzz'
when Mod3 = 0 then 'Bizz'
else Number
end
from Numbers;
GSquared - Wow. That's all I can say. I tried the others following this post and on my machine the above is the tops.
I did copy the code that I pasted (note, not mine, my CIO's) and it ran for me so not sure what the issue was there.
Anyway, very cool! Thanks! 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 23, 2009 at 12:35 pm
The code (the loop) runs. It just takes a lot longer than other solutions, at least on the machines I tested it on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 301 through 309 (of 309 total)
You must be logged in to reply to this topic. Login to reply