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

 The Joy of Numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, November 22, 2008 9:17 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 Heh... dang it... I gotta remember... "Scroll Down". You have that in the code. I'm running some tests on this (cuz it's interesting)... I'll give a complete report when I'm done but, so far, the Insert completed on my humble 6 year old desktop (P4 1.8 Ghz, 1GB Ram, 5400 RPM Ide Drive, Windows XP, SQL Server 2005 sp2 Developer's Edition) in only 29 minutes and the conversion of the "i" column to INT NOT NULL (I didn't convert to BIGINT, just INT) only took an hour. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #607018
 Posted Sunday, November 23, 2008 6:29 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 Ok... the Billion row results are in. On the orignal code that Russ submitted (looks like modification of Itzek's code), Insert Duration - 00:29:00Convert column to NOT NULL - 01:01:42Add Clustered PK - 01:27:59Total - 2:58:41First, we'll revert do Itzek's original rendition of the code with is about 5 minutes faster on my humble box... just notice the difference in placement of the ROW_NUMBER... `;WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsNums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)SELECT N AS i INTO NUMBERS_TEST FROM Nums WHERE n <= 100000000`Now, lemme show you one of those "undocumented" tricks that makes all the difference in the world... let's add a something that will make the "i" column NOT NULL as it's being built...`;WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsNums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)SELECT ISNULL(N,0) AS i INTO NUMBERS_TEST FROM Nums WHERE n <= 100000000`Believe it or not, it doesn't slow the code down at all and has the added benefit of making the result column NOT NULL. That wipes out the whole 01:01:42 for converting the column to NOT NULL in a separate step.Now, for the big suprise... after the table was built, the dedicated database I made for this test had expanded to 16.5 GB. Now, the database is set to the Simple Recovery Mode, and the "used" portion did drop back down to that vicinity, but the database MDF file expanded to over 30 GB to build the clustered index. In other words, adding the clustered index temporarily caused the disk usage to almost double for the sorts involved.I will say that I'm pretty happy that I don't have to create a Billion row numbers table everyday! --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." Helpful Links:How to post code problemsHow to post performance problems
Post #607176
 Posted Wednesday, December 30, 2009 12:15 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 james elmer (1/23/2007)Good article. Interesting that my create table/insert while loop (to 10,000 rows) executed in 4 seconds and the set based solution executed in 17 seconds.Heh... guess I've waited long enough... would you post your code, please? --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." Helpful Links:How to post code problemsHow to post performance problems
Post #840200
 Posted Sunday, May 16, 2010 12:11 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 24, 2012 11:32 AM Points: 56, Visits: 880
 Hi,At the part of eliminate duplicate what if we have a '!' character in string?So following solution is so simple and suitable for any scenario.And I use a new way for publishing numbers table.`DECLARE @s VARCHAR(500)='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaffffffffffffffffffffffffdddddddddddd ghqwer a d qqqq [pppp]'DECLARE @result VARCHAR(500)='';WITH c AS (SELECT 1 AS n UNION ALL SELECT 1 + n FROM c WHERE n < 100),c1 AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM c c1 CROSS JOIN c c2), k AS(SELECT n, k = SUBSTRING (@s, n, 1) FROM c1 WHERE n <= LEN(@s))SELECT @result = @result + kFROM k k1WHERE NOT EXISTS (SELECT * FROM k k2 WHERE k1.k = k2.k AND k1.n+1 = k2.n);SELECT @result AS removed;/*removed-----------------------afd ghqwer a d q [p]`
Post #922566
 Posted Sunday, May 16, 2010 10:42 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 {edit} Still testing... comment removed. Sorry. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #922609
 Posted Thursday, May 20, 2010 11:34 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 24, 2012 11:32 AM Points: 56, Visits: 880
 In splitting string using number table is not this approch simpler?`DECLARE @s VARCHAR(80)='Army,Navy,Air Force,Marines';WITH c AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM c WHERE n < 100)SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM c WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ','`
Post #925408
 Posted Sunday, May 23, 2010 11:11 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 _ms65g_ (5/20/2010)In splitting string using number table is not this approch simpler?`DECLARE @s VARCHAR(80)='Army,Navy,Air Force,Marines';WITH c AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM c WHERE n < 100)SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM c WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ','`Sorry... got pulled away on other things...Yes... that method is much "simpler". It's also much slower in the grand scheme of things because recursive counters are as bad or worse than While Loops for performance and they use a lot more "reads" even if the reads are in memory. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #926532
 Posted Sunday, May 23, 2010 4:39 PM
 Valued Member Group: General Forum Members Last Login: Monday, December 24, 2012 11:32 AM Points: 56, Visits: 880
 Thank you, But I do not mean publishing number table, I mean Splitting method is not simpler?Also, A simplified method for publishing number table (no loop, no recursion, and no ranking)`;WITH C (i) AS(SELECT '0' UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5'UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9')SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4`
Post #926573
 Posted Sunday, May 23, 2010 6:05 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 _ms65g_ (5/23/2010)Thank you, But I do not mean publishing number table, I mean Splitting method is not simpler?Also, A simplified method for publishing number table (no loop, no recursion, and no ranking)`;WITH C (i) AS(SELECT '0' UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5'UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9')SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4`Oh be careful... That method will always generate all 10000 numbers even it you put a filter on it and it has the added disadvantage of not producing an ordered output. Compare the actual execution plans of your method and a derivative of Itzik Ben-Gan's method...`--===== This method causes all 10000 rows to show up in the execution plan even thouth only 100 -- are called for. It also has the disadvantage of not being sorted. I wouldn't use this method.;WITH C (i) AS( SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9'),cteTally AS(SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4) SELECT nbr FROM cteTally t WHERE nbr BETWEEN 1 AND 100 ;--===== Although a bit more complicated, this method is very effective because it only generates the -- number of rows requested AND has the advantage of a sorted result set.WITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4) SELECT N FROM cteTally WHERE N BETWEEN 1 AND 100`Also, although it doesn't appear to hurt anything, the split code you posted has an unnecessary SELECT in it. Take a look...`DECLARE @s VARCHAR(80);SELECT @s = 'Army,Navy,Air Force,Marines';--===== Your splitter with Itzik's inline Tally tableWITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4) --===== Do your split SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM cteTally WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ',';--===== An even simpler splitter, also with Itzik's inline Tally tableWITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4) --===== Do the split SELECT SUBSTRING(@s, N, CHARINDEX(',', @s + ',', N) - N) AS Item FROM cteTally WHERE N < LEN(@s) + 2 AND SUBSTRING(',' + @s, N, 1) = ',';` --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." Helpful Links:How to post code problemsHow to post performance problems
Post #926577
 Posted Friday, May 28, 2010 3:27 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 24, 2012 11:32 AM Points: 56, Visits: 880
 Remove duplicate side-by-side characters from a stringNew approach using numbers table`CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000)) RETURNS VARCHAR(8000) ASBEGIN    DECLARE @result VARCHAR(8000) = '';      ;WITH DataOrder   AS   (      SELECT ID, Data             ,ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) AS RowNum        FROM (SELECT SUBSTRING(@String, nbr, 1), nbr                 FROM Nums                WHERE nbr <= LEN(@String)             ) D(data, ID)   )    SELECT @result = @result + Data    FROM (SELECT ID, Data                 ,DENSE_RANK() OVER (ORDER BY ID - RowNum) As [Rank]            FROM DataOrder         )D   GROUP BY Data, [Rank]   ORDER BY MIN(ID)   RETURN @resultEND;`
Post #929544

 Permissions