Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

The Joy of Numbers Expand / Collapse
Author
Message
Posted Saturday, November 22, 2008 9:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607018
Posted Sunday, November 23, 2008 6:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
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:00
Convert column to NOT NULL - 01:01:42
Add Clustered PK - 01:27:59

Total - 2:58:41

First, 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 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
Nums 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 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
Nums 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607176
Posted Wednesday, December 30, 2009 12:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #840200
Posted Sunday, May 16, 2010 12:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 + k
FROM k k1
WHERE 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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
{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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #922609
Posted Thursday, May 20, 2010 11:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
_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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #926532
Posted Sunday, May 23, 2010 4:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 numbers
FROM C c1, C c2, C c3, C c4

Post #926573
Posted Sunday, May 23, 2010 6:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
_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 numbers
FROM 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 numbers
FROM 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,000
cteTally(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 table
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,000
cteTally(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 table
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,000
cteTally(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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #926577
Posted Friday, May 28, 2010 3:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 string
New approach using numbers table

CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000)) 
RETURNS VARCHAR(8000) 
AS
BEGIN
    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 @result
END;

Post #929544
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse