Results 1 to 22 of 22

Thread: [RESOLVED] SQL - Basic math issue

  1. #1

    Thread Starter
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,601

    Resolved [RESOLVED] SQL - Basic math issue

    The numbers are what are in the table columns. I cannot figure out why it is returning zero.

    SQL Server 2014

    select convert(decimal(16,6), (6250 - 3104820) / 3104820) * (53 / 100 )
    Please remember next time...elections matter!

  2. #2
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,657

    Re: SQL - Basic math issue

    Can't test it, and not sure how SQL handles it, and not motivated enough to look it up right now myself, but could it be that dividing of integers results in an integer type, so (53/100) = 0?

  3. #3

    Thread Starter
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,601

    Re: SQL - Basic math issue

    Quote Originally Posted by OptionBase1 View Post
    Can't test it, and not sure how SQL handles it, and not motivated enough to look it up right now myself, but could it be that dividing of integers results in an integer type, so (53/100) = 0?
    I think it might be something to do with default data types but also I am so weak in math. Plug it into you calculator...your will get the right answer.
    Please remember next time...elections matter!

  4. #4
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,657

    Re: SQL - Basic math issue

    Has nothing to do with calculator math. In many programming languages, the result of an int divided by an int is an int. And an int has no decimal value, so values are truncated.

    So 53 / 100 is not 0.53, it is 0.

    Change all values in the calculation so they all have a decimal piece (so 53.0 / 100.0, etc.) and see if that changes the result.

  5. #5
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,320

    Re: SQL - Basic math issue

    select convert(decimal(16,6), (6250 - 3104820) / 3104820) returns 0.000000

    select convert(decimal(16,6), (6250 - 3104820) / 3104820.0) returns -0.997987

    You have to decide what data types you want to use to the intermediate result -- DECIMAL (aka fixed point) or FLOAT (aka floating point).

    We are using DECIMAL(24, 12) for all out calculation but sometimes the precision is not enough. Here is how we coerce intermediate data-types

    Code:
    select convert(decimal(16,6), 
            CONVERT(DECIMAL(24, 12), 
            CONVERT(DECIMAL(24, 12), 
            CONVERT(DECIMAL(24, 12), 6250 - 3104820)
                / CONVERT(DECIMAL(24, 12), 3104820))
                * CONVERT(DECIMAL(24, 12), 
                    CONVERT(DECIMAL(24, 12), 53)
                    / CONVERT(DECIMAL(24, 12), 100))))
    It's a mouthful but this allows us to control exactly precision/scale escalation during the intermedate steps in complex calculations.

    cheers,
    </wqw>

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: SQL - Basic math issue

    Quote Originally Posted by TysonLPrice View Post
    The numbers are what are in the table columns. I cannot figure out why it is returning zero.

    SQL Server 2014

    select convert(decimal(16,6), (6250 - 3104820) / 3104820) * (53 / 100 )
    If you are doing this deep a level of math, then this formula needs to be in a scalar function where you use properly typed variables. If you were in VB you would most likely not put this type of formula in-line in your code - treat T-SQL the same way.

    Code:
    Drop Function dbo.DoCalc_F
    Go
    Create Function dbo.DoCalc_F(@F1 decimal(16,6), @F2 decimal(16,6))
    Returns decimal(16,6)
    as
    Begin 
    Declare @X1 decimal(16,6)
    Declare @X2 decimal(16,6)
    Declare @X3 decimal(16,6)
    
    Set @X1=53
    Set @X2=100
    
    Set @X3=(@F1 - @F2) / @F2 * (@X1 / @X2)
    --decimal(16,6), (6250 - 3104820) / 3104820) * (53 / 100 ) 
    
    Return @X3
    
    End
    Code:
    Select dbo.DoCalc_F(6250,3104820)
    
    ---------------------------------------
    -0.528933
    
    (1 row affected)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,601

    Re: SQL - Basic math issue

    Thanks all...as stated I needed decimals.
    Please remember next time...elections matter!

  8. #8
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,320

    Re: [RESOLVED] SQL - Basic math issue

    Don't to this with scalar UDF as these are performance killers for large resultsets. It is only sql2019 that tries to inline scalar UDFs so you *might* not get penalized if such calculations are extracted into UDFs.

    On any previous db engine version though expanding and repeating the full calculation inline (however bulk) is the only option to get any decent performance. . . ¯\_(ツ)_/¯

    cheers,
    </wqw>

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Quote Originally Posted by wqweto View Post
    Don't to this with scalar UDF as these are performance killers for large resultsets. It is only sql2019 that tries to inline scalar UDFs so you *might* not get penalized if such calculations are extracted into UDFs.

    On any previous db engine version though expanding and repeating the full calculation inline (however bulk) is the only option to get any decent performance. . . ¯\_(ツ)_/¯

    cheers,
    </wqw>
    Been creating enterprise systems for 20 years with all business logic in T-SQL sprocs and udf's and tvf's. I've never seen a problem with speed and my clients have tables with millions of rows (health and education industries).

    I very strongly disagree with your assessment of performance killing!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Truly simple test for speed here. Only 1.5 million rows - both inline math and same select with a UDF.

    Adding three INT fields from the table.

    Both took 6 seconds.

    Speed of access in SQL is nearly all buried in I/O. Everything past the I/O portion of any bottleneck is small potatoes in comparison, imo.
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Here is another comparison. Instead of returning 1.5 million results, I'm doing a MIN().

    The in-line math runs instantly - recording zero seconds.

    The UDF takes 2 seconds.

    On 1.5 million calls, I would not consider "2 seconds" to be "performance killers".
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Here is a set of "scripts" for UDF's at one of my clients sites. I've got 50 of them - doing things as simple as figure your age to building "address lines" from many "address fields".
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,601

    Re: [RESOLVED] SQL - Basic math issue

    Maybe he was referring to something like this. I'm not disagreeing with your test results. Maybe "it just depends".

    https://www.red-gate.com/simple-talk...-code-changes/

    Why are Scalar UDF Functions inherently slow?

    When running a scaler UDF on a database with a compatibility level set to less than 150, they just don’t scale well. By scale, I mean they work fine for a few rows but run slower and slower as the number of rows processed gets larger and larger. Here are some of the reasons why scalar UDF’s don’t work well with large recordsets.

    When a T-SQL statement uses a scalar function, the database engine optimizer doesn’t look at the code inside a scalar function to determine its costing. This is because Scalar operators are not costed, whereas relational operators are costed. The optimizer considers scalar functions as a black box that uses minimal resources. Because scalar operations are not costed appropriately, the optimize is notorious for creating very bad plans when scalar functions perform expensive operations.
    A Scalar function is evaluated as a batch of statements where each statement is run sequentially one statement after another. Because of this, each statement has its own execution plan and is run in isolation from the other statements in the UDF, and therefore can’t take advantage of cross-statement optimization.
    The optimize will not allow queries that use a scalar function to go parallel. Keep in mind, parallelism may not improve all queries, but when a scalar UDF is being used in a query, that query’s execution plan will not go parallel.
    Please remember next time...elections matter!

  14. #14
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,320

    Re: [RESOLVED] SQL - Basic math issue

    Of course *scalar* UDFs performance problems are documented. Not table-valued UDFs (multi-step or not), not SPs but exactly scalar ones.

    We do have UDFs (thousands of them) and have been using these since sql2000 but I would never recommend encapsulating logic is scalar UDFs without first considering performance implications on the particular SQL Server version the solution is targeting.

    Recent sql2019 mitigates such performance issues by successfully inlining scalar UDFs if these are not particularly complex (e.g. WHILE loops comes to mind) so this might be ok to use.

    With RDBMs I would always prefer to sticking with "cave-age solutions" instead of using "latest shiny slick" ones out of habit -- been biten too many times already.

    cheers,
    </wqw>

  15. #15
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Quote Originally Posted by TysonLPrice View Post
    Maybe he was referring to something like this. I'm not disagreeing with your test results. Maybe "it just depends".

    https://www.red-gate.com/simple-talk...-code-changes/

    Why are Scalar UDF Functions inherently slow?

    When running a scaler UDF on a database with a compatibility level set to less than 150, they just don’t scale well. By scale, I mean they work fine for a few rows but run slower and slower as the number of rows processed gets larger and larger. Here are some of the reasons why scalar UDF’s don’t work well with large recordsets.

    When a T-SQL statement uses a scalar function, the database engine optimizer doesn’t look at the code inside a scalar function to determine its costing. This is because Scalar operators are not costed, whereas relational operators are costed. The optimizer considers scalar functions as a black box that uses minimal resources. Because scalar operations are not costed appropriately, the optimize is notorious for creating very bad plans when scalar functions perform expensive operations.
    A Scalar function is evaluated as a batch of statements where each statement is run sequentially one statement after another. Because of this, each statement has its own execution plan and is run in isolation from the other statements in the UDF, and therefore can’t take advantage of cross-statement optimization.
    The optimize will not allow queries that use a scalar function to go parallel. Keep in mind, parallelism may not improve all queries, but when a scalar UDF is being used in a query, that query’s execution plan will not go parallel.
    This exact point from the paragraph above: "bad plans when scalar functions perform expensive operations."

    has zero to do with your simple math formula. UDF's were designed for exactly the purpose of your simple formula. When we rolled our own DB's on VAX/VMS systems in the 80's we had "equation" fields - something I'm really familiar with from an actual backend database programmer world.

    Everything in T-SQL has a purpose. Used properly they shine. Used improperly they clunk.

    Blanket statements about bad performance need to be challenged and clarified in order to keep this forum clean. That was my intent with these posts - clarifying the "bad performance" characterization so future readers on the forum get full info.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #16
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,484

    Re: [RESOLVED] SQL - Basic math issue

    In 2 seconds can be bad..... I have to return results in less that 2 seconds with internal time (time spent in our DB) should be under 500 ms. We are in the Credit card processing area
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Quote Originally Posted by GaryMazzone View Post
    In 2 seconds can be bad..... I have to return results in less that 2 seconds with internal time (time spent in our DB) should be under 500 ms. We are in the Credit card processing area
    I ran a query with ONLY the UDF - against 1.5 million rows. That took 2 seconds.

    2 seconds / 1.5 million is not a lot of "clock time" per row.

    Normally that simple math UDF would be part of a more complex query with lots of fields. All that other "sql" will make the UDF insignificant. I can't believe we are having this discussion at this point, lol!

    .0000013 seconds per row...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,320

    Re: [RESOLVED] SQL - Basic math issue

    Quote Originally Posted by szlamany View Post
    I ran a query with ONLY the UDF - against 1.5 million rows. That took 2 seconds.

    2 seconds / 1.5 million is not a lot of "clock time" per row.
    I've seen bad performing innocuous looking UDFs e.g. in the 30 seconds overhead per mil rows. This one is clearly not a suspect but dealing with strings (VARCHARs) of any kind is probably worst for the engine's memory allocator.

    Why would you want to incur any overhead at all? This would escalate quickly on a busy server or with complex queries (based on views calling UDFs or with many JOINs)

    For scalar UDFs the age old workaround is to use inline TVFs instead so SELECT dbo.MyScalarUdf(@Param1, @Param2, @Param3) As Col1 is replaced by SELECT (SELECT Value FROM dbo.MyInlineTVF(@Param1, @Param2, @Param3)) As Col1 so that the calculation in the body of the function is really inlined because inline TVF are like views with parameters for the query optimizer i.e. always expanded before optimizing the final query.

    cheers,
    </wqw>

  19. #19
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,320

    Re: [RESOLVED] SQL - Basic math issue

    Btw, another thing just came up to my mind: Do you know that queries with scalar UDFs cannot go for parallel execution plans?

    This might hurt performance a lot if the query is deemed fit for paralel execution otherwise.

  20. #20
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Quote Originally Posted by wqweto View Post
    Btw, another thing just came up to my mind: Do you know that queries with scalar UDFs cannot go for parallel execution plans?

    This might hurt performance a lot if the query is deemed fit for paralel execution otherwise.
    I write complex business applications that have all business logic in SPROCS, UDF's and TVF's. We calculate payrolls in a SPROC, schedule students into high school classes in a SPROC, calculate your pension in a SPROC.

    I've done this since I first arrived into the MS SQL world in 2001. This allows me to launch major functionality changes without releasing any "executable" code.

    I've always created 4GL's - even since my PDP and VAX days - and the last two (one in VB6 and now the latest in jQuery/JS/Ajax/.Net backend) work perfectly with business logic in T-SQL. Basically all my clients run the exact same "business agnostic" front end along with an equally "business agnostic" VB.Net backend running in IIS.

    I've encountered every kind of bottleneck you could imagine! And worked around them. When I started in 2001, the first book I bought was INSIDE MS SQL SERVER 2000 by Kalen Delaney. Having come from a PDP and VAX past we always grew our own ISAM database systems. I digested as many facts as I could, was able to apply that to my past experiences, and have had a hugely successful time working in MS SQL.

    That's why I'm the MS SQL PowerPoster, lol!

    At any rate - I consider things that happen once the data arrives in memory and is being packaged up for returning the result set to be of the least concern.

    I balance things against on going "maintenance" and "future" enhancements...having functions encapsulate logic like this just makes too much sense to me.

    If I'm going to have half a dozen tax collectors in PA running the same application, then being able to change UDF's for customization for each client, really helps me out.

    Not getting "parallel execution plans" doesn't usually affect my ROI.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  21. #21
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,320

    Re: [RESOLVED] SQL - Basic math issue

    I became Microsoft Certified Trainer in 1998 and the first Microsoft Official Course on SQL Server I lectured was about version 6.5 — yikes! This is before the complete rewrite version 7 was and two version before oldest version 2000 used nowadays.

    I dropped teaching at version 2000 and have been producing business applications with MSSQL on a daily basis in the past 20+ years. I’m simultaneously dog-fooding our apps and have been responsible for daily devops on the fleet of DB servers for all our clients in the meantime incl. measuring, planning, executing and A/B testing every possible performance optimization.

    I’ve had great success with implementing SQL Server solutions so far.

  22. #22
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,090

    Re: [RESOLVED] SQL - Basic math issue

    Quote Originally Posted by wqweto View Post
    I’ve had great success with implementing SQL Server solutions so far.
    That is great to hear!

    And now we can both walk away realizing that there are several ways to successfully utilize the MS SQL tool that we have all come to love!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width