The T-SQL Quiz

Question

The T-SQL Quiz

Viewing 15 posts - 151 through 165 (of 309 total)

You must be logged in to reply to this topic. Login to reply

igor bekelman SSC Enthusiast Points: 168 More actions · Answer 1

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

igor bekelman SSC Enthusiast Points: 168 More actions · Answer 2

:"By the way, that's quite a laptop you have... what make is it? "

Toshiba Tecra T5, this config is about $3.500

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

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)

igor bekelman SSC Enthusiast Points: 168 More actions · Answer 7

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

------------------------------------------------------------------------------

Andrew Ramka Newbie Points: 3 More actions · Answer 8

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

igor bekelman SSC Enthusiast Points: 168 More actions · Answer 9

igor bekelman

SSC Enthusiast

Points: 168

August 2, 2007 at 5:31 pm

#724355

Andrew Ramka

Poor logic, poor coding..

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

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)

Adam Machanic SSCoach Points: 15370 More actions · Answer 11

There is a little-known control-of-flow statement called "else" ...

--
Adam Machanic
whoisactive

igor bekelman SSC Enthusiast Points: 168 More actions · Answer 12

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

Sergiy SSC Guru Points: 110208 More actions · Answer 13

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

igor bekelman SSC Enthusiast Points: 168 More actions · Answer 14

igor bekelman

SSC Enthusiast

Points: 168

August 3, 2007 at 3:46 am

#724439

replace != concat. Period.

Sergiy SSC Guru Points: 110208 More actions · Answer 15

Agree.

But replace with what?

'Bizz', 'Buzz' or 'BizzBuzz'?

All three options are valid!

_____________
Code for TallyGenerator