varbinary(float) to int

  • Great work Jeff!

    An excellent candidate for the Script Library!

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Thanks for the great feedback, David,

    Couldn't believe the pickle someone put you into... had to do something to get you out of that mess

    --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)
    Intro to Tally Tables and Functions

  • High praise coming from the likes of you, Peter!  Thank you and I know which "Script Library" you're talking about... I'll throw it in in the next day or two. 

    If it's something that get's used a lot, I think I may have figured a way to optimize it a bit (no pun intended ) by building a "mini-tally" table with all the bit values already precalculated.

    --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)
    Intro to Tally Tables and Functions

  • I took my old VB function and adapted according to your code (11-bit exponent and all), and it performs 3 times faster than your original code.

    BUT (drawback here)... There seems to be a discrepancy of how my 1  / ( 2 ^ bitno) calculates versus yours. Maybe you can find the discrepancy? The error is less than 2.8421709430404E-14!

    CREATE

    FUNCTION dbo.fnBinaryFloat2Float

    (

            @BinaryFloat BINARY(8)

    )

    RETURNS FLOAT

    AS

    BEGIN

            DECLARE @Part TINYINT,

                    @PartValue TINYINT,

                    @Mask TINYINT,

                    @Mantissa FLOAT,

                    @Exponent SMALLINT,

                    @Bit TINYINT,

                    @Ln2 FLOAT,

                    @BigValue BIGINT

            SELECT  @Part = 1,

                    @Mantissa = 1,

                    @Bit = 1,

                    @Ln2 = LOG(2),

                    @BigValue = CAST(@BinaryFloat AS BIGINT),

                    @Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

            WHILE @Part <= 8

                    BEGIN

                            SELECT  @Part = @Part + 1,

                                    @PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),

                                    @Mask = CASE WHEN @Part = 2 THEN 8 ELSE 128 END

                            WHILE @Mask > 0

                                    BEGIN

                                            IF @PartValue & @Mask > 0

                                                    SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

                                            SELECT  @Bit = @Bit + 1,

                                                    @Mask = @Mask / 2

                                    END

                    END

            RETURN SIGN(@BigValue) * @Mantissa * EXP((@Exponent - 1023) * @Ln2)

    END

     


    N 56°04'39.16"
    E 12°55'05.25"

  • I found the error!!!

    Replace RETURN line of

    RETURN SIGN(@BigValue) * @Mantissa * EXP((@Exponent - 1023) * @Ln2)

    to

    RETURN SIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)

    And ZERO ERRORS occurs!!!

     

     

     

    ALTER FUNCTION dbo.fnBinaryFloat2Float

    (

            @BinaryFloat BINARY(8)

    )

    RETURNS FLOAT

    AS

    BEGIN

            DECLARE @Part TINYINT,

                    @PartValue TINYINT,

                    @Mask TINYINT,

                    @Mantissa FLOAT,

                    @Exponent SMALLINT,

                    @Bit TINYINT,

                    @Ln2 FLOAT,

                    @BigValue BIGINT

            SELECT  @Part = 1,

                    @Mantissa = 1,

                    @Bit = 1,

                    @Ln2 = LOG(2),

                    @BigValue = CAST(@BinaryFloat AS BIGINT),

                    @Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

            WHILE @Part <= 8

                    BEGIN

                            SELECT  @Part = @Part + 1,

                                    @PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),

                                    @Mask = CASE WHEN @Part = 2 THEN 8 ELSE 128 END

                            WHILE @Mask > 0

                                    BEGIN

                                            IF @PartValue & @Mask > 0

                                                    SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

                                            SELECT  @Bit = @Bit + 1,

                                                    @Mask = @Mask / 2

                                    END

                    END

            RETURN SIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)

    END

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Very cool... and, now you know why I wait a day or two before adding anything to the script library   Nice job, Peter! 

    I gotta learn to trust WHILE loops a bit more for speed instead of making derived tables in functions... this isn't the first time a WHILE loop has just beat the pants off a derived table.

    --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)
    Intro to Tally Tables and Functions

  • Thank you!

    I think we had the same discussion for the Luhn algorithm, right?

    With the @Multiple variable...

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Z'actly...

    --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)
    Intro to Tally Tables and Functions

  • Must be a difference in boxes we're running on... I don't come up with your's being 3 times faster, but the point about the WHILE loop method is still well taken on my part... thanks, Peter...

    --===== Declare a timer and do a little setup

    DECLARE @StartTime DATETIME

        SET NOCOUNT ON

    --===== Create some test data

         IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL

            DROP TABLE #MyHead

     SELECT CAST(CAST(N AS FLOAT(53))/100 AS BINARY(8)) AS BinaryFloatValue

       INTO #MyHead

       FROM Tally

    --===== Jeff's function (about 6 seconds)

        SET @StartTime = GETDATE()

     SELECT dbo.fConvertBinFloatToFloat(BinaryFloatValue) FROM #MyHead

      PRINT DATEDIFF(ms,@StartTime,GETDATE())

    GO

    --===== Declare a timer and do a little setup

    DECLARE @StartTime DATETIME

        SET NOCOUNT ON

    --===== Create some test data

         IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL

            DROP TABLE #MyHead

     SELECT CAST(CAST(N AS FLOAT(53))/100 AS BINARY(8)) AS BinaryFloatValue

       INTO #MyHead

       FROM Tally

    --===== Peter's function (about 4 seconds)

        SET @StartTime = GETDATE()

     SELECT dbo.fnBinaryFloat2Float(BinaryFloatValue) FROM #MyHead

      PRINT DATEDIFF(ms,@StartTime,GETDATE())

    All bet's are off if you use SET STATISTICS IO TIME ON... both take about a million years to execute, then

     

    --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)
    Intro to Tally Tables and Functions

  • It is 2 times.

    I average about 1.1 seconds for 11,000 test values with my suggestion and average about 2.3 seconds for you original code.

    Strange, I can't calculate simple percentage

     

     


    N 56°04'39.16"
    E 12°55'05.25"

  • To make the test right open 10 windows on QA and run the same script from all of them simultaneously.

    There are possibly more than one users in the system, right?

    That's where sharing resources takes its place.

    _____________
    Code for TallyGenerator

  • Nope... it's likely a batch job to be run only one batch at a time... but I do get your point.

    --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)
    Intro to Tally Tables and Functions

  • Well done, Jeff Moden and Peso. The function fnBinaryFloat2Float was just what I needed.

    Well, actually, I wanted a function to convert BINARY(4) to REAL. But by changing some of the constants and types, I easily produced the following.

    Note that the internal working is still in double-precision. Maybe this could be changed also.

    [font="Courier New"]

    CREATE FUNCTION dbo.fnBinaryReal2Real

    (

    @BinaryReal BINARY(4)

    )

    RETURNS REAL

    AS

    BEGIN

    DECLARE @Part TINYINT,

    @PartValue TINYINT,

    @Mask TINYINT,

    @Mantissa FLOAT,

    @Exponent SMALLINT,

    @Bit TINYINT,

    @Ln2 FLOAT,

    @IntValue INT

    SELECT @Part = 1,

    @Mantissa = 1,

    @Bit = 1,

    @Ln2 = LOG(2),

    @IntValue = CAST(@BinaryReal AS INT),

    @Exponent = (@IntValue & 0x7f800000) / EXP(23 * @Ln2)

    WHILE @Part <= 4

    BEGIN

    SELECT @Part = @Part + 1,

    @PartValue = CAST(SUBSTRING(@BinaryReal, @Part, 1) AS TINYINT),

    @Mask = CASE WHEN @Part = 2 THEN 64 ELSE 128 END

    WHILE @Mask > 0

    BEGIN

    IF @PartValue & @Mask > 0

    SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

    SELECT @Bit = @Bit + 1,

    @Mask = @Mask / 2

    END

    END

    RETURN SIGN(@IntValue) * @Mantissa * POWER(CAST(2.0 as FLOAT), @Exponent - 126)

    END

    [/font]

  • I've recoded Peso's function to provide improved performance. Using the benchmark posted by Jeff Moden (4/10/2007) run-time changed from 1.0 seconds to 0.16 seconds.

    [font="Courier New"]

    CREATE FUNCTION dbo.fnBinaryFloat2Float

    (

    @BinaryFloat BINARY(8)

    )

    RETURNS FLOAT

    AS

    BEGIN

    DECLARE @Mantissa FLOAT,

    @Exponent SMALLINT,

    @BigValue BIGINT,

    @Float2 FLOAT

    SELECT@Float2 = CAST(2.0 AS FLOAT),

    @BigValue = CAST(@BinaryFloat AS BIGINT),

    @Exponent = (@BigValue & 0x7ff0000000000000) / 0x0010000000000000,

    @Mantissa = 1.0 + (@BigValue & 0x000FFFFFFFFFFFFF) * POWER(@Float2, -52)

    RETURNSIGN(@BigValue) * @Mantissa * POWER(@Float2, @Exponent - 1023)

    END

    [/font]

  • Here is the alternative coding of the function dbo.fnBinaryReal2Real

    CREATE FUNCTION dbo.fnBinaryReal2Real

    (

    @BinaryFloat BINARY(4)

    )

    RETURNS REAL

    AS

    BEGIN

    DECLARE@Mantissa REAL,

    @Exponent SMALLINT,

    @IntValue INT,

    @Real2 REAL

    SELECT @Real2 = CAST(2.0 AS REAL),

    @IntValue = CAST(@BinaryFloat AS INT),

    @Exponent = (@IntValue & 0x7f800000) / 0x00800000,

    @Mantissa = 1.0 + (@IntValue & 0x007FFFFF) * POWER(@Real2, -23)

    RETURN SIGN(@IntValue) * @Mantissa * POWER(@Real2, @Exponent - 127)

    END

Viewing 15 posts - 16 through 30 (of 40 total)

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