SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/23/2005 :  17:57:21  Show Profile  Reply with Quote
I am just saying that I found a function that returns a table of numbers more flexible. The permanent table may be a little faster to run, but the function is easier to code with. I any usually using it for one time situations where ease of coding is more important.

quote:
Originally posted by Xerxes

quote:
Originally posted by Michael Valentine Jones

I found it more useful to have a table valued function to return the numbers you need exactly.

The one I wrote returns anything up to 10,000 rows in less than a second, and will return 1,000,000 rows in about 10 seconds, so the performance hit is not huge in most situations. It slows down above that, so I limited the results to 10,000,000 rows, which it takes about two minutes to generate. This seems to cover the vast majority of situations.

Of course, you could store a useful number of rows in a permanent table if you need to use it a lot and are concerned with performance. You can still have a function around for when you find that more useful.


-- Return numbers 1 to 2000
select NUMBER from dbo.UDF_NUMBER_RANGE(1,2000)

-- Return numbers -14 to 3147
select NUMBER from dbo.UDF_NUMBER_RANGE(-14,3147)



Codo Ergo Sum



Michael,

You mean you're suggesting I do this for a permanent numbers table instead of just the old 1 - 8000 that the SQL Warrior Queen suggested?

Semper fi,

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



Codo Ergo Sum
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/23/2005 :  18:06:44  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
in all honesty, I also use a function instead of the permanent table.

mine has three params. (lower bound, upper bound, and increment)

Select n From dbo.getSequence(0,10,2)
Gives 0,2,4,6,8,10

It probably is faster the have and indexed permanent table though.

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
665 Posts

Posted - 02/24/2005 :  10:18:25  Show Profile  Reply with Quote
Corey & Mike...

Sorry to be so thickheaded, but I'm not sure about the actual NEED for a numbers table as the SQL Warrior Queen had suggested earlier. Anyhow, let me get this straight: you suggest a UDF for extracting from the permannet NUMBERS table? With that, these questions come to mind:

1.) Mike, why would you ever need a number-generating function that would require parameters? Couldn't you simply create a table tghat contained values large enough--beyond what you would ever need? For example, in using PI after 39 digits, your error in calculating the circumference of the orbit of PLUTO is about the width of a hydrogen atom. Don't we reach a point of diminishing returns here?

2.) Corey, aside from creating a few repetitious lines of code, what uses are there for a permanant 8000 member table?

3.) Tara had mentioned performance issues involved when deciding to make the NUMBERS table permanent as opposed to creating it on the fly. I understand that, but, really, what am I saving: a few milliseconds?

I hope you guys can clarify this for me. I know I'm missing something here, because I just don't get. Is this a serious issue in SQL-land? Why don't I see this in my developer's guide?

Thanks for any assistance you can offer. I sure appreciate your past efforts, this just makes me a better Yak.

Thanks!



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 :  10:29:18  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
In my case, the udf creates the table from scratch. I do not have a permanent numbers table. The parameters allow me to generate very specialized recordsets.

The benefits are not in the time taken to generate the table. Most likely the benefits of a permanent table would be the ability to index that table. This would likely speed up the query itself.

Again, I don't personally have a static numbers table, so I haven't taken the time to study the differences. My comment "If you don't have a permanent numbers table, then you should" is more directed toward the more inexperience SQL developers as it is a simpler concept than a function generated recordset.

X - You are shaping up to be a fine Yak

EDIT: Also in response to the 'is this important... why isn't it in a book?' We have found many uses for tally/numbers tables. I am sure it is in a book, but you just aren't reading the right one. I am sure there are quite a few very interesting things that come up around here that are not in books (or not in most books at least). Thats why hanging out around SqlTeam is so helpful.

Corey

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

Edited by - Seventhnight on 02/24/2005 10:34:59
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/24/2005 :  10:37:48  Show Profile  Visit spirit1's Homepage  Reply with Quote
hey corey can you show me your function?

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/24/2005 :  10:43:05  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
sure... but it's nothing special:


CREATE FUNCTION dbo.getSequence
(
	@BeginNum int,
	@EndNum int,
	@Incr int
)
RETURNS @Sequence Table
(
	n int
)
AS
BEGIN 
	While @BeginNum <= @EndNum
	Begin
		Insert Into @Sequence (n) 
		Select n = @BeginNum

		Select @BeginNum = @BeginNum + @Incr
	End
	RETURN
END



and yes there his a while loop

Corey

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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/24/2005 :  10:53:00  Show Profile  Visit spirit1's Homepage  Reply with Quote
arghhhhh ..... i was hoping you'd come up with something that has no loop.
i was playing with it and can't find a way to do that with no loop...

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

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 02/24/2005 :  12:02:31  Show Profile  Reply with Quote
"In a set-based world a loop is the fly in the ointment."--Xerxes the Ointment Owner

You may quote me!

Thanks for your help, Corey! Your explanation has added to my Yak-ness


Semper fi,

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

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/24/2005 :  12:07:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
The permanent numbers table is the way to go for performance. You should have a clustered index on the n column in that table. It will be much faster than a UDF that builds it each time.

Tara
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 02/24/2005 :  12:30:26  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

The permanent numbers table is the way to go for performance. You should have a clustered index on the n column in that table. It will be much faster than a UDF that builds it each time.

Tara



SQL Warrior Queen ,

Thanks for your response, however, I remain perplexed regarding my #3 question where I said
"3.) Tara had mentioned performance issues involved when deciding to make the NUMBERS table permanent as opposed to creating it on the fly. I understand that, but, really, what am I saving: a few milliseconds?"

I believe we also reach a point of diminishing returns here as well. I don't know what I'm really 'saving' here. It's like my analogy regarding Pi & Pluto (see prior posting) or even 'paper & plastic'--what's the difference? What is truly being saved here? Time? Server speed? What? As a DBA maybe you can explain this well enough. I just don't get it, Tara. I'm sorry, but when we're discussing a pure integer-generator, what is really the 'performance' issue? Is it a function of data size? The scope of the processing (multiple tables requiring the integer-generator)? I'm stumped.

Thanks for your assistance!


Semper fi,

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

Edited by - Xerxes on 02/24/2005 12:31:28
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/24/2005 :  12:37:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well where I work, our applications (each major version of it) go through about 3 months of performance testing. When your queries typically run in about 500 milliseconds, you want to squeeze out a few milliseconds. And that's with a 10GB database. Now if you've got a different scenario where queries are taking a few seconds to run, then a few milliseconds isn't going to be noticed as the user already has to wait. I personally see a few milliseconds worth it regardless though. But you guys might not have the strict performance requirements that we do here, so you might not be as concerned with it.

The performance issue is the time that it takes to generate the numbers. Not with the data size or anything else. And yes if you are constantly having to generate these, then why not have a static table? Really, why not? I personally don't see ease of coding as an answer. I strive to write good code and not just code that is easy. Typically people who do this type of thing will use dynamic SQL, cursors, select *, and all sorts of other bad T-SQL programming practices. I'm not trying to hit anyone in this thread about this, I'm just trying to give you the point of view of a DBA. Often times, the DBA is at odds with the developer when it comes to these sorts of things. But where I work, the DBA wins as that is what we are here to do (partially of course as I have other things that I do).

Tara

Edited by - tkizer on 02/24/2005 12:39:11
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 02/24/2005 :  12:49:34  Show Profile  Visit JimL's Homepage  Reply with Quote
quote:
But where I work, the DBA wins as that is what we are here to do (partially of course as I have other things that I do).



Thats Cause Tara will beat them over the head with her snowboard if they dont let her win!


Aaaaaahh thats ok Tara we would not want you any other way!

Jim
Users <> Logic
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 02/24/2005 :  12:54:59  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

Well where I work, our applications (each major version of it) go through about 3 months of performance testing. When your queries typically run in about 500 milliseconds, you want to squeeze out a few milliseconds. And that's with a 10GB database. Now if you've got a different scenario where queries are taking a few seconds to run, then a few milliseconds isn't going to be noticed as the user already has to wait. I personally see a few milliseconds worth it regardless though. But you guys might not have the strict performance requirements that we do here, so you might not be as concerned with it.

The performance issue is the time that it takes to generate the numbers. Not with the data size or anything else. And yes if you are constantly having to generate these, then why not have a static table? Really, why not? I personally don't see ease of coding as an answer. I strive to write good code and not just code that is easy. Typically people who do this type of thing will use dynamic SQL, cursors, select *, and all sorts of other bad T-SQL programming practices. I'm not trying to hit anyone in this thread about this, I'm just trying to give you the point of view of a DBA. Often times, the DBA is at odds with the developer when it comes to these sorts of things. But where I work, the DBA wins as that is what we are here to do (partially of course as I have other things that I do).

Tara




Thanks for your assessment, Tara. It makes much more sense now. It seems to me that where you work, what the DBA says goes. Sounds a lot like the way we did things in the Marine Corps (which I miss terribly!) But I can see the 'why' of the performance issues in your case: it's a matter of PRIDE--which, by the way, as a MARINE I understand perfectly!

Have a great day, 'cause I'm having one too!



Semper fi,

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

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/24/2005 :  12:55:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Hehe. And I'm using the board on Sunday, can't wait!

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/24/2005 :  12:57:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
Where's Jeff (jsmith8858) and all the others guys to help me out here? They would have all sorts of information regarding the permanent table versus the UDF.

Tara
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/24/2005 :  13:01:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
i don't get what's there to debate about??
it's down to
building a udf each time VS a clustered index seek.

an as far as i know those seeks are preety tough to beat speed wise.


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

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/24/2005 :  13:11:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
Agreed. But it seems a few people here have gone the UDF route due to ease of coding.

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/24/2005 :  13:54:15  Show Profile  Reply with Quote
Nothing says that one can't use both techniques.
If there is code that makes use of a number table and is frequently run,
then it just makes no sense to NOT use a static table for performance issues.
For one of situations, saving a few milliseconds is not as important, and you might use what you feel comfy with.

I have usually have both options at hand, and use what feels best for the task at hand.

rockmoose
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 02/24/2005 :  14:15:09  Show Profile  Reply with Quote
quote:
Originally posted by rockmoose

Nothing says that one can't use both techniques.
If there is code that makes use of a number table and is frequently run,
then it just makes no sense to NOT use a static table for performance issues.
For one of situations, saving a few milliseconds is not as important, and you might use what you feel comfy with.

I have usually have both options at hand, and use what feels best for the task at hand.

rockmoose



Sure makes sense to me, Moose! I just like getting several points of view on issues of this sort because the wealth of experience here is just too tempting not to try

Thanks!

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 :  14:45:29  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
quote:
Originally posted by spirit1

arghhhhh ..... i was hoping you'd come up with something that has no loop.
i was playing with it and can't find a way to do that with no loop...

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




Because you complained
granted generating the plan takes a few seconds.... once done its fine


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)*@step
From (Select * From (Select n1=0 Union All Select power(2,0)) A Where n1<=abs((@BeginN - @EndN)/@step)) n1
Cross Join (Select * From (Select n2=0 Union All Select power(2,1)) A Where n2<=abs((@BeginN - @EndN)/@step)) n2
Cross Join (Select * From (Select n3=0 Union All Select power(2,2)) A Where n3<=abs((@BeginN - @EndN)/@step)) n3
Cross Join (Select * From (Select n4=0 Union All Select power(2,3)) A Where n4<=abs((@BeginN - @EndN)/@step)) n4
Cross Join (Select * From (Select n5=0 Union All Select power(2,4)) A Where n5<=abs((@BeginN - @EndN)/@step)) n5
Cross Join (Select * From (Select n6=0 Union All Select power(2,5)) A Where n6<=abs((@BeginN - @EndN)/@step)) n6
Cross Join (Select * From (Select n7=0 Union All Select power(2,6)) A Where n7<=abs((@BeginN - @EndN)/@step)) n7
Cross Join (Select * From (Select n8=0 Union All Select power(2,7)) A Where n8<=abs((@BeginN - @EndN)/@step)) n8
Cross Join (Select * From (Select n9=0 Union All Select power(2,8)) A Where n9<=abs((@BeginN - @EndN)/@step)) n9
Cross Join (Select * From (Select n10=0 Union All Select power(2,9)) A Where n10<=abs((@BeginN - @EndN)/@step)) n10
Cross Join (Select * From (Select n11=0 Union All Select power(2,10)) A Where n11<=abs((@BeginN - @EndN)/@step)) n11
Cross Join (Select * From (Select n12=0 Union All Select power(2,11)) A Where n12<=abs((@BeginN - @EndN)/@step)) n12
Cross Join (Select * From (Select n13=0 Union All Select power(2,12)) A Where n13<=abs((@BeginN - @EndN)/@step)) n13
Cross Join (Select * From (Select n14=0 Union All Select power(2,13)) A Where n14<=abs((@BeginN - @EndN)/@step)) n14
Where @beginN+(n1+n2+n3+n4+n5+n6+n7)*@step < @EndN
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
Go to Top of Page
Page: of 7 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  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.61 seconds. Powered By: Snitz Forums 2000