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) -
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) -
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) -
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) -
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) -
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) -
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 39 total)
You must be logged in to reply to this topic. Login to reply