Addition Of Digits

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  August 6, 2012 at 3:50 am

  #1521919

  Cadavre (8/6/2012)

  ---

  Mark-101232 (8/6/2012)

  ---

  Million row test for three of the solutions. Results are interesting/surpising. Perhaps some of you folks could check this and run it.

  Celko forgot to account for the REPLACE "bug".

  Change his code to this: -

  DECLARE @T INT = 985;

  SELECT LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (@T AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'));

  Then take another look at your test results: -

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  -- This is NOT a part of the solution.

  IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL

  DROP TABLE #Test

  ;

  --===== Create and populate the test table.

  -- This is NOT a part of the solution.

  SELECT TOP (1000000)

  TestID = IDENTITY(INT,1,1),

  TestNumber = ABS(CHECKSUM(NEWID()))

  INTO #Test

  FROM sys.all_columns ac1

  CROSS JOIN sys.all_columns ac2

  ;

  --===== Add the expected PK

  ALTER TABLE #Test

  ADD PRIMARY KEY CLUSTERED (TestID)

  ;

  -----------------------------------------------------------------------------

  PRINT '========== SomewhereSomehow ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

  select @T = sum(convert(int,substring(convert(varchar(10),TestNumber),n,1)))

  from nums

  CROSS JOIN #Test

  where n <= len(convert(varchar(10),TestNumber))

  GROUP BY TestNumber;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) ,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== Mark ====================================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  WITH Tens(Pos,Val) AS (

  SELECT 1, 1 UNION ALL

  SELECT 2, 10 UNION ALL

  SELECT 3, 100 UNION ALL

  SELECT 4, 1000 UNION ALL

  SELECT 5, 10000 UNION ALL

  SELECT 6, 100000 UNION ALL

  SELECT 7, 1000000 UNION ALL

  SELECT 8, 10000000 UNION ALL

  SELECT 9, 100000000 UNION ALL

  SELECT 10,1000000000)

  SELECT @T = SUM((TestNumber / Val) % 10)

  FROM Tens

  CROSS JOIN #Test

  WHERE Val<=TestNumber

  GROUP BY TestNumber;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== IMPROVED CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  ========== SomewhereSomehow ===========================================

  SQL Server Execution Times:

  CPU time = 28155 ms, elapsed time = 8756 ms.

  ========== CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 24343 ms, elapsed time = 24385 ms.

  ========== Mark =====================================================

  SQL Server Execution Times:

  CPU time = 19921 ms, elapsed time = 6877 ms.

  ========== IMPROVED CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 3594 ms, elapsed time = 3619 ms.

  ---

  --edit--

  Found the link to the replace "bug"

  That explains a lot, thanks!

  Also the CELKO solution can be improved a bit by removing the REPLACE(...,'1', '#') - the code is simply counting characters so there's no point changing 1's to #'s.

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Sumit Rastogi

  SSC Eights!

  Points: 838

  More actions

  August 6, 2012 at 3:51 am

  #1521920

  declare @string varchar(max) = '25475675675675675675675675675587876865674553334645647656786867879789780890890789676565674545634343453445675756756786867897978978078997896785657454563445345234534645675678768997807867567456342323167567567567567567567567567567567567567567567665756756756756756756756756756756756756756767876879789797287686782567345345289789797278978978978979245645622222245645622222222435645645645646364564564564564564564564564564564564564564564564562'

  declare @s-2 varchar(1)

  declare @totalcount int = 0

  declare @count int

  set @count = LEN(@string)

  while (@count != 0)

  begin

  set @count = @count - 1

  set @s-2=convert(int,left(@string, 1))

  set @string = RIGHT(@string, len(@string)-1)

  set @totalcount = @totalcount + @s-2

  end

  print convert(varchar,@totalcount)

  Thanks,

  Sumit Rastogi

• SomewhereSomehow

  SSCommitted

  Points: 1586

  More actions

  August 6, 2012 at 4:18 am

  #1521943

  Cadavre,

  Thx for the testing scripts! Especially pointing a bug with replace function (closed as by design).

  I have quite similar results

  ========== SomewhereSomehow ===========================================

  SQL Server Execution Times:

  CPU time = 19813 ms, elapsed time = 5501 ms.

  ========== CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 20437 ms, elapsed time = 20529 ms.

  ========== Mark =====================================================

  SQL Server Execution Times:

  CPU time = 12891 ms, elapsed time = 3846 ms.

  ========== IMPROVED CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 2859 ms, elapsed time = 2853 ms.

  ---

  I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
  Blog: http://somewheresomehow.ru[/url]
  Twitter: @SomewereSomehow

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 6, 2012 at 4:53 am

  #1521955

  SomewhereSomehow (8/6/2012)

  ---

  Cadavre,

  Thx for the testing scripts! Especially pointing a bug with replace function (closed as by design).

  I have quite similar results

  ========== SomewhereSomehow ===========================================

  SQL Server Execution Times:

  CPU time = 19813 ms, elapsed time = 5501 ms.

  ========== CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 20437 ms, elapsed time = 20529 ms.

  ========== Mark =====================================================

  SQL Server Execution Times:

  CPU time = 12891 ms, elapsed time = 3846 ms.

  ========== IMPROVED CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 2859 ms, elapsed time = 2853 ms.

  Mark's testing scripts, I just improved Celko's solution by taking into account the replace "bug" 😉

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  August 6, 2012 at 3:47 pm

  #1522322

  Yet another version and result run on really slow laptop

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  -- This is NOT a part of the solution.

  IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL

  DROP TABLE #Test

  ;

  --===== Create and populate the test table.

  -- This is NOT a part of the solution.

  SELECT TOP (1000000)

  TestID = IDENTITY(INT,1,1),

  TestNumber = ABS(CHECKSUM(NEWID()))

  INTO #Test

  FROM sys.all_columns ac1

  CROSS JOIN sys.all_columns ac2

  ;

  --===== Add the expected PK

  ALTER TABLE #Test

  ADD PRIMARY KEY CLUSTERED (TestID)

  ;

  -----------------------------------------------------------------------------

  PRINT '========== SomewhereSomehow ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

  select @T = sum(convert(int,substring(convert(varchar(10),TestNumber),n,1)))

  from nums

  CROSS JOIN #Test

  where n <= len(convert(varchar(10),TestNumber))

  GROUP BY TestNumber;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) ,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== Mark ====================================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  WITH Tens(Pos,Val) AS (

  SELECT 1, 1 UNION ALL

  SELECT 2, 10 UNION ALL

  SELECT 3, 100 UNION ALL

  SELECT 4, 1000 UNION ALL

  SELECT 5, 10000 UNION ALL

  SELECT 6, 100000 UNION ALL

  SELECT 7, 1000000 UNION ALL

  SELECT 8, 10000000 UNION ALL

  SELECT 9, 100000000 UNION ALL

  SELECT 10,1000000000)

  SELECT @T = SUM((TestNumber / Val) % 10)

  FROM Tens

  CROSS JOIN #Test

  WHERE Val<=TestNumber

  GROUP BY TestNumber;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== IMPROVED CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== IMPROVED CELKO 2==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== Mark REWRITE====================================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = (TestNumber % 10) +

  ((TestNumber/10) % 10) +

  ((TestNumber/100) % 10) +

  ((TestNumber/1000) % 10) +

  ((TestNumber/10000) % 10) +

  ((TestNumber/100000) % 10) +

  ((TestNumber/1000000) % 10) +

  ((TestNumber/10000000) % 10) +

  ((TestNumber/100000000) % 10) +

  ((TestNumber/1000000000) % 10)

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  ========== SomewhereSomehow ===========================================

  SQL Server Execution Times:

  CPU time = 62000 ms, elapsed time = 58485 ms.

  ========== CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 22219 ms, elapsed time = 19565 ms.

  ========== Mark =====================================================

  SQL Server Execution Times:

  CPU time = 36063 ms, elapsed time = 32138 ms.

  ========== IMPROVED CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 15000 ms, elapsed time = 13883 ms.

  ========== IMPROVED CELKO 2===========================================

  SQL Server Execution Times:

  CPU time = 14703 ms, elapsed time = 12285 ms.

  ========== Mark REWRITE=====================================================

  SQL Server Execution Times:

  CPU time = 2969 ms, elapsed time = 2372 ms.

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  August 6, 2012 at 7:50 pm

  #1522360

  Not as fast as CELKO or IMPROVED CELKO but a strong third place is this option:

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  -- This is NOT a part of the solution.

  IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL

  DROP TABLE #Test

  ;

  --===== Create and populate the test table.

  -- This is NOT a part of the solution.

  SELECT TOP (1000000)

  TestID = IDENTITY(INT,1,1),

  TestNumber = ABS(CHECKSUM(NEWID()))

  INTO #Test

  FROM sys.all_columns ac1

  CROSS JOIN sys.all_columns ac2

  ;

  --===== Add the expected PK

  ALTER TABLE #Test

  ADD PRIMARY KEY CLUSTERED (TestID)

  ;

  -----------------------------------------------------------------------------

  PRINT '========== SomewhereSomehow ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

  select @T = sum(convert(int,substring(convert(varchar(10),TestNumber),n,1)))

  from nums

  CROSS JOIN #Test

  where n <= len(convert(varchar(10),TestNumber))

  GROUP BY TestNumber;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) ,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== Mark ====================================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  WITH Tens(Pos,Val) AS (

  SELECT 1, 1 UNION ALL

  SELECT 2, 10 UNION ALL

  SELECT 3, 100 UNION ALL

  SELECT 4, 1000 UNION ALL

  SELECT 5, 10000 UNION ALL

  SELECT 6, 100000 UNION ALL

  SELECT 7, 1000000 UNION ALL

  SELECT 8, 10000000 UNION ALL

  SELECT 9, 100000000 UNION ALL

  SELECT 10,1000000000)

  SELECT @T = SUM((TestNumber / Val) % 10)

  FROM Tens

  CROSS JOIN #Test

  WHERE Val<=TestNumber

  GROUP BY TestNumber;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== IMPROVED CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== DWAIN ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = SUM(CAST(Num AS INT))

  FROM #Test

  CROSS APPLY (SELECT CAST(TestNumber AS CHAR(10))) a(n)

  CROSS APPLY (

  VALUES (SUBSTRING(n, 1, 1))

  ,(SUBSTRING(n, 2, 1))

  ,(SUBSTRING(n, 3, 1))

  ,(SUBSTRING(n, 4, 1))

  ,(SUBSTRING(n, 5, 1))

  ,(SUBSTRING(n, 6, 1))

  ,(SUBSTRING(n, 7, 1))

  ,(SUBSTRING(n, 8, 1))

  ,(SUBSTRING(n, 9, 1))

  ,(SUBSTRING(n, 10, 1))

  ) x(Num)

  GROUP BY TestID

  OPTION (MAXDOP 1)

  SET STATISTICS TIME OFF;

  GO

  Timings I got are:

  ========== SomewhereSomehow ===========================================

  SQL Server Execution Times:

  CPU time = 49093 ms, elapsed time = 12991 ms.

  ========== CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 4851 ms, elapsed time = 4875 ms.

  ========== Mark =====================================================

  SQL Server Execution Times:

  CPU time = 31451 ms, elapsed time = 8665 ms.

  ========== IMPROVED CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 3604 ms, elapsed time = 3621 ms.

  ========== DWAIN ===========================================

  SQL Server Execution Times:

  CPU time = 5054 ms, elapsed time = 5135 ms.

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  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

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  August 6, 2012 at 7:58 pm

  #1522361

  BTW. Adding OPTION (MAXDOP 1) to SomewhereSomehow and Mark's gets these results (good improvement on CPU at expense of some elapsed time).

  ========== SomewhereSomehow ===========================================

  SQL Server Execution Times:

  CPU time = 17316 ms, elapsed time = 17491 ms.

  ========== CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 4883 ms, elapsed time = 5055 ms.

  ========== Mark =====================================================

  SQL Server Execution Times:

  CPU time = 10249 ms, elapsed time = 10443 ms.

  ========== IMPROVED CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 3526 ms, elapsed time = 3570 ms.

  ========== DWAIN ===========================================

  SQL Server Execution Times:

  CPU time = 5101 ms, elapsed time = 5178 ms.

  Try that OPTION anytime you see elapsed time < CPU time to see if it helps you on a CPU bound query.

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  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

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  August 6, 2012 at 8:30 pm

  #1522366

  For what it's worth, this version breaks the 5 CPU sec barrier consistently and sometimes 5 secs elapsed time too. Still in 3rd place though.

  PRINT '========== DWAIN IMPROVED (SLIGHTLY) ========================'

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = SUM(Num)

  FROM #Test

  CROSS APPLY (

  VALUES (CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 1, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 2, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 3, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 4, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 5, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 6, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 7, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 8, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 9, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 10, 1) AS INT))

  ) x(Num)

  GROUP BY TestID

  OPTION (MAXDOP 1)

  SET STATISTICS TIME OFF;

  GO

  The technique, by the way is similar to the CROSS APPLY VALUES approach to UNPIVOT - see: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  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

• Jeff Moden

  SSC Guru

  Points: 1003869

  More actions

  August 6, 2012 at 11:14 pm

  #1522385

  SQL Padawan (8/5/2012)

  ---

  I could be wrong but Celko's method is the fastest. One thing to keep in mind is the LEN function's limit is 8000 characters or in other words the sum of all digits can not exceed 8000...

  Oh, be careful, now. Please read Books Online where it gives the return type for the LEN() function...

  Return Types

  bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

  --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)
  

• SomewhereSomehow

  SSCommitted

  Points: 1586

  More actions

  August 7, 2012 at 12:22 am

  #1522403

  WoW! When I asked for testing, I didn't think that it will turn into a kind of contest =)

  Here is one more for uint.

  PRINT '========== ??? ========================'

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = Num

  from #Test

  cross apply ( values (

  (TestNumber ) % 10 +

  (TestNumber / 10) % 10 +

  (TestNumber / 100) % 10 +

  (TestNumber / 1000) % 10 +

  (TestNumber / 10000) % 10 +

  (TestNumber / 100000) % 10 +

  (TestNumber / 1000000) % 10 +

  (TestNumber / 10000000) % 10 +

  (TestNumber / 100000000) % 10 +

  (TestNumber / 1000000000) % 10

  )) x(Num)

  SET STATISTICS TIME OFF;

  go

  ========== ??? ========================

  SQL Server Execution Times:

  CPU time = 438 ms, elapsed time = 436 ms.

  Champion for the moment? (in my tests)

  ---

  I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
  Blog: http://somewheresomehow.ru[/url]
  Twitter: @SomewereSomehow

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  August 7, 2012 at 12:26 am

  #1522406

  SomewhereSomehow (8/7/2012)

  ---

  WoW! When I asked for testing, I didn't think that it will turn into a kind of contest =)

  Here is one more for uint.

  PRINT '========== ??? ========================'

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = Num

  from #Test

  cross apply ( values (

  (TestNumber ) % 10 +

  (TestNumber / 10) % 10 +

  (TestNumber / 100) % 10 +

  (TestNumber / 1000) % 10 +

  (TestNumber / 10000) % 10 +

  (TestNumber / 100000) % 10 +

  (TestNumber / 1000000) % 10 +

  (TestNumber / 10000000) % 10 +

  (TestNumber / 100000000) % 10 +

  (TestNumber / 1000000000) % 10

  )) x(Num)

  SET STATISTICS TIME OFF;

  go

  ========== ??? ========================

  SQL Server Execution Times:

  CPU time = 438 ms, elapsed time = 436 ms.

  Champion for the moment? (in my tests)

  Very interesting. That may very well set the bar. Avoiding the string casting and back makes a lot of sense.

  Edit: BTW. You don't need to do it as a VALUES set. CROSS APPLY using SELECT would suffice. I initially thought you were doing an UNPIVOT but then realized that you're not (no GROUP BY probably also helps).

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  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

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  August 7, 2012 at 12:46 am

  #1522414

  The 2nd to last query in the sequence illustrates what I meant by the edit in my prior post.

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  -- This is NOT a part of the solution.

  IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL

  DROP TABLE #Test

  ;

  --===== Create and populate the test table.

  -- This is NOT a part of the solution.

  SELECT TOP (1000000)

  TestID = IDENTITY(INT,1,1),

  TestNumber = ABS(CHECKSUM(NEWID()))

  INTO #Test

  FROM sys.all_columns ac1

  CROSS JOIN sys.all_columns ac2

  ;

  --===== Add the expected PK

  ALTER TABLE #Test

  ADD PRIMARY KEY CLUSTERED (TestID)

  ;

  -----------------------------------------------------------------------------

  PRINT '========== SomewhereSomehow ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

  select @T = sum(convert(int,substring(convert(varchar(10),TestNumber),n,1)))

  from nums

  CROSS JOIN #Test

  where n <= len(convert(varchar(10),TestNumber))

  GROUP BY TestNumber

  OPTION (MAXDOP 1);

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) ,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== Mark ====================================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  WITH Tens(Pos,Val) AS (

  SELECT 1, 1 UNION ALL

  SELECT 2, 10 UNION ALL

  SELECT 3, 100 UNION ALL

  SELECT 4, 1000 UNION ALL

  SELECT 5, 10000 UNION ALL

  SELECT 6, 100000 UNION ALL

  SELECT 7, 1000000 UNION ALL

  SELECT 8, 10000000 UNION ALL

  SELECT 9, 100000000 UNION ALL

  SELECT 10,1000000000)

  SELECT @T = SUM((TestNumber / Val) % 10)

  FROM Tens

  CROSS JOIN #Test

  WHERE Val<=TestNumber

  GROUP BY TestNumber

  OPTION (MAXDOP 1);

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== IMPROVED CELKO ==========================================='

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

  ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

  FROM #Test;

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== DWAIN IMPROVED (SLIGHTLY) ========================'

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = SUM(Num)

  FROM #Test

  CROSS APPLY (

  VALUES (CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 1, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 2, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 3, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 4, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 5, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 6, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 7, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 8, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 9, 1) AS INT))

  ,(CAST(SUBSTRING(CAST(TestNumber AS CHAR(10)), 10, 1) AS INT))

  ) x(Num)

  GROUP BY TestID

  OPTION (MAXDOP 1)

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== SomewhereSomehow Try 2 ========================'

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = Num

  from #Test

  cross apply ( values (

  (TestNumber ) % 10 +

  (TestNumber / 10) % 10 +

  (TestNumber / 100) % 10 +

  (TestNumber / 1000) % 10 +

  (TestNumber / 10000) % 10 +

  (TestNumber / 100000) % 10 +

  (TestNumber / 1000000) % 10 +

  (TestNumber / 10000000) % 10 +

  (TestNumber / 100000000) % 10 +

  (TestNumber / 1000000000) % 10

  )) x(Num)

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== WITHOUT CROSS APPLY VALUES ========================'

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = Num

  from #Test

  cross apply ( SELECT

  (TestNumber ) % 10 +

  (TestNumber / 10) % 10 +

  (TestNumber / 100) % 10 +

  (TestNumber / 1000) % 10 +

  (TestNumber / 10000) % 10 +

  (TestNumber / 100000) % 10 +

  (TestNumber / 1000000) % 10 +

  (TestNumber / 10000000) % 10 +

  (TestNumber / 100000000) % 10 +

  (TestNumber / 1000000000) % 10

  ) x(Num)

  SET STATISTICS TIME OFF;

  GO

  PRINT '========== WITHOUT CROSS APPLY AT ALL ========================'

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T =

  (TestNumber ) % 10 +

  (TestNumber / 10) % 10 +

  (TestNumber / 100) % 10 +

  (TestNumber / 1000) % 10 +

  (TestNumber / 10000) % 10 +

  (TestNumber / 100000) % 10 +

  (TestNumber / 1000000) % 10 +

  (TestNumber / 10000000) % 10 +

  (TestNumber / 100000000) % 10 +

  (TestNumber / 1000000000) % 10

  from #Test

  SET STATISTICS TIME OFF;

  GO

  The results show that there seems to be a pretty close heat going on between your suggestion and the alternative that doesn't use CROSS APPLY VALUES, however they are now tied for second place. The best seems to be when you remove CROSS APPLY entirely.

  ========== SomewhereSomehow ===========================================

  SQL Server Execution Times:

  CPU time = 22449 ms, elapsed time = 42572 ms.

  ========== CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 4836 ms, elapsed time = 5148 ms.

  ========== Mark =====================================================

  SQL Server Execution Times:

  CPU time = 12152 ms, elapsed time = 29379 ms.

  ========== IMPROVED CELKO ===========================================

  SQL Server Execution Times:

  CPU time = 3510 ms, elapsed time = 3753 ms.

  ========== DWAIN IMPROVED (SLIGHTLY) ========================

  SQL Server Execution Times:

  CPU time = 5148 ms, elapsed time = 5468 ms.

  ========== SomewhereSomehow Try 2 ========================

  SQL Server Execution Times:

  CPU time = 1233 ms, elapsed time = 1432 ms.

  ========== WITHOUT CROSS APPLY VALUES ========================

  SQL Server Execution Times:

  CPU time = 1232 ms, elapsed time = 1364 ms.

  ========== WITHOUT CROSS APPLY AT ALL ========================

  SQL Server Execution Times:

  CPU time = 1201 ms, elapsed time = 1373 ms.

  You must be running on quite the bear of a machine for it to execute in 438ms!

  Nothing like a little competition to get the juices flowing!

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  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

• SomewhereSomehow

  SSCommitted

  Points: 1586

  More actions

  August 7, 2012 at 12:59 am

  #1522417

  Well, I do all the tests on my local work machine, 3.3GHz-4 core, 3 GB RAM. SQL Server 2008R2 RTM.

  I have quite the same performance and the same plans for both queries - with cross apply values and without cross apply. Smart optimizer tranformes it in the same way.

  ---

  I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
  Blog: http://somewheresomehow.ru[/url]
  Twitter: @SomewereSomehow

• Paul White

  SSC Guru

  Points: 150467

  More actions

  September 21, 2012 at 4:25 am

  #1540564

  This is about twice as fast for me:

  CREATE ASSEMBLY Test

  FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300453D5C500000000000000000E00002210B010800000800000006000000000000FE250000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B02500004B00000000400000A802000000000000000000000000000000000000006000000C000000142500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000004060000002000000008000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000E025000000000000480000000200050084200000900400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003001D00000001000011160A02280500000A0B2B0C06071F0A5D580A071F0A5B0B071630F0062A1E02280600000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000030010000237E00009C0100004001000023537472696E677300000000DC0200000800000023555300E4020000100000002347554944000000F40200009C01000023426C6F620000000000000002000001471502000900000000FA2533001600000100000007000000020000000200000001000000060000000400000001000000010000000200000000000A0001000000000006003D00360006006D005A000B00810000000600B00090000600D00090000A001F01040106003401360000000000010000000000010001000100100018000000050001000100502000000000960044000A00010079200000000086184E000F00020000000100540011004E00130021004E00190029004E000F0031004E000F00390039010A0009004E000F00200023001E002E000B006B012E00130074012E001B007D016601048000000000000000000000000000000000EE00000002000000000000000000000001002D00000000000200000000000000000000000100F800000000000000003C4D6F64756C653E004461746162617365332E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053756D446967697473002E63746F7200496E7075740053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365330053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465004D61746800416273000000000003200000000000E371327B18D8AB49BF080585140503AB0008B77A5C561934E08904000108080320000105200101110D04200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650104070208080801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000453D5C50000000000200000080000000302500003007000052534453FA3B49E3B829D24C859CA14C76F908B101000000633A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C4461746162617365335C4461746162617365335C6F626A5C52656C656173655C4461746162617365332E70646200D82500000000000000000000EE250000002000000000000000000000000000000000000000000000E02500000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650033002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650033002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

  WITH PERMISSION_SET = SAFE;

  GO

  CREATE FUNCTION dbo.SumDigits

  (@Input integer)

  RETURNS integer

  AS EXTERNAL NAME Test.UserDefinedFunctions.SumDigits;

  GO

  -- Test & ensure function is fully compiled

  SELECT dbo.SumDigits (2147483647);

  SET STATISTICS TIME ON;

  DECLARE @T INT;

  SELECT @T = dbo.SumDigits(TestNumber)

  FROM #Test;

  SET STATISTICS TIME OFF;

  Source:

  [font="Courier New"]    public static int SumDigits(int Input)

      {

          int sum = 0;

          for (int n = System.Math.Abs(Input); n > 0; sum += n % 10, n /= 10) ;

          return sum;

      }

  [/font]

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 21, 2012 at 4:45 am

  #1540575

  SQL Kiwi (9/21/2012)

  ---

  This is about twice as fast for me:

  ...

  public static int SumDigits(int Input)

  {

  int sum = 0;

  for (int n = System.Math.Abs(Input); n > 0; sum += n % 10, n /= 10) ;

  return sum;

  }

  ...

  Another way in c# :

  public static int SumDigits(int Input) { return Input.ToString().Sum(c => c - '0'); }

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]