Home Forums SQL Server 2014 Development - SQL Server 2014 how to write query RE: how to write query

  • August 31, 2013 at 10:15 am

    #1646575

    Erland Sommarskog (8/31/2013)

    Jeff Moden (8/30/2013)

    If you're interested in speed, STR isn't the way to do it.

    It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem.

    And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).

    True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.

    I like str() + replace() because is short and concise.

    I used to think the same thing for a very long time. Like me, you might want to change you mind.

    It may be "concise" but it's far from accurate and far from being performant.

    It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.

    STR() is also more than twice as slow as RIGHT() concatenation method. While I agree that a 2,300ms difference in CPU time (3,800ms vs 1,500ms) for a single instance of the formula over a million rows on a relatively slow machine isn't much, just imagine if everyone on a project paid attention to such minor nuances... the end result would be code that ran twice as fast as the compeition's code. 😉 And, what does it cost? If you know how to use the RIGHT() concatenation method and even if you can't actually type quickly, the answer is measured as just a second or two to get twice the performance and much more accurate rounding.

    Here's a link for some testing that I did that demonstrates the performance and accuracy differences. Again, it was a while back meaning that I did the testing on my really old desktop and that causes the appearance of a larger difference in time but that doesn't affect things like the rounding errors when working with decimal numbers.

    http://www.sqlservercentral.com/articles/T-SQL/71565/

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