The T-SQL Quiz
-
I done this comparisson (see my posts before) . The result is practically the same. Again, there is no magic
Internally set based solution are still loops (we have no set based processor commands that a ready to accomodate this case, AFAIK).The difference, IMHO, is how loop is compiled. As you can see, ratio is the same for 1 and 100 millions, which means that one loop operation costs 6 times more for explicit T-SQL loops compared to internal ones.
BTW, it was 100 millions rows
-
:"By the way, that's quite a laptop you have... what make is it? "
Toshiba Tecra T5, this config is about $3.500
-
Heh... you and I are on the same page... that's the "magic" I was referring to... sorry I misread your previous post on the subject.
Yep, made the correction on the 100 Meg rows... old eyes thought it was just 10 Meg.
And, thanks for the info on the laptop.
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Igor,
your test is still not correct.
No, not true, it's still not complete.
You forget - it's not a single user application, it's a server.
So, open 10 windows in QA and start 10 loops simulteneously.
Then start 10 set based solutions in other 10 windows.
It would be nice to have Performance Monitor running to see which way every approach affects the server.
What about magic now?
_____________
Code for TallyGenerator -
Hey,
I saw the test you'd outlined so I tried it on my laptop:
WHILE LOOP TEST...
1000000 The final count (just to show work was done)
970 Duration in milliseconds
------------------------------------------------------------------------------
SET BASED TEST...
1000000 The final count (just to show work was done)
313 Duration in milliseconds
------------------------------------------------------------------------------
It's a Core 2 CPU T7600 @2.33GHz
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Outstanding... thanks Grant!
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Sergiy, I ran 10 Jeff's scripts simultaneously for 100 million rows
As you can see the ratio is close to what I got running single script
WHILE LOOP TEST...
100000000 The final count (just to show work was done)
180746 Duration in milliseconds
------------------------------------------------------------------------------
SET BASED TEST...
100000000 The final count (just to show work was done)
25893 Duration in milliseconds
------------------------------------------------------------------------------
-
Im not sure if any of the solutions are correct!
Take 15 for example, 15 is evenly divisible by 3,5 and (3 and 5). The question states
For each number evenly divisible by 3, substitute 'Bizz' For each number evenly divisible by 5, substitute 'Buzz' For each number divisible by both substitute 'BizzBuzz'
15 is evenly divisible by 3 so replace with Bizz
15 is evenly divisible by 5 so replace with Buzz
15 is evenly divisible by (3 and 5) so replace with BizzBuzz
so every num evenly divisible by 15 should actually be replaced by Bizz Buzz BizzBuzz.
I cant see where the question implies if else logic which is the path taken for most of the solutions so what about this solution, which is the solution I spit out in about 8 mins
Declare
@i as int;
Set
@i = 1;
While
@i <101
begin
if (@i % 3) = 0
print 'Bizz'
if (@i%5) = 0
print 'Buzz'
if ((@i%3) = 0) and ((@i%5) = 0)
print 'BizzBuzz'
if (Not((@i%3) = 0)) and (Not((@i%5)=0))
print @i
Set @i= @i+1
End
-
-
Yeowch! When you jab someone in the eye with a sharp stick, you should at least tell them why
--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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
There is a little-known control-of-flow statement called "else" ...
--
Adam Machanic
whoisactive -
OK
logic
"15 is evenly divisible by 3 so replace with Bizz
15 is evenly divisible by 5 so replace with Buzz
15 is evenly divisible by (3 and 5) so replace with BizzBuzz
so every num evenly divisible by 15 should actually be replaced by Bizz Buzz BizzBuzz."
a) applying replace 3 times
1. 15 replace with Bizz = Bizz
2. Bizz (former 15) replace with Buzz = Buzz
3. Buzz replace with BizzBuzz= BizzBuzz
b) by his version of replace
The result should "15 Bizz Buzz BizzBuzz"
c) BTW, where from did we get spaces?
coding
a) if ((@i%3) = 0) and ((@i%5) = 0) - again?
b) case?
c) Let me stop
-
I like this logic!
Logic is as poor as logic of original quiz.
What was asked?
"each number evenly divisible by 3, substitute 'Bizz' For each number evenly divisible by 5, substitute 'Buzz' For each number divisible by both substitute 'BizzBuzz'"
So, 15 is evenly divisible by 3 - must be replaced by 'Bizz'
And, 15 is evenly divisible by 5 - must be replaced by 'Buzz'
And, 15 is evenly divisible by both - must be replaced by 'BizzBuzz'
Where do you see - "divisible by 3 but not divisible by 5"?
Conclusion - question is crap.
Shame on all of us - good programmers should see such logic flaws when they just appear on horizon!
I think this guy is not the easiest guy to work with, but his project must be very well defined, and logic must be well esteblished.
Experience proves that proper task definitions are 70% of success, especially when we talking about databases.
_____________
Code for TallyGenerator -
replace != concat. Period.
-
Agree.
But replace with what?
'Bizz', 'Buzz' or 'BizzBuzz'?
All three options are valid!
_____________
Code for TallyGenerator
Viewing 15 posts - 151 through 165 (of 309 total)
You must be logged in to reply to this topic. Login to reply