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.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 The Forms of Things Unknown
 Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 7

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  17:54:51  Show Profile  Reply with Quote
Will this take it to 5 pages? Just curious.....

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/24/2005 :  18:04:10  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
post count?? whats that?

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  18:06:48  Show Profile  Reply with Quote
quote:
Originally posted by Seventhnight

post count?? whats that?

Corey

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



Yeah, what is that anyway? ha, ha

Wanted to thank you for the code generator idea. I also posted a response to your puzzle solution in the Yak Corral.

Thanks for putting up with me, Corey! You've been a big help!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  23:10:09  Show Profile  Reply with Quote
quote:
Originally posted by Seventhnight

quote:
Originally posted by Xerxes

...
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!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/25/2005 :  00:24:02  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 02/25/2005 :  06:59:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
this is getting to be a very loooooooong thread...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/25/2005 :  08:17:47  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 02/25/2005 :  09:03:27  Show Profile  Visit JimL's Homepage  Reply with Quote
This is begining to look like another tread to shamelessly update ones post count.

Dang I guess that includes me.



Jim
Users <> Logic
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/25/2005 :  10:04:38  Show Profile  Reply with Quote
quote:
Originally posted by JimL

This is begining to look like another tread to shamelessly update ones post count.

Dang I guess that includes me.



Jim
Users <> Logic



Great to have you, Jim. Can you guess where the topic title came from?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/25/2005 :  10:11:54  Show Profile  Reply with Quote
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!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/25/2005 :  10:19:47  Show Profile  Reply with Quote
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



Codo Ergo Sum
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 02/25/2005 :  10:26:14  Show Profile  Visit JimL's Homepage  Reply with Quote

quote:


Great to have you, Jim. Can you guess where the topic title came from?



You Stated that earler in the thread. But I knew it anyway.
And scott and spock were my favs, kurk was too full of himself.

Jim
Users <> Logic
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 02/25/2005 :  10:33:27  Show Profile  Visit spirit1's Homepage  Reply with Quote
so how do you guys like the new enterprise?
i like season 4 and i'm sad it'll be the last one.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/25/2005 :  11:05:36  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

so how do you guys like the new enterprise?
i like season 4 and i'm sad it'll be the last one.

Go with the flow & have fun! Else fight the flow



I'll be sad to see it end, too.

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 02/25/2005 :  11:47:14  Show Profile  Visit JimL's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 02/25/2005 :  11:55:29  Show Profile  Visit spirit1's Homepage  Reply with Quote
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.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/25/2005 :  11:59:07  Show Profile  Reply with Quote
quote:
Originally posted by JimL

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!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/25/2005 :  12:00:14  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

so how do you guys like the new enterprise?
i like season 4 and i'm sad it'll be the last one.

Go with the flow & have fun! Else fight the flow



WHAT?

There's so much more material....

Oh...and let's start a new thread...

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=15





Brett

8-)
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 02/25/2005 :  12:01:38  Show Profile  Visit spirit1's Homepage  Reply with Quote
Astoundingly, Lieutenant Galloway returns to life for this episode after being vapourised by Captain Tracey in "The Omega Glory"!

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/25/2005 :  12:08:08  Show Profile  Reply with Quote
I'm number 100!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!
Go to Top of Page
Page: of 7 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000