October 2, 2007 at 12:20 pm
Here's what I used:
-- Initialization common to both methods.
Declare @Original decimal( 38, 12 ),
@Result decimal( 38, 0 ); -- Doesn't need scale, only precision
Set @Original = 09876504321.1203456; -- This would be, say, an input parameter
Declare @MaxLoop int;
set @MaxLoop = 1000000;
while @MaxLoop > 0
begin
-- Uncomment one method or the other.
-- Method 1:
-- Set @Result = Replace( RTrim( Replace( Replace( Convert( varchar, @Original ), '.', '' ), '0', ' ' ) ), ' ', '0' );
-- Method 2:
-- Set @Result = Floor( @Original );
-- While @Result < @Original
-- begin
-- Set @Original = @Original * 10;
-- Set @Result = floor( @Original );
-- end--while
Set @MaxLoop = @MaxLoop - 1;
end--loop
select @Result as Result;
I used the Client Processing Time in Statistics. There is no I/O except to display the result at the end so essentially all time is processing time.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 2, 2007 at 10:27 pm
You can touch it. It won't hurt you.
Heh... and folks think Serqiy is a smartass??? :laugh:
Anyway, point well taken and I guess I have to eat a little crow on this one :blush: That's one of the few times I've seen a loop beat an all function solution and for the reasons you stated...
But, if you're going to write a loop, would you at least write one that's fast? 😛
Here's the hands-free no-modification-required test harness I built from your code example... take a peek at the 3rd method :alien:
--=====================================================================
-- Setup the environment common to all tests
--=====================================================================
--===== Suppress the auto-display of rowcounts
SET NOCOUNT ON
--===== Declare the necessary local variables
DECLARE @Original DECIMAL(38,12) --The original decimal value
DECLARE @Result DECIMAL(38, 0) --The desired result
DECLARE @MaxLoop INT --Number of times to perform the loop
DECLARE @Counter INT --General purpose loop counter
DECLARE @StartTime DATETIME --To measure duration for each test
--===== Presets
SET @Original = 09876504321.1203456
SET @MaxLoop = 1000000
PRINT 'Original value as stored...'
PRINT @Original
PRINT REPLICATE('-',78)
--=====================================================================
-- Test Method 1: Character based manipulation
--=====================================================================
PRINT 'Test Method 1: Character based manipulation'
--===== Presets
SET @Counter = 1
SET @StartTime = GETDATE()
--===== Run the test
WHILE @Counter <= @MaxLoop
BEGIN --Test Code
---------------------------------------------------------------
SET @Result = REPLACE(RTRIM(REPLACE(REPLACE(CONVERT(VARCHAR,@Original ),'.','' ),'0',' ')),' ','0')
---------------------------------------------------------------
SET @Counter = @Counter + 1
END
--===== Display the calculated duration per loop
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))
+ ' Microseconds per loop'
--===== Print the result
PRINT CAST(@Result AS VARCHAR(40)) + ' Result'
PRINT REPLICATE('-',78)
--=====================================================================
-- Test Method 2: Typical Numeric based loop
--=====================================================================
PRINT 'Test Method 2: Typical Numeric based loop'
--===== Presets
SET @Counter = 1
SET @StartTime = GETDATE()
--===== Run the test
WHILE @Counter <= @MaxLoop
BEGIN --Test Code
---------------------------------------------------------------
SET @Result = FLOOR(@Original);
WHILE @Result < @Original
BEGIN
SET @Original = @Original * 10
SET @Result = FLOOR(@Original);
END
---------------------------------------------------------------
SET @Counter = @Counter + 1
END
--===== Display the calculated duration per loop
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))
+ ' Microseconds per loop'
--===== Print the result
PRINT CAST(@Result AS VARCHAR(40)) + ' Result'
PRINT REPLICATE('-',78)
--=====================================================================
-- Test Method 3: Not-so-typical Numeric based loop
--=====================================================================
PRINT 'Test Method 3: Not-so-typical Numeric based loop'
--===== Presets
SET @Counter = 1
SET @StartTime = GETDATE()
--===== Run the test
WHILE @Counter <= @MaxLoop
BEGIN --Test Code
---------------------------------------------------------------
WHILE ISNULL(@Result,0) < @Original
SELECT @Original = @Original * 10,
@Result = FLOOR(@Original)
---------------------------------------------------------------
SET @Counter = @Counter + 1
END
--===== Display the calculated duration per loop
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))
+ ' Microseconds per loop'
--===== Print the result
PRINT CAST(@Result AS VARCHAR(40)) + ' Result'
PRINT REPLICATE('-',78)
...and, here's the results...
Original value as stored...
9876504321.120345600000
------------------------------------------------------------------------------
Test Method 1: Character based manipulation
22.436000000000 Microseconds per loop
98765043211203456 Result
------------------------------------------------------------------------------
Test Method 2: Typical Numeric based loop
11.220000000000 Microseconds per loop
98765043211203456 Result
------------------------------------------------------------------------------
Test Method 3: Not-so-typical Numeric based loop
8.436000000000 Microseconds per loop
98765043211203456 Result
------------------------------------------------------------------------------
--Jeff Moden
October 2, 2007 at 10:33 pm
As I sai Jeff, there's something to be said for ingenuity and straight math operations. It's worth mentioning that math ops will almost always beat the crap out of 15 different conversions / string ops like I had to do to get the results. It's just common sens. :w00t:
But I agree that it seems a bit unnatural. It's like having to say that a cursor is good in production code :hehe:.
October 2, 2007 at 10:44 pm
Yeah, huh? Using loops comes highly unnatural to me. 😀 Goes against my data-troll grain.
The other place I saw it was with a splitter function that used a WHILE loop. The way the guy did it, it actually beat the Tally table method for single rows... still couldn't beat the Tally table method for splitting a whole table with no function, but I was definitely impressed.
--Jeff Moden
October 3, 2007 at 11:00 am
Sorry, Jeff. You got the third method to operate a little faster by removing the setup just before entering the loop. This is a crucial step. Without it, the method gives the correct answer only for fractional values. For whole numbers (such as 12345.0) the method, by performing the multiplication first, gives an answer that is off by a factor of 10 (123450) while methods 1 & 2 give the correct answer.
It's frustrating, I know. We could write some smoking code if only the users would be satisfied with getting a correct answer most of the time. That silly every single time constraint can be a real nuisance.:rolleyes:
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 3, 2007 at 4:02 pm
OK. I've gotten around my the problem with my earlier submission where I was restricted to 9 digits to the left of the decimal, and 6 digits to the right of the decimal.
What I also like about this is that is is a mathematical solution. No turning numbers into characters.
Let me know what you think:
Declare @Original decimal(25,10)
Set @Original = 09876504321.1203456
select cast(@Original*power(10,len(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1) as decimal(20,0))
-- In case you want a readable version of what the above is doing:
select @Original, --Original number
cast(@Original as decimal(15,0)), --Original number without the decimals
cast(@Original-cast(@Original as decimal(15,0)) as float(53)), --Just the decimals
len(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1, --How many decimal places
power(10,len(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1), --10 to the power of the number of decimal places
cast(@Original*power(10,len(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1) as decimal(20,0)) --Multiply away the decimals from the original
********
EDIT: Dang it. Hold on a sec. I have a problem with my count of the decimal places. Working on a fix.
EDIT #2: Ah the best laid plans... This still has a restriction of 6 decimal places. Any more than that and I can't properly calculate the power of 10 I need. Sorry for the false alarm. --SF
Kindest Regards,
--SF
(SELECT COALESCE(Phlogiston,Caloric,DarkMatter) as Construct FROM Science.dbo.Theory)
October 3, 2007 at 11:06 pm
Tomm Carr (10/3/2007)
Sorry, Jeff. You got the third method to operate a little faster by removing the setup just before entering the loop. This is a crucial step. Without it, the method gives the correct answer only for fractional values. For whole numbers (such as 12345.0) the method, by performing the multiplication first, gives an answer that is off by a factor of 10 (123450) while methods 1 & 2 give the correct answer.It's frustrating, I know. We could write some smoking code if only the users would be satisfied with getting a correct answer most of the time. That silly every single time constraint can be a real nuisance.:rolleyes:
What in the hell are you talking about?
Original value as stored...
12345.000000000000
------------------------------------------------------------------------------
Test Method 1: Character based manipulation
20.126000000000 Microseconds per loop
12345 Result
------------------------------------------------------------------------------
Test Method 2: Typical Numeric based loop
10.390000000000 Microseconds per loop
12345 Result
------------------------------------------------------------------------------
Test Method 3: Not-so-typical Numeric based loop
8.406000000000 Microseconds per loop
12345 Result
------------------------------------------------------------------------------
--Jeff Moden
October 4, 2007 at 10:21 am
Oh, sorry. :blush: I forgot to mention that it would seem to work because @Result is left at the correct answer from the method that came before it. So when working with whole numbers, Method 3 never enters the loop because @Result starts out equal to @Original. However, it you reset @Result to 0 or null between the two methods, you will see what I mean. Actually, you should reset @Original also as method 2 modifies it.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 4, 2007 at 5:21 pm
Thanks for the heads up, Tomm... I'll check it out...
--Jeff Moden
October 4, 2007 at 5:37 pm
Wow... I made a mess of that one :blush: It's only fast if it works right, huh 😀
Folks, don't use method 3... Tomm's right... I've got a nasty bug in it... I see what I can do to fix it, but right now it's wrong and so am I :blush:...
--Jeff Moden
October 5, 2007 at 12:54 pm
Well, it's not quite ideal as I can't use numbers with 10 decimal places. My method is restricted to 9 digits after the decimal place, but that isn't too shabby in my opinion.
Here is my solution (similar to my earlier ones, but this, I believe, actually works):
Declare @Original decimal(25,10)
Set @Original = 09876504321.120345678 -- 0987654321123456.012345678
select cast(@Original*power(10,len(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0))
-- Below steps out what the above is doing
select @Original, -- Original number
left(@Original,charindex('.',cast(@Original as varchar(30)))), -- Original number without the decimals
cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14)), -- Just the decimals
Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))), -- Just the decimals, but reversed
cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)), -- Get rid of unneeded 0s
len(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0))), -- How many digits, will use this as an exponent
power(10,len(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)))), -- What do I need to multiply the original number by?
cast(@Original*power(10,len(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0)) --Eureka!
Any comments? Criticisms?
Kindest Regards,
--SF
(SELECT COALESCE(Phlogiston,Caloric,DarkMatter) as Construct FROM Science.dbo.Theory)
October 5, 2007 at 1:18 pm
And for those of you who, unlike me, remember that you can use bigint if int is too restrictive, this works as well as my last post, and doesn't have the left() or charindex() in it. Which makes this one, in my opinion, better than my last one.
Declare @Original decimal(25,10)
Set @Original = 09876504321.120345678 -- 0987654321123456.012345678
select cast(@Original*power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0))
-- Again, stepping it through
select @Original, -- Original number
left(@Original,charindex('.',cast(@Original as varchar(30)))), -- Original number without the decimals
@Original-(cast(@Original as bigint)), -- Just the decimals
Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))), -- Just the decimals, but reversed
cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)), -- Get rid of unneeded 0s
len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0))), -- How many digits, will use this as an exponent
power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))), -- What do I need to multiply the original number by?
cast(@Original*power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0)) --Eureka!
--SF
Kindest Regards,
--SF
(SELECT COALESCE(Phlogiston,Caloric,DarkMatter) as Construct FROM Science.dbo.Theory)
October 7, 2007 at 7:58 pm
The error in Jeff's code is easy to fix by setting @Result to FLOOR(@Original) before the loop, however this makes it identical to test 2 except for the fact that the two variables are set in a single statement instead of two.
The test harness is inaccurate as @Original and @Result need to be reset at the start of each test loop otherwise the algorithms in tests 2 and 3 don't get run.
Finally, in test 1 the result is a varchar that is stored in a decimal variable so an implicit conversion is performed, only to be converted back to a varchar for printing. I changed the test harness to store the result in a varchar.
The results are interesting...
I also tried the code by S. Frog however it doesn't work for the value 12345.0 (same problem as Jeff's original code).
--=====================================================================
-- Setup the environment common to all tests
--=====================================================================
--===== Suppress the auto-display of rowcounts
SET NOCOUNT ON
--===== Declare the necessary local variables
DECLARE @UnolteredOriginal DECIMAL(38,12) --The original decimal value
DECLARE @Original DECIMAL(38,12) --The original decimal value
DECLARE @Result DECIMAL(38, 0) --The desired result
DECLARE @ResultStr VARCHAR(200) --The desired result
DECLARE @MaxLoop INT --Number of times to perform the loop
DECLARE @Counter INT --General purpose loop counter
DECLARE @StartTime DATETIME --To measure duration for each test
--===== Presets
SET @UnolteredOriginal = 09876504321.1203456
SET @MaxLoop = 1000000
PRINT 'Original value as stored...'
PRINT @UnolteredOriginal
PRINT REPLICATE('-',78)
--=====================================================================
-- Test Method 1: Character based manipulation
--=====================================================================
PRINT 'Test Method 1: Character based manipulation'
--===== Presets
SET @Original = @UnolteredOriginal
SET @Counter = 1
SET @StartTime = GETDATE()
--===== Run the test
WHILE @Counter <= @MaxLoop
BEGIN --Test Code
---------------------------------------------------------------
SET @ResultStr = REPLACE(RTRIM(REPLACE(REPLACE(CONVERT(VARCHAR,@Original ),'.','' ),'0',' ')),' ','0')
---------------------------------------------------------------
SET @Counter = @Counter + 1
END
--===== Display the calculated duration per loop
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))
+ ' Microseconds per loop'
--===== Print the result
PRINT @ResultStr + ' Result'
PRINT REPLICATE('-',78)
--=====================================================================
-- Test Method 2: Typical Numeric based loop
--=====================================================================
SET @Result = NULL
PRINT 'Test Method 2: Numeric based loop'
--===== Presets
SET @Counter = 1
SET @StartTime = GETDATE()
--===== Run the test
WHILE @Counter <= @MaxLoop
BEGIN --Test Code
SELECT @Original = @UnolteredOriginal, @Result = FLOOR(@Original)
---------------------------------------------------------------
SET @Result = FLOOR(@Original);
WHILE @Result < @Original
BEGIN
SET @Original = @Original * 10
SET @Result = FLOOR(@Original);
END
---------------------------------------------------------------
SET @Counter = @Counter + 1
END
--===== Display the calculated duration per loop
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))
+ ' Microseconds per loop'
--===== Print the result
PRINT CAST(@Result AS VARCHAR(40)) + ' Result'
PRINT REPLICATE('-',78)
--=====================================================================
-- Test Method 3: Numeric based loop
--=====================================================================
SET @Result = NULL
PRINT 'Test Method 3: Not-so-typical Numeric based loop'
--===== Presets
SET @Counter = 1
SET @StartTime = GETDATE()
--===== Run the test
WHILE @Counter <= @MaxLoop
BEGIN --Test Code
SELECT @Original = @UnolteredOriginal, @Result = FLOOR(@Original)
---------------------------------------------------------------
WHILE ISNULL(@Result, 0) < @Original
SELECT @Original = @Original * 10, @Result = FLOOR(@Original)
---------------------------------------------------------------
SET @Counter = @Counter + 1
END
--===== Display the calculated duration per loop
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))
+ ' Microseconds per loop'
--===== Print the result
PRINT CAST(@Result AS VARCHAR(40)) + ' Result'
PRINT REPLICATE('-',78)
Original value as stored...
9876504321.120345600000
------------------------------------------------------------------------------
Test Method 1: Character based manipulation
7.843000000000 Microseconds per loop
98765043211203456 Result
------------------------------------------------------------------------------
Test Method 2: Numeric based loop
32.410000000000 Microseconds per loop
98765043211203456 Result
------------------------------------------------------------------------------
Test Method 3: Numeric based loop
25.733000000000 Microseconds per loop
98765043211203456 Result
------------------------------------------------------------------------------
Original value as stored...
12345.000000000000
------------------------------------------------------------------------------
Test Method 1: Character based manipulation
7.330000000000 Microseconds per loop
12345 Result
------------------------------------------------------------------------------
Test Method 2: Numeric based loop
4.733000000000 Microseconds per loop
12345 Result
------------------------------------------------------------------------------
Test Method 3: Numeric based loop
4.593000000000 Microseconds per loop
12345 Result
------------------------------------------------------------------------------
October 8, 2007 at 8:57 am
Fixed for 12345.0
Declare @Original decimal(25,10)
Set @Original = 12345.0 -- 09876504321.120345678
IF (@Original-(cast(@Original as bigint))) = 0
select cast(@Original as bigint)
ELSE
select cast(@Original*power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0))
Not quite as elegant as I had hoped, but it works.
-S.F.
Kindest Regards,
--SF
(SELECT COALESCE(Phlogiston,Caloric,DarkMatter) as Construct FROM Science.dbo.Theory)
October 8, 2007 at 9:16 am
DECLARE @Original DECIMAL(10, 5)
SET @Original = 1078.734
SELECT @Original,
REPLACE(REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(STR(@Original, 15, 5), ' ', '#'), '0', ' ')), ' ', '0'), '#', ''), '.', '')
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply