Challenge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

Challenge

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 13:23:47

This got me thinking. What is the fastest way to build <edit>this</edit> tally table?

create table tally(n int primary key)

Populate this table with 10K ints from 0 to 9999. Points given for a) script execution speed and b)fewest keystrokes. *

My solution so far...


insert tally
select a.n + 1000 * b.n
from (
    select a.n + 100 * b.n as n
    from (select a.n + 10 * b.n as n
          from (select a.n + b.n as n
                from (select 0 as n union select 1 union select 2 union select 3 union select 4 ) as a,
                     (select 0 as n union select 5) as b) a,
               (select a.n + b.n as n
                from (select 0 as n union select 1 union select 2 union select 3 union select 4 ) as a,
                     (select 0 as n union select 5) as b) b) a,
         (select a.n + b.n as n
          from (select 0 as n union select 1 union select 2 union select 3 union select 4 ) as a,
               (select 0 as n union select 5) as b) b) a,
 (select a.n + b.n as n
  from (select 0 as n union select 1 union select 2 union select 3 union select 4 ) as a,
       (select 0 as n union select 5) as b) b

I've got that running in 140 ms and I count that as 710 keystrokes (removing the pretty formatting).

So what do you have?

EDIT: *: Favoring speed ...
Jay White
{0}

Edited by - Page47 on 04/25/2003 15:28:21

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-04-25 : 13:49:42

I don't have a better solution, but I will start taking side-bets. My bet is that IF Jay is beaten on this, it will come from the Yak-Finder General (Arnold Fribble).

(Note, I'm not taking bets on whether Jay will be beaten, but only on IF he is, who it will be by.)

------------------------------------------------------
The more you know, the more you know you don't know.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 14:17:20

How about:


CREATE TABLE Tally (x bit null, n int not null IDENTITY (0, 1) PRIMARY KEY)
GO

INSERT INTO Tally (x) SELECT TOP 1000 1 FROM sysColumns a, sysColumns b
GO
SELECT * FROM Tally
GO

DROP TABLE Tally
GO

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 14:35:32

Well, Brett, nice effort.

First I had to modify you post so it would work with my ddl (that makes me grumpy...) and that was 10K, not 1K ....


create table tally_brett(x bit null, n int not null identity(0,1) primary key)
insert into tally_brett(x) select top 10000 1 from syscolumns a, syscolumns b
insert tally(n) select n from tally_brett

And, sorry chap, but my testing shows that takes, on average, 100ms longer than my original try. So you lose points on speed, earn points on keystrokes ... but then loose major points for IDENTITY :)....

Jay White
{0}

Edited by - Page47 on 04/25/2003 14:37:05

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 14:41:29

quote:

but then loose major points for IDENTITY :)....

Why?

Also I'm assuming I came in it at 240ms

And what happens when you go to 100,000?

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 14:46:03

quote:
Why?

... I'm just being silly

... I'm not an identity fan ...

quote:
And what happens when you go to 100,000?

... That's not my challenge ... but if you're curious, I'm sure you could modify my approach to go to 100K then compare the two and let us know ...

Jay White
{0}

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-25 : 14:46:21

quote:

but then loose major points for IDENTITY :)....

Why?

Brett
I think You and I are the only ones that use IDENTITY regularly. LOL

Correction Gess its only me

Jim

Edited by - JimL on 04/25/2003 14:49:36

dsdeming

479 Posts

Posted - 2003-04-25 : 14:53:07

OK, I admit it. I'm using an identity, but the results are encouraging:

CREATE TABLE Tally2
(
Sequence int IDENTITY( 1, 1 ) PRIMARY KEY,
PlaceHolder bit NULL
)
SET ROWCOUNT 10000
-- I chose master..sysindexes for the cross join because at 102 rows it was the closest I could
-- come to the square root of 10K
INSERT INTO Tally2( PlaceHolder ) SELECT 1 FROM master..sysindexes a CROSS JOIN master..sysindexes b

After running both my code and the benchmark code through 10 iterations each, I got the following results:

BenchmarkResult MyResult
--------------- -----------
280 250
216 153
190 156
186 140
203 160
200 140
190 153
203 156
186 156
203 156

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 15:00:10

NO NO NO ... PLAY BY MY RULES ...

You have to insert into [tally] as I have created it!

So, dsdeming, add a "insert tally select n from tally2" into your script then time the whole thing ...

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 15:02:09

quote:

I think You and I are the only ones that use IDENTITY regularly. LOL

Nope...more likely an anti-IDENTITY fan..prefer natural keys fro stuff.. but that's the fastest way.

And

quote:

but if you're curious, I'm sure you could modify my approach to go to 100K then compare the two and let us know

That's my point. While clever, the adjustment of your code to go to 100,000 or then un to 1,000,000 or 10,000,000 requires more than mine.

I just have to repeat the tables once twice or three time to cartesian up, and change my top. Your's would be a bit more complex to alter.

Speed of development should be part of the equation, as all things are trade offs anyway (and we're talking about 100ms).

Brett

8-)

EDIT: OK it's your game.

EDIT2: But the question was phrased:

quote:

What is the fastest way to build a tally table?

Edited by - x002548 on 04/25/2003 15:04:15

Edited by - x002548 on 04/25/2003 15:05:46

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-25 : 15:02:16

insert tally
select z from
(select z=a+b+c+d+e+f+g+h+i+j+k+l+m+n
from
(select a=0 union select 1) as a ,
(select b=0 union select 2) as b ,
(select c=0 union select 4) as c ,
(select d=0 union select 8) as d ,
(select e=0 union select 16) as e ,
(select f=0 union select 32) as f ,
(select g=0 union select 64) as g ,
(select h=0 union select 128) as h ,
(select i=0 union select 256) as i ,
(select j=0 union select 512) as j ,
(select k=0 union select 1024) as k,
(select l=0 union select 2048) as l ,
(select m=0 union select 4096) as m ,
(select n=0 union select 8192) as n
) as a
where z < 10000

Think it's less keystrokes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-25 : 15:03:28

more keystrokes, but according to execution plan is faster:

insert tally
select a.n + 1000 * b.n
from (
select a.n + 100 * b.n as n
from (select a.n + 10 * b.n as n
from (select a.n + b.n as n
from (select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 ) as a,
(select 0 as n union all select 5) as b) a,
(select a.n + b.n as n
from (select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 ) as a,
(select 0 as n union all select 5) as b) b) a,
(select a.n + b.n as n
from (select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 ) as a,
(select 0 as n union all select 5) as b) b) a,
(select a.n + b.n as n
from (select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 ) as a,
(select 0 as n union all select 5) as b) b

yep ... just replaced UNION with UNION ALL ... the most obvious enhancement !!

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-25 : 15:05:32

Don't understand.
Where's the cross join

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-25 : 15:08:10

quote:

insert tally
select z from
(select z=a+b+c+d+e+f+g+h+i+j+k+l+m+n
from
(select a=0 union select 1) as a ,
(select b=0 union select 2) as b ,
(select c=0 union select 4) as c ,
(select d=0 union select 8) as d ,
(select e=0 union select 16) as e ,
(select f=0 union select 32) as f ,
(select g=0 union select 64) as g ,
(select h=0 union select 128) as h ,
(select i=0 union select 256) as i ,
(select j=0 union select 512) as j ,
(select k=0 union select 1024) as k,
(select l=0 union select 2048) as l ,
(select m=0 union select 4096) as m ,
(select n=0 union select 8192) as n
) as a
where z < 10000

Think it's less keystrokes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nigel -- I definitely LIKE that one ..... I thought about powers of 2 myself but that is definitely a nice one.

- Jeff

dsdeming

479 Posts

Posted - 2003-04-25 : 15:11:57

quote:
So, dsdeming, add a "insert tally select n from tally2" into your script then time the whole thing ...

Actually what I did in my code was ALTER TABLE to drop the offending column.

All I was attempting to demonstrate was that a CROSS JOIN with an IDENTITY column and SET ROWCOUNT is faster and more scalable in case the number of desired rows changes. Apparently I failed.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 15:12:51

quote:

insert tally
select z from
(select z=a+b+c+d+e+f+g+h+i+j+k+l+m+n
from
(select a=0 union select 1) as a ,
(select b=0 union select 2) as b ,
(select c=0 union select 4) as c ,
(select d=0 union select 8) as d ,
(select e=0 union select 16) as e ,
(select f=0 union select 32) as f ,
(select g=0 union select 64) as g ,
(select h=0 union select 128) as h ,
(select i=0 union select 256) as i ,
(select j=0 union select 512) as j ,
(select k=0 union select 1024) as k,
(select l=0 union select 2048) as l ,
(select m=0 union select 4096) as m ,
(select n=0 union select 8192) as n
) as a
where z < 10000

That's just damn clever...but it took 7 seconds to run in my QA

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 15:18:41

quote:

yep ... just replaced UNION with UNION ALL ... the most obvious enhancement !!

Now your talkin!!! ... I've been bested by Mr. Cross Join!! nice work ....

Brett says: "Yeah, but my solution solves a different problem better ..."
I say: "Okay, great, thanks for playing. You still lose."

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 15:26:47

quote:

Brett says: "Yeah, but my solution solves a different problem better ..."

Please quote me, but don't put words in my mouth.

And it's your ball...you can take it home now.

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 15:27:05

nr, I'm timing yours as being about 20ms slower than mine.
jeff, I'm timing yours as being about 20ms faster than mine.
dsdeming, Alter what? [Tally]? I didn't see any alters?
Brett, if nr's code too 7 seconds, you really should upgrade your machine

.

EDIT: Aw, come on Brett, I'm just playin with you. Maybe next season we'll catch up at Jay Peak and run the glades ... it's all good.

Jay White
{0}

Edited by - Page47 on 04/25/2003 15:30:06

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-25 : 15:39:48

This should be faster - and faster still with union all.
Might even be faster without the where clause.
Be worth trying 0-9 in the derived tables so that a where isn't needed.

insert #tally
select z from
(select z=a+b+c+d+e+f+g
from
(select a=0 union select 1 union select 2 union select 3) as a ,
(select b=0 union select 4 union select 8 union select 12) as b ,
(select c=0 union select 16 union select 32 union select 48) as c ,
(select d=0 union select 64 union select 128 union select 192) as d ,
(select e=0 union select 256 union select 512 union select 768) as e ,
(select f=0 union select 1024 union select 2048 union select 3072) as f ,
(select g=0 union select 4096 union select 8192) as g
) as a
where z < 10000

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 15:43:12

Just ran Nigel's code and it flew....wonder why, I'm the only one on the box..Network traffic?

or Jay, we could do some mud jumping at the big K, shouldn't be to crowded, and could get a round of golf in after the lifts close.

Life is Good

Time to start the weekend...happy hour is a callin

Brett

8-)

EDIT: Thanks for the challenge btw

Edited by - x002548 on 04/25/2003 15:43:43

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources