Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2728293031

The T-SQL Quiz Expand / Collapse
Author
Message
Posted Tuesday, January 13, 2009 8:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 10,342, Visits: 13,351
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #635532
Posted Friday, January 23, 2009 9:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:07 PM
Points: 2,049, Visits: 3,592
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.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #642561
Posted Friday, January 23, 2009 10:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 35,584, Visits: 32,174
Heh... Ok... Game on! ;)

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #642634
Posted Friday, January 23, 2009 10:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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

Post #642646
Posted Friday, January 23, 2009 10:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,550, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #642649
Posted Friday, January 23, 2009 11:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #642660
Posted Friday, January 23, 2009 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #642676
Posted Friday, January 23, 2009 11:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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!
Post #642682
Posted Friday, January 23, 2009 12:32 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:07 PM
Points: 2,049, Visits: 3,592
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! :D


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #642703
Posted Friday, January 23, 2009 12:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #642707
« Prev Topic | Next Topic »

Add to briefcase «««2728293031

Permissions Expand / Collapse