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

Our new SQL Server Forums are live!
Come on over! We've restricted the ability to create new threads on these forums.

... Y'know, Corey, that LOOP looks a whole lot better than that 'book' you wrote!

Semper fi,

Xerxes, USMC(Ret.) ------------------------------------------------------ Once a Marine ALWAYS a Marine!

If you didn't like that one, I have another. This one actually seems to perform better Looks worse though

Declare @beginN int,
@EndN int,
@Step int
Select
@BeginN = -14,
@EndN = 53,
@step = 3
Declare @s table (n int)
Insert Into @s
Select n=@beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step From
(Select * From (Select n1=0 Union All Select power(2,0)) A,
(Select * From (Select n2=0 Union All Select power(2,1)) A,
(Select * From (Select n3=0 Union All Select power(2,2)) A,
(Select * From (Select n4=0 Union All Select power(2,3)) A,
(Select * From (Select n5=0 Union All Select power(2,4)) A,
(Select * From (Select n6=0 Union All Select power(2,5)) A,
(Select * From (Select n7=0 Union All Select power(2,6)) A,
(Select * From (Select n8=0 Union All Select power(2,7)) A,
(Select * From (Select n9=0 Union All Select power(2,8)) A,
(Select * From (Select n10=0 Union All Select power(2,9)) A,
(Select * From (Select n11=0 Union All Select power(2,10)) A,
(Select * From (Select n12=0 Union All Select power(2,11)) A,
(Select * From (Select n13=0 Union All Select power(2,12)) A,
(Select * From (Select n14=0 Union All Select power(2,13)) A,
(Select * From (Select n15=0 Union All Select power(2,14)) A,
(Select * From (Select n16=0 Union All Select power(2,15)) A,
(Select * From (Select n17=0 Union All Select power(2,16)) A,
(Select * From (Select n18=0 Union All Select power(2,17)) A
Where n18<=abs((@BeginN - @EndN)/@step)) n18
Where n17<=abs((@BeginN - @EndN)/@step)) n17
Where n16<=abs((@BeginN - @EndN)/@step)) n16
Where n15<=abs((@BeginN - @EndN)/@step)) n15
Where n14<=abs((@BeginN - @EndN)/@step)) n14
Where n13<=abs((@BeginN - @EndN)/@step)) n13
Where n12<=abs((@BeginN - @EndN)/@step)) n12
Where n11<=abs((@BeginN - @EndN)/@step)) n11
Where n10<=abs((@BeginN - @EndN)/@step)) n10
Where n9<=abs((@BeginN - @EndN)/@step)) n9
Where n8<=abs((@BeginN - @EndN)/@step)) n8
Where n7<=abs((@BeginN - @EndN)/@step)) n7
Where n6<=abs((@BeginN - @EndN)/@step)) n6
Where n5<=abs((@BeginN - @EndN)/@step)) n5
Where n4<=abs((@BeginN - @EndN)/@step)) n4
Where n3<=abs((@BeginN - @EndN)/@step)) n3
Where n2<=abs((@BeginN - @EndN)/@step)) n2
Where n1<=abs((@BeginN - @EndN)/@step)) n1
Where @EndN >= @beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step
Order By n
Select * from @s

Corey "If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Corey, wanna clue me in on how this one "works better?"

Semper fi,

XERXES, USMC(Ret.) ------------------------------------------------------ The Marine Corps taught me everything but SQL!

More along the lines of looks worse, but runs faster. I tested your code on my desktop machine for 200000 rows, and it ran in about 50 seconds. My code below ran in about 4 seconds on the same machine. I tested it recently on a fast server, and it was able to produce 16777216 rows in about 70 seconds.

You might notice that my code is very similar to what rockmoose posted in his function, but mine generates the tables to cross join on the fly, while his gets the rows to cross from a static table. Both pieces of code make use of a square root to minimize the number of rows to cross, and to multiply the second table in the cross by the square root of the total number of rows.

declare @START_NUMBER int,
@END_NUMBER int
select @START_NUMBER = 1
select @END_NUMBER = 200000
Declare @s table (n int)
Insert Into @s
select
NUMBER = (a.NUMBER+b.NUMBER)+
-- Picks lowest of start and end number
case
when @START_NUMBER <= @END_NUMBER
then @START_NUMBER
else @END_NUMBER end
from
(
Select top 100 percent
NUMBER = N01+N02+N03+N04+N05+N06+N07+N08+N09+N10+N11+N12
From
-- Cross rows from 12 tables based on powers of two
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 union all select 1 ) n01 cross join
( select N02 = 0 union all select 2 ) n02 cross join
( select N03 = 0 union all select 4 ) n03 cross join
( select N04 = 0 union all select 8 ) n04 cross join
( select N05 = 0 union all select 16 ) n05 cross join
( select N06 = 0 union all select 32 ) n06 cross join
( select N07 = 0 union all select 64 ) n07 cross join
( select N08 = 0 union all select 128 ) n08 cross join
( select N09 = 0 union all select 256 ) n09 cross join
( select N10 = 0 union all select 512 ) n10 cross join
( select N11 = 0 union all select 1024 ) n11 cross join
( select N12 = 0 union all select 2048 ) n12
where
N01+N02+N03+N04+N05+N06+N07+N08+N09+N10+N11+N12 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) a
cross join
(
Select -- Top with sort forces optimizer to create this result
-- before starting final cross join
top 100 percent
NUMBER =
(N01+N02+N03+N04+N05+N06+N07+N08+N09+N10+N11+N12) *
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
From
( select N01 = 0 union all select 1 ) n01 cross join
( select N02 = 0 union all select 2 ) n02 cross join
( select N03 = 0 union all select 4 ) n03 cross join
( select N04 = 0 union all select 8 ) n04 cross join
( select N05 = 0 union all select 16 ) n05 cross join
( select N06 = 0 union all select 32 ) n06 cross join
( select N07 = 0 union all select 64 ) n07 cross join
( select N08 = 0 union all select 128 ) n08 cross join
( select N09 = 0 union all select 256 ) n09 cross join
( select N10 = 0 union all select 512 ) n10 cross join
( select N11 = 0 union all select 1024 ) n11 cross join
( select N12 = 0 union all select 2048 ) n12
where
N01+N02+N03+N04+N05+N06+N07+N08+N09+N10+N11+N12 <
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) b
where
a.NUMBER+b.NUMBER < abs(@START_NUMBER-@END_NUMBER)+1 and
-- Do nothing if total rows over limit (4096*4096 = 16777216 )
abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216
order by
1

quote:]Originally posted by Seventhnight If you didn't like that one, I have another. This one actually seems to perform better Looks worse though

Declare @beginN int,
@EndN int,
@Step int
Select
@BeginN = -14,
@EndN = 53,
@step = 3
Declare @s table (n int)
Insert Into @s
Select n=@beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step From
(Select * From (Select n1=0 Union All Select power(2,0)) A,
(Select * From (Select n2=0 Union All Select power(2,1)) A,
(Select * From (Select n3=0 Union All Select power(2,2)) A,
(Select * From (Select n4=0 Union All Select power(2,3)) A,
(Select * From (Select n5=0 Union All Select power(2,4)) A,
(Select * From (Select n6=0 Union All Select power(2,5)) A,
(Select * From (Select n7=0 Union All Select power(2,6)) A,
(Select * From (Select n8=0 Union All Select power(2,7)) A,
(Select * From (Select n9=0 Union All Select power(2,8)) A,
(Select * From (Select n10=0 Union All Select power(2,9)) A,
(Select * From (Select n11=0 Union All Select power(2,10)) A,
(Select * From (Select n12=0 Union All Select power(2,11)) A,
(Select * From (Select n13=0 Union All Select power(2,12)) A,
(Select * From (Select n14=0 Union All Select power(2,13)) A,
(Select * From (Select n15=0 Union All Select power(2,14)) A,
(Select * From (Select n16=0 Union All Select power(2,15)) A,
(Select * From (Select n17=0 Union All Select power(2,16)) A,
(Select * From (Select n18=0 Union All Select power(2,17)) A
Where n18<=abs((@BeginN - @EndN)/@step)) n18
Where n17<=abs((@BeginN - @EndN)/@step)) n17
Where n16<=abs((@BeginN - @EndN)/@step)) n16
Where n15<=abs((@BeginN - @EndN)/@step)) n15
Where n14<=abs((@BeginN - @EndN)/@step)) n14
Where n13<=abs((@BeginN - @EndN)/@step)) n13
Where n12<=abs((@BeginN - @EndN)/@step)) n12
Where n11<=abs((@BeginN - @EndN)/@step)) n11
Where n10<=abs((@BeginN - @EndN)/@step)) n10
Where n9<=abs((@BeginN - @EndN)/@step)) n9
Where n8<=abs((@BeginN - @EndN)/@step)) n8
Where n7<=abs((@BeginN - @EndN)/@step)) n7
Where n6<=abs((@BeginN - @EndN)/@step)) n6
Where n5<=abs((@BeginN - @EndN)/@step)) n5
Where n4<=abs((@BeginN - @EndN)/@step)) n4
Where n3<=abs((@BeginN - @EndN)/@step)) n3
Where n2<=abs((@BeginN - @EndN)/@step)) n2
Where n1<=abs((@BeginN - @EndN)/@step)) n1
Where @EndN >= @beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step
Order By n
Select * from @s

Corey

Codo Ergo Sum

Edited by - Michael Valentine Jones on 02/25/2005 00:44:29

by the way, the reason I said the second one works better is that it a) generates a plan almost instantly, b) for the extraneous joins (the record sets that will only have a zero) they roll up to just a zero. It is built to simulate one built for the exact range.

its pretty hard to explain...

I really don't think 200,000 rows is a relevant test. a varchar column can only be 8000 characters. I don't think I've ever used more than a couple thousand anyway. For the lower sets of numbers, it is dynamic and fast (8000 runs <1 sec). Thats what counts

Corey "If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

quote:Originally posted by Michael Valentine Jones

You might notice that my code is very similar to what rockmoose posted in his function, but mine generates the tables to cross join on the fly, while his gets the rows to cross from a static table. Both pieces of code make use of a square root to minimize the number of rows to cross, and to multiply the second table in the cross by the square root of the total number of rows.Codo Ergo Sum

Mike,

This is really impressive: cross joining on the fly? Wow!

With your example, Rockmoose's and Corey's two examples, I have enough to study for a while

Thanks!

Semper fi,

XERXES, USMC(Ret.) ------------------------------------------------------ The Marine Corps taught me everything but SQL!

The reason I tested is with a value of 200000 rows, is because the largest number of rows your code would return is 262144. I just wanted to look at the inherent speed of the algorithim.

I also tested with smaller result sets, and found that the code I supplied was still faster. but the difference of 10000 rows in .15 vs .30 seconds was not very dramatic. At 1000 rows, the run time is so close there really isn't any difference.

quote:Originally posted by Seventhnight

yeah it is...

by the way, the reason I said the second one works better is that it a) generates a plan almost instantly, b) for the extraneous joins (the record sets that will only have a zero) they roll up to just a zero. It is built to simulate one built for the exact range.

its pretty hard to explain...

I really don't think 200,000 rows is a relevant test. a varchar column can only be 8000 characters. I don't think I've ever used more than a couple thousand anyway. For the lower sets of numbers, it is dynamic and fast (8000 runs <1 sec). Thats what counts

Corey "If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

we never got the TOS here. stupid tv's wont buy it... i watched some on german tv's but i hate when you hear someone else's voice synced over the original. that's the stupidest thing ever invented.

I liked the original best, But looking at it now the special effects Pppppppttttttt........

Then again it was state of the art back then, we were all going ouuuuu aahhh when the first phaser was used .

Jim Users <> Logic

But I don't understand why in "Turnabout Intruder" that Kirk didn't just use the darn shuttlecraft to rescue Sulu and the rest of the frozen landing party!

Semper fi,

XERXES, USMC(Ret.) ------------------------------------------------------ The Marine Corps taught me everything but SQL!