Finding values with cents...

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

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

  • 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
     
  • 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)

  • 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