February 2, 2007 at 2:47 pm
Thanx again... seems to be luck of the current load that dictates the results and not the function itself.
February 2, 2007 at 2:54 pm
I concur. It's more of a load based thing as opposed to function based on the small resutls set. It was definitely a fun thing to do since the script was already provided - especially the @Bitbucket .
Have a great weekend !
(I'm taking off early at 4:00 PM today)
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 2, 2007 at 8:21 pm
I think the tests done already are getting overwhelmed by the overhead of looping.
I used a different methodology to test. I loaded a table containing a single money column with 5,000,000 rows, re-indexed it with a fill factor of 100%, ran select statements to test different methods for rounding off the money value, and displayed the elapsed milliseconds for each one.
I found that using the FLOOR function gave a small but consistent runtime advantage over ROUND or CONVERT. The longest runtime for FLOOR was 73% of the best of either ROUND or CONVERT in the test results posted below.
The F_TABLE_NUMBER_RANGE function used to load the test data is available at this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
/* -- Commented section loads the test table
--drop table T_TEST_ROUND_CENTS create table T_TEST_ROUND_CENTS (num money not null primary key clustered)
-- Load table T_TEST_ROUND_CENTS with 5,000,000 rows, $0.0100 to $50000.0000 insert into T_TEST_ROUND_CENTS select NUMBER = NUMBER *.01 from f_table_number_range(1,5000000) order by number
-- Reindex table for maximum page fill dbcc dbreindex (T_TEST_ROUND_CENTS,'',100)
-- Show size of table exec sp_spaceused T_TEST_ROUND_CENTS,'true'
select min(num),max(num),count(*) from T_TEST_ROUND_CENTS
*/
go set nocount on go -- Do not round, control table scan declare @st datetime declare @x money set @st = getdate()
select @x = num from T_TEST_ROUND_CENTS
select [Do Nothing] = datediff(ms,0,getdate()-@st), [Rowcount] = @@rowcount go -- Test ROUND function declare @st datetime declare @x money set @st = getdate()
select @x = round(num,0) from T_TEST_ROUND_CENTS
select [Round] = datediff(ms,0,getdate()-@st), [Rowcount] = @@rowcount go -- Test CONVERT function declare @st datetime declare @x money set @st = getdate()
select @x = convert(int,num) from T_TEST_ROUND_CENTS
select [Convert] = datediff(ms,0,getdate()-@st), [Rowcount] = @@rowcount go -- Test FLOOR function declare @st datetime declare @x money set @st = getdate()
select @x = floor(num+$0.5000) from T_TEST_ROUND_CENTS
select [Floor] = datediff(ms,0,getdate()-@st), [Rowcount] = @@rowcount go
Results:
Test# 1:
Do Nothing Rowcount ----------- ----------- 3083 5000000
Round Rowcount ----------- ----------- 6780 5000000
Convert Rowcount ----------- ----------- 6880 5000000
Floor Rowcount ----------- ----------- 4890 5000000
Test# 2:
Do Nothing Rowcount ----------- ----------- 3076 5000000
Round Rowcount ----------- ----------- 6730 5000000
Convert Rowcount ----------- ----------- 6810 5000000
Floor Rowcount ----------- ----------- 4886 5000000
Test# 3:
Do Nothing Rowcount ----------- ----------- 3093 5000000
Round Rowcount ----------- ----------- 6740 5000000
Convert Rowcount ----------- ----------- 6820 5000000
Floor Rowcount ----------- ----------- 4880 5000000
February 4, 2007 at 4:42 pm
I agree with that! The looping has got to interfere with the overall performance.
Anyway, Michael... I have a question about your wonderful F_TABLE_NUMBER_RANGE function... Since it's very fast (Surprisingly so! I especially like the tuning trick you did with the CASE in the WHERE clause not to mention the use of TOP and ORDER BY), have you used it in anything like a "split" function instead of using a Tally (numbers) table? If so, what were the effects on performance?
Also, since it's such a cool function, would you tell us what you have used it for? I can see things like doing an outer join on converting the numbers to dates to allow for 0 sums on missing dates and the like and using it as a driver to construct test tables. I can even see using it to help develop "bins" for hourly reporting by day. But I sure wouldn't mind some ideas as to where else to use it. Thanks...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2007 at 7:31 pm
I use it any place that you might use a number table. It's fast enough that I see little need for keeping a permanent number table. I like being able to just give the first and last number and getting the exact list of numbers that I want. It makes it very convenient for generating test data.
You outlined most of the uses that I have come up with. On a recent project I used it to generate 40,000,000 random numbers for test data using the newid() function.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply