Finding values with cents...

Question

Finding values with cents...

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Thanx again... seems to be luck of the current load that dictates the results and not the function itself.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

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."

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

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.