Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Count Decimal Places Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, October 25, 2006 6:57 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:00 PM Points: 35,967, Visits: 30,258
 Ok... this takes 2 seconds longer (1,000,000 rows in 23 seconds instead of 21)... had to work around the "zero domain" on the LOG10 function to get this to work properly for whole numbers...DECLARE @TestNum DECIMAL(38,15)    SET @TestNum = 99   --99.0000 SELECT CASE            WHEN FLOOR(REVERSE(ABS(@TestNum))) = 0.0            THEN 0            ELSE FLOOR(LOG10(REVERSE(ABS(@TestNum)))+1)        ENDIt does NOT support FLOAT... (the original problem description was based on the DECIMAL datatype so I think we're ok there)... Float does wierd things when you throw a REVERSE on it.  If anyone needs a decimal place counter that works on FLOAT, we'll have to take a different tact... --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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #318166
 Posted Thursday, October 26, 2006 7:46 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 9:49 AM Points: 6,798, Visits: 6,272
 10-PATINDEX('%[^0]%',REVERSE(RIGHT(CAST(mynumber as varchar),9))+'1') 1,000,000 rows <=10 secs  Far away is close at hand in the images of elsewhere.Anon.
Post #318298
 Posted Thursday, October 26, 2006 5:20 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:00 PM Points: 35,967, Visits: 30,258
 Very cool, David... fast as all get out... but try this... obviously, we have to know exactly what the scale of the decimal places is to use it or we come up with the wrong answer...DECLARE @TestNum DECIMAL(38,15)    SET @TestNum = 99.123456789012345 SELECT 10-PATINDEX('%[^0]%',REVERSE(RIGHT(CAST(@TestNum as varchar),9))+'1') However... you gave me one heck of an idea... the following takes a bit more time (1,000,000 records in about 12 seconds)...DECLARE @TestNum DECIMAL(38,15)    SET @TestNum = 90 --99.123456780000000 --0.123456780000000 --99.1 --90SELECT CHARINDEX('.',REVERSE(@TestNum))      -PATINDEX('%[^0]%',REVERSE(@TestNum))...the neat thing about it is that you don't need to know the precision or scale of the decimal column... it figures it out... --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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #318572
 Posted Tuesday, February 05, 2013 3:06 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, February 23, 2013 1:26 PM Points: 3, Visits: 39
 Slight improvement that works for decimal datatype:CASE WHEN FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 END
Post #1415675
 Posted Tuesday, February 05, 2013 7:52 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:00 PM Points: 35,967, Visits: 30,258
 Markus S. Gallagher (2/5/2013)Slight improvement that works for decimal datatype:CASE WHEN FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 ENDSlight improvement how? Is it faster? --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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1415854
 Posted Tuesday, February 05, 2013 8:07 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, February 23, 2013 1:26 PM Points: 3, Visits: 39
 actually no, i posted this answer before seeing the second page on the forum where you already improved on your initial answer so you can just ignore my previous post.for some reason i don't seem to be able to delete my posts
Post #1415872
 Posted Wednesday, February 06, 2013 12:59 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:00 PM Points: 35,967, Visits: 30,258
Post #1416700
 Posted Monday, February 11, 2013 1:13 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 6:57 PM Points: 3,590, Visits: 5,099
 I know I must be doing something wrong here so will somebody please check me?` SELECT TestNum, Jeff=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) END ,Markus=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 END ,Dwain=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) ENDFROM ( SELECT CAST(99 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111111111111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(0.1 AS DECIMAL(38,15))) Nums(TestNum)CREATE TABLE #BigNums (TestNum DECIMAL(38,15))INSERT INTO #BigNumsSELECT 1.* CHECKSUM(NEWID()) / POWER(10, ABS(CHECKSUM(NEWID())) % 10)FROM ( SELECT TOP 1000000 1 FROM sys.all_columns a, sys.all_columns b )Tally(n)DECLARE @Hold DECIMAL(38,15)PRINT 'Jeff'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Markus'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Dwain'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) ENDFROM #BigNumsSET STATISTICS TIME OFFDROP TABLE #BigNums`I get these timing results which just can't possibly be right. `Jeff SQL Server Execution Times: CPU time = 3339 ms, elapsed time = 3411 ms.Markus SQL Server Execution Times: CPU time = 4227 ms, elapsed time = 4291 ms.Dwain SQL Server Execution Times: CPU time = 2028 ms, elapsed time = 2036 ms.`Edit: Fixed the Tally table I used to set up the test harness to be SQL 2000 compatible (I think). My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1418241
 Posted Monday, February 11, 2013 2:21 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 6:57 PM Points: 3,590, Visits: 5,099
 Then again, there's always some schmo that's going to come along and try to beat it.` SELECT TestNum, Jeff=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) END ,Markus=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 END ,Dwain=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) END ,DwainRedux=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(TestNum - CAST(FLOOR(TestNum) AS DECIMAL)) AS DECIMAL)) ENDFROM ( SELECT CAST(99 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(-99.11111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(-99.111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111111111111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(0.1 AS DECIMAL(38,15))) Nums(TestNum)CREATE TABLE #BigNums (TestNum DECIMAL(38,15))INSERT INTO #BigNumsSELECT 1.* CHECKSUM(NEWID()) / POWER(10, ABS(CHECKSUM(NEWID())) % 10)FROM ( SELECT TOP 1000000 1 FROM sys.all_columns a, sys.all_columns b )Tally(n)DECLARE @Hold DECIMAL(38,15)PRINT 'Jeff'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Markus'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Dwain'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Dwain Redux'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(TestNum - CAST(FLOOR(TestNum) AS DECIMAL)) AS DECIMAL)) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Jeff - Revised WHEN'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) ENDFROM #BigNumsSET STATISTICS TIME OFFDROP TABLE #BigNums`Latest speed results:`Jeff SQL Server Execution Times: CPU time = 3338 ms, elapsed time = 3408 ms.Markus SQL Server Execution Times: CPU time = 4337 ms, elapsed time = 4369 ms.Dwain SQL Server Execution Times: CPU time = 2012 ms, elapsed time = 2071 ms.Dwain Redux SQL Server Execution Times: CPU time = 1888 ms, elapsed time = 1928 ms.Jeff - Revised WHEN SQL Server Execution Times: CPU time = 2293 ms, elapsed time = 2362 ms.`It appears that most of the speed boost was a result of the revision to the WHEN clause. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1418261

 Permissions