February 5, 2013 at 8:07 am
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 🙁
February 6, 2013 at 12:59 pm
Markus S. Gallagher (2/5/2013)
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 🙁
Ah! Understood. Thank you for the feedback. Just to explain my question... it wasn't in defense of what I posted. I challenge anyone and everyone to nearly any claims of performance where a test to support such a claim has not been posted with the claim. It's usually not meant to be personal. it's meant to prevent the development of myths as so many myths have been formed.
Shifting gears, the rolks at RedGate made it so you can't delete posts because a whole lot of people were deleting their posts once they 1) had and answer to the post or 2) had bad mouthed just about everyone and needed to be held accountable in public for their actions. They (folks at RedGate) decided it was better to simply not be able to delete posts and to leave such a thing only up to official RedGate moderators (mostly Steve Jones).
--Jeff Moden
February 11, 2013 at 1:13 am
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))) END
FROM (
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 #BigNums
SELECT 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)
END
FROM #BigNums
SET STATISTICS TIME OFF
PRINT '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 END
FROM #BigNums
SET STATISTICS TIME OFF
PRINT '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))) END
FROM #BigNums
SET STATISTICS TIME OFF
DROP TABLE #BigNums
I get these timing results which just can't possibly be right. :w00t:
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2013 at 2:21 am
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)) END
FROM (
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 #BigNums
SELECT 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)
END
FROM #BigNums
SET STATISTICS TIME OFF
PRINT '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 END
FROM #BigNums
SET STATISTICS TIME OFF
PRINT '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))) END
FROM #BigNums
SET STATISTICS TIME OFF
PRINT '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)) END
FROM #BigNums
SET STATISTICS TIME OFF
PRINT 'Jeff - Revised WHEN'
SET STATISTICS TIME ON
SELECT @Hold=CASE
WHEN FLOOR(TestNum) = TestNum
THEN 0
ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1)
END
FROM #BigNums
SET STATISTICS TIME OFF
DROP 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2017 at 11:20 am
Easiest Way to find out.
-- For digits
Select Left(COLUMN_NAME, Case When CharIndex('.',COLUMN_NAME) = 0 Then 0 Else CharIndex('.',COLUMN_NAME)-1 END) as 'LeftPortion',
RIGHT(COLUMN_NAME, len(COLUMN_NAME) - CharIndex('.',COLUMN_NAME)) as 'RightPortion' From TABLE_NAME
--To find Max Lengths
Select COLUMN_NAME, MAX(LEN(Left(COLUMN_NAME, Case When Ind = 0 Then 0 Else Ind-1 END))) as LenBeforedecimal , MAX(LEN(Right(COLUMN_NAME, LEN(COLUMN_NAME) - Ind))) as afterdecimal From
(Select COLUMN_NAME, CHARINDEX('.', Cast(COLUMN_NAME as varchar(30))) as Ind From TABLE_NAME) a
Group BY COLUMN_NAME
May 23, 2017 at 7:16 pm
shanmukha.sqlbi - Monday, May 22, 2017 11:20 AMEasiest Way to find out.
-- For digits
Select Left(COLUMN_NAME, Case When CharIndex('.',COLUMN_NAME) = 0 Then 0 Else CharIndex('.',COLUMN_NAME)-1 END) as 'LeftPortion',
RIGHT(COLUMN_NAME, len(COLUMN_NAME) - CharIndex('.',COLUMN_NAME)) as 'RightPortion' From TABLE_NAME
--To find Max Lengths
Select COLUMN_NAME, MAX(LEN(Left(COLUMN_NAME, Case When Ind = 0 Then 0 Else Ind-1 END))) as LenBeforedecimal , MAX(LEN(Right(COLUMN_NAME, LEN(COLUMN_NAME) - Ind))) as afterdecimal From
(Select COLUMN_NAME, CHARINDEX('.', Cast(COLUMN_NAME as varchar(30))) as Ind From TABLE_NAME) a
Group BY COLUMN_NAME
You've missed a key part of the original request...
Keith Saynor - Tuesday, October 10, 2006 8:49 AMWhat I really want is a count of the number of digits following the decimal point ignoring the trailing zeros.
--Jeff Moden
May 23, 2017 at 7:50 pm
Not to mention that the original request was lame.
3.3 not equal to 3.30
10/3 = 3.3 - correct
10/3 = 3.30 - wrong.
Number of decimals to be displayed is defined not by the number of trailing zeros.
Some of those trailing zeros might be significant and should not be removed from the result.
_____________
Code for TallyGenerator
May 23, 2017 at 8:25 pm
May 23, 2017 at 8:47 pm
May 23, 2017 at 8:52 pm
Nice try but prone to silent errors (run the code below to see) and uses almost 3 times more CPU than Dwain's Redux...
declare @S varchar(20)
begin
set @S = '10.000000'
select len(@S) - charindex('.',@s,1) +1 - patindex('%[1-9]%',reverse(@s))
end
--Jeff Moden
May 23, 2017 at 8:56 pm
Jamie Ingram-729524 - Tuesday, May 23, 2017 8:47 PM
... and please stop deleting your posts. It's ok to make a mistake and correct it if it needs correction. When I make mistakes, I'll leave it and usually cross it out with an explanation as to what my mistake was so that others might keep from making the same mistake.
--Jeff Moden
May 23, 2017 at 9:04 pm
Sergiy - Tuesday, May 23, 2017 7:50 PMNot to mention that the original request was lame.3.3 not equal to 3.3010/3 = 3.3 - correct10/3 = 3.30 - wrong.Number of decimals to be displayed is defined not by the number of trailing zeros.Some of those trailing zeros might be significant and should not be removed from the result.
Agreed... Truly, a significant observation on your part. 😉
For anyone that doesn't understand how 3.3 and 3.30 are not the same, in the world of (for example) engineering, the number of decimal places is the accuracy to which something was measured and is frequently followed by a +/- measurement to indicate the level of error where the number of decimal places is also significant even if they are all zeros.
Also, in Sergiy's good example, 10/3 can be expressed as 3.3 if a single decimal place is used but it will also be 3.33 and not 3.30 if two decimal places are used. {Edit} Well, unless you're using the 3rd parameter of ROUND(), 😉
--Jeff Moden
May 23, 2017 at 9:34 pm
Jeff Moden - Tuesday, May 23, 2017 7:16 PMshanmukha.sqlbi - Monday, May 22, 2017 11:20 AMEasiest Way to find out.
-- For digits
Select Left(COLUMN_NAME, Case When CharIndex('.',COLUMN_NAME) = 0 Then 0 Else CharIndex('.',COLUMN_NAME)-1 END) as 'LeftPortion',
RIGHT(COLUMN_NAME, len(COLUMN_NAME) - CharIndex('.',COLUMN_NAME)) as 'RightPortion' From TABLE_NAME
--To find Max Lengths
Select COLUMN_NAME, MAX(LEN(Left(COLUMN_NAME, Case When Ind = 0 Then 0 Else Ind-1 END))) as LenBeforedecimal , MAX(LEN(Right(COLUMN_NAME, LEN(COLUMN_NAME) - Ind))) as afterdecimal From
(Select COLUMN_NAME, CHARINDEX('.', Cast(COLUMN_NAME as varchar(30))) as Ind From TABLE_NAME) a
Group BY COLUMN_NAMEYou've missed a key part of the original request...
Keith Saynor - Tuesday, October 10, 2006 8:49 AMWhat I really want is a count of the number of digits following the decimal point ignoring the trailing zeros.
Also, consider the "easiness" of the following replacement for your first query and then understand how the mathematical simplification also made it use 1/4th the amount of CPU time. (column and table name from Dwain's test data setup)
--===== For Digits
SELECT LeftPortion = CONVERT(VARCHAR(10),CONVERT(INT,TestNum))
,RightPortion = STUFF(ABS(TestNum % 1),1,2,'')
FROM #BigNums
;
If you avoid the character base conversions altogether, the following runs in about a 10th of the time.
--===== For Digits
SELECT IntegerPortion = CONVERT(INT,TestNum)
,DecimalPortion = ABS(TestNum % 1)
FROM #BigNums
;
I might, however, get flamed on that last one because it returns a zero for the DecimalPortion if the datatype is an INT.
--Jeff Moden
May 24, 2017 at 8:27 am
Jeff Moden - Tuesday, May 23, 2017 9:34 PMJeff Moden - Tuesday, May 23, 2017 7:16 PMshanmukha.sqlbi - Monday, May 22, 2017 11:20 AMEasiest Way to find out.
-- For digits
Select Left(COLUMN_NAME, Case When CharIndex('.',COLUMN_NAME) = 0 Then 0 Else CharIndex('.',COLUMN_NAME)-1 END) as 'LeftPortion',
RIGHT(COLUMN_NAME, len(COLUMN_NAME) - CharIndex('.',COLUMN_NAME)) as 'RightPortion' From TABLE_NAME
--To find Max Lengths
Select COLUMN_NAME, MAX(LEN(Left(COLUMN_NAME, Case When Ind = 0 Then 0 Else Ind-1 END))) as LenBeforedecimal , MAX(LEN(Right(COLUMN_NAME, LEN(COLUMN_NAME) - Ind))) as afterdecimal From
(Select COLUMN_NAME, CHARINDEX('.', Cast(COLUMN_NAME as varchar(30))) as Ind From TABLE_NAME) a
Group BY COLUMN_NAMEYou've missed a key part of the original request...
Keith Saynor - Tuesday, October 10, 2006 8:49 AMWhat I really want is a count of the number of digits following the decimal point ignoring the trailing zeros.
Also, consider the "easiness" of the following replacement for your first query and then understand how the mathematical simplification also made it use 1/4th the amount of CPU time. (column and table name from Dwain's test data setup)
--===== For Digits
SELECT LeftPortion = CONVERT(VARCHAR(10),CONVERT(INT,TestNum))
,RightPortion = STUFF(ABS(TestNum % 1),1,2,'')
FROM #BigNums
;If you avoid the character base conversions altogether, the following runs in about a 10th of the time.
--===== For Digits
SELECT IntegerPortion = CONVERT(INT,TestNum)
,DecimalPortion = ABS(TestNum % 1)
FROM #BigNums
;
I might, however, get flamed on that last one because it returns a zero for the DecimalPortion if the datatype is an INT.
I agree with you Jeff. I haven't took that into consideration. I should work around on my queries considering the performance.
May 24, 2017 at 8:47 am
Thanks for the feedback, shanmukha.sqlbi .
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply