April 12, 2015 at 12:09 pm
hi every,
can any one help on my issue
i have a input string
declare @string varchar(1000)='100010111100010001'
in string zero positions when it started and when it ended
output should be as mentioned below
Result
--------
2-4
5-6
11-13
15-17
April 12, 2015 at 1:07 pm
cooljagadeesh (4/12/2015)
hi every,can any one help on my issue
i have a input string
declare @string varchar(1000)='100010111100010001'
in string zero positions when it started and when it ended
output should be as mentioned below
Result
--------
2-4
5-6
11-13
15-17
Quick and simple solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @STRING varchar(1000)='100010111100010001';
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(LEN(@STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3)
,CONSECUTIVE_ZEROS AS
(
SELECT
NM.N - ROW_NUMBER() OVER
(
ORDER BY NM.N
) GRP_ID
,NM.N
FROM NUMS NM
WHERE ASCII(SUBSTRING(@STRING,NM.N,1)) = 48
)
SELECT
CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0) AS RESULT
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.GRP_ID;
Results
RESULT
-------
2-4
6-6
11-13
15-17
April 13, 2015 at 1:06 am
thanks boss
April 13, 2015 at 8:36 am
cooljagadeesh (4/13/2015)
thanks boss
Do you understand the solution that Eirikur provided? There's at least one question that I'd ask.
April 14, 2015 at 1:46 am
Luis Cazares (4/13/2015)
cooljagadeesh (4/13/2015)
thanks bossDo you understand the solution that Eirikur provided? There's at least one question that I'd ask.
Don't be shy Luis, you can ask:-D
😎
April 14, 2015 at 9:35 am
Eirikur Eiriksson (4/14/2015)
Luis Cazares (4/13/2015)
cooljagadeesh (4/13/2015)
thanks bossDo you understand the solution that Eirikur provided? There's at least one question that I'd ask.
Don't be shy Luis, you can ask:-D
😎
I know, but it would be too easy for the OP. I want him/her to analyze the solution.
April 14, 2015 at 9:47 am
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';
select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
April 14, 2015 at 10:42 am
ztoddw (4/14/2015)
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
Out of curiosity, why do you think this code is efficient?
😎
April 14, 2015 at 11:51 am
I admit I don't know for sure that it's more efficient- it just seems like your solution was doing more work than it needed to- although I'm not sure since I don't completely understand it. 🙂
April 14, 2015 at 12:36 pm
Eirikur Eiriksson (4/14/2015)
ztoddw (4/14/2015)
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
Out of curiosity, why do you think this code is efficient?
😎
It does produce a far simpler execution plan. I didn't do any load testing but it might be more efficient than it appears at a glance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2015 at 3:34 pm
Sean Lange (4/14/2015)
Eirikur Eiriksson (4/14/2015)
ztoddw (4/14/2015)
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
Out of curiosity, why do you think this code is efficient?
😎
It does produce a far simpler execution plan. I didn't do any load testing but it might be more efficient than it appears at a glance.
Hope I'm not sounding arrogant, maybe I phrased the question in a clumsy way. Maybe there is something in
ztoddw's code which I can learn from!
The plan produced from the Tally/DelimitedSplit type code is always looking more complex than it is execution wise, one has to read between the lines here;-)
😎
April 14, 2015 at 11:11 pm
ztoddw (4/14/2015)
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
Despite the very short execution plan, it's not and it comes up with the wrong answer in many cases... usually when the first or last character is a "0".
declare @STRING varchar(1000);set @STRING='011101000';
select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
Result:
1-4
5-8
7-9
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2015 at 11:33 pm
Thanks for the bug report Jeff- it's awesome when folks help me test. Got that fixed. I didn't realize Sql Server had the little quirk with the len function that it excluded trailing blanks.
declare @STRING varchar(1000);set @STRING='011101000';
select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')+'~'))+n))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
April 15, 2015 at 7:31 am
Eirikur Eiriksson (4/14/2015)
Sean Lange (4/14/2015)
Eirikur Eiriksson (4/14/2015)
ztoddw (4/14/2015)
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
Out of curiosity, why do you think this code is efficient?
😎
It does produce a far simpler execution plan. I didn't do any load testing but it might be more efficient than it appears at a glance.
Hope I'm not sounding arrogant, maybe I phrased the question in a clumsy way. Maybe there is something in
ztoddw's code which I can learn from!
The plan produced from the Tally/DelimitedSplit type code is always looking more complex than it is execution wise, one has to read between the lines here;-)
😎
Very true. The plan is not always a solid indication of performance. I hadn't looked at the actual performance and didn't notice the inaccuracy that Jeff did. I like your approach as I find it much easier to read and seems a bit simpler to me. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2015 at 2:02 pm
ztoddw (4/14/2015)
I admit I don't know for sure that it's more efficient- it just seems like your solution was doing more work than it needed to- although I'm not sure since I don't completely understand it. 🙂
No worries, I was kind of curious as I haven't used the systems procedure table for this kind of thing in many years. Turns out that it is not too efficient though.
😎
To explain what the code does, here is a commented version
/* Safe place to do exercises in
*/
USE tempdb;
GO
/* Quiet please!
*/
SET NOCOUNT ON;
/* Sample data
*/
DECLARE @STRING varchar(1000)='100010111100010001';
/* Seed for an In-line Tally table,
produced a single column tabular
output of 10 NULL value rows
*/
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
/* In-line Tally/Numbers table, unbroken
sequence of numbers from 1 to the
length of the sample data.
NOTE: this code can produce a sequence up to 1000, if longer
input is expected, add instances of T in the from clause.
*/
, NUMS(N) AS (SELECT TOP(LEN(@STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3)
/* The CTE goes through all characters in the string by character code
and discards all '1'. The row_number function then uses an Itzik Ben-Gan style
Island grouping to identify the "islands" of '0'
*/
,CONSECUTIVE_ZEROS AS
(
/**/
SELECT
NM.N - ROW_NUMBER() OVER
(
--PARTITION BY [COLUMN_NAME] /* Use the key column if the source is a table*/
ORDER BY NM.N
) GRP_ID
,NM.N
FROM NUMS NM
/* Filter on '0' */
WHERE ASCII(SUBSTRING(@STRING,NM.N,1)) = 48
)
/* Construct the required results with a group by aggregation
and string concatenation
*/
SELECT
CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0) AS RESULT
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.GRP_ID;
To test the different code, I constructed a quick test harness, the main thing being a data set generation
USE tempdb;
GO
SET NOCOUNT ON;
/*
Sample Data
Generate varable length strings from 1 to 30 characters long
consisting of a random combination of '0' and '1'
*/
IF OBJECT_ID(N'dbo.TBL_BINARY_STRING') IS NOT NULL DROP TABLE dbo.TBL_BINARY_STRING;
SELECT TOP(1000000)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS RRID
,CHECKSUM(NEWID()) AS SVAL
INTO #SAMPLE
FROM sys.all_columns C1,sys.all_columns C2,sys.all_columns C3,sys.all_columns C4,sys.all_columns C5
;WITH T(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(26),(28),(29),(30)) AS X(N))
,MASKS(MS) AS (SELECT POWER(2,TN.N) AS MS FROM T TN)
SELECT
ISNULL(S.RRID,0) AS BIST_ID
,SUBSTRING((
SELECT
SIGN(S.SVAL & M.MS)
FROM #SAMPLE SX
CROSS APPLY MASKS M
WHERE S.RRID = SX.RRID
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(32)'),1,(ABS(CHECKSUM(NEWID())) % 30) + 1) AS BIST_VALUE
INTO dbo.TBL_BINARY_STRING
FROM #SAMPLE S;
DROP TABLE #SAMPLE;
ALTER TABLE dbo.TBL_BINARY_STRING ALTER COLUMN BIST_ID INT NOT NULL;
ALTER TABLE dbo.TBL_BINARY_STRING ALTER COLUMN BIST_VALUE VARCHAR(32) NOT NULL;
ALTER TABLE dbo.TBL_BINARY_STRING ADD CONSTRAINT PK_DBO_TBL_BINARY_STRING_BIST_ID PRIMARY KEY CLUSTERED (BIST_ID);
IF OBJECT_ID(N'dbo.TBL_NUMBERS') IS NOT NULL DROP TABLE dbo.TBL_NUMBERS;
SELECT TOP (8001)
ISNULL(ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
),0) AS N
INTO dbo.TBL_NUMBERS
FROM sys.all_columns C1,sys.all_columns C2,sys.all_columns C3;
ALTER TABLE dbo.TBL_NUMBERS ALTER COLUMN N INT NOT NULL;
ALTER TABLE dbo.TBL_NUMBERS ADD CONSTRAINT PK_DBO_TBL_NUMBERS_N PRIMARY KEY CLUSTERED (N);
CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_DBO_TBL_NUMBERS_N ON dbo.TBL_NUMBERS (N ASC);
/* length distribution */
SELECT
COUNT(*) AS SAMPLE_COUNT
,LEN(BS.BIST_VALUE) AS SAMPLE_LENGTH
FROM dbo.TBL_BINARY_STRING BS
GROUP BY LEN(BS.BIST_VALUE)
ORDER BY SAMPLE_LENGTH
The first test is on different cardinalities and almost equal distribution, recording the statistics IO and TIME
Test Code
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @INT_BUCKET INT = 0;
DECLARE @CHR_BUCKET VARCHAR(50) = '';
RAISERROR(N'-----------------------------------------------------------------------------------------
INLINE TALLY METHOD
-----------------------------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,CONSECUTIVE_ZEROS AS
(
SELECT
BS.BIST_ID
,NM.N - ROW_NUMBER() OVER
(
PARTITION BY BS.BIST_ID
ORDER BY NM.N
) GRP_ID
,NM.N
FROM dbo.TBL_BINARY_STRING BS
CROSS APPLY (SELECT TOP(LEN(BS.BIST_VALUE)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2 ) NM(N)
WHERE ASCII(SUBSTRING(BS.BIST_VALUE,NM.N,1)) = 48
)
SELECT
@INT_BUCKET = CZ.BIST_ID
,@CHR_BUCKET = CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0) --AS RESULT
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.BIST_ID
,CZ.GRP_ID
SET STATISTICS TIME,IO OFF;
RAISERROR(N'-----------------------------------------------------------------------------------------
SYSTEM PROCEDURE TABLE METHOD
-----------------------------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
select
@INT_BUCKET = BS.BIST_ID
,@CHR_BUCKET = ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')+'~'))+n))
from dbo.TBL_BINARY_STRING BS
CROSS APPLY (select n=number, sub=substring(BS.BIST_VALUE,number,len(BS.BIST_VALUE))
from master.dbo.spt_values where type='P'
and (number between 1 and len(BS.BIST_VALUE))) t
where (n=1 or substring(BS.BIST_VALUE,n-1,1)<>'0')
and substring(BS.BIST_VALUE,n,1)='0';
SET STATISTICS IO,TIME OFF;
RAISERROR(N'-----------------------------------------------------------------------------------------
NORMAL TALLY METHOD
-----------------------------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH CONSECUTIVE_ZEROS AS
(
SELECT
BS.BIST_ID
,NM.N - ROW_NUMBER() OVER
(
PARTITION BY BS.BIST_ID
ORDER BY NM.N
) AS GRP_ID
,NM.N
FROM dbo.TBL_BINARY_STRING BS
CROSS APPLY (SELECT TOP(LEN(BS.BIST_VALUE)) N FROM dbo.TBL_NUMBERS) NM(N)
WHERE ASCII(SUBSTRING(BS.BIST_VALUE,NM.N,1)) = 48
)
SELECT
@INT_BUCKET = CZ.BIST_ID
,@CHR_BUCKET = CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0) --AS RESULT
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.BIST_ID
,CZ.GRP_ID;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'
-----------------------------------------------------------------------------------------
NORMAL TALLY METHOD 2
-----------------------------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH BASE_DATA AS
(
SELECT
BS.BIST_ID
,NM.N
FROM dbo.TBL_BINARY_STRING BS
CROSS APPLY (SELECT TOP(LEN(BS.BIST_VALUE)) N FROM dbo.TBL_NUMBERS) NM(N)
WHERE ASCII(SUBSTRING(BS.BIST_VALUE,NM.N,1)) = 48
)
,CONSECUTIVE_ZEROS AS
(
SELECT
BD.BIST_ID
,BD.N
,BD.N - ROW_NUMBER() OVER
(
PARTITION BY BD.BIST_ID
ORDER BY BD.N
) AS GRP_ID
FROM BASE_DATA BD
)
SELECT
@INT_BUCKET = CZ.BIST_ID
,@CHR_BUCKET = CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0)
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.BIST_ID
,CZ.GRP_ID;
SET STATISTICS TIME,IO OFF;
Results
Beginning execution loop
ROW_COUNT
-----------
1000000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 13561095
NORMAL TALLY METHOD 16149828
NORMAL TALLY METHOD 2 16196631
SYSTEM PROCEDURE TABLE METHOD 20096644
ROW_COUNT
-----------
1000000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 13637100
NORMAL TALLY METHOD 2 14916365
NORMAL TALLY METHOD 18913170
SYSTEM PROCEDURE TABLE METHOD 20023827
ROW_COUNT
-----------
1000000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 13749306
NORMAL TALLY METHOD 14984769
NORMAL TALLY METHOD 2 15488002
SYSTEM PROCEDURE TABLE METHOD 19832617
Batch execution completed 3 times.
Beginning execution loop
ROW_COUNT
-----------
100000
T_TEXT DURATION
-------------------------------------- -----------
NORMAL TALLY METHOD 2 592804
NORMAL TALLY METHOD 671804
INLINE TALLY METHOD 795605
SYSTEM PROCEDURE TABLE METHOD 2667617
ROW_COUNT
-----------
100000
T_TEXT DURATION
-------------------------------------- -----------
NORMAL TALLY METHOD 811205
INLINE TALLY METHOD 826805
NORMAL TALLY METHOD 2 1390409
SYSTEM PROCEDURE TABLE METHOD 2653018
ROW_COUNT
-----------
100000
T_TEXT DURATION
-------------------------------------- -----------
NORMAL TALLY METHOD 733205
INLINE TALLY METHOD 795605
NORMAL TALLY METHOD 2 1060807
SYSTEM PROCEDURE TABLE METHOD 2652017
Batch execution completed 3 times.
Beginning execution loop
ROW_COUNT
-----------
10000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 93403
NORMAL TALLY METHOD 2 109201
NORMAL TALLY METHOD 124801
SYSTEM PROCEDURE TABLE METHOD 280802
ROW_COUNT
-----------
10000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 78000
NORMAL TALLY METHOD 111201
NORMAL TALLY METHOD 2 124801
SYSTEM PROCEDURE TABLE METHOD 265202
ROW_COUNT
-----------
10000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 62400
NORMAL TALLY METHOD 2 109200
NORMAL TALLY METHOD 109201
SYSTEM PROCEDURE TABLE METHOD 280802
Batch execution completed 3 times.
Beginning execution loop
ROW_COUNT
-----------
1000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 15600
NORMAL TALLY METHOD 15600
NORMAL TALLY METHOD 2 15600
SYSTEM PROCEDURE TABLE METHOD 31200
ROW_COUNT
-----------
1000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 15600
NORMAL TALLY METHOD 2 15600
NORMAL TALLY METHOD 15601
SYSTEM PROCEDURE TABLE METHOD 31200
ROW_COUNT
-----------
1000
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 15600
NORMAL TALLY METHOD 15600
NORMAL TALLY METHOD 2 15600
SYSTEM PROCEDURE TABLE METHOD 31200
Batch execution completed 3 times.
Beginning execution loop
ROW_COUNT
-----------
100
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 0
NORMAL TALLY METHOD 0
NORMAL TALLY METHOD 2 0
SYSTEM PROCEDURE TABLE METHOD 15600
ROW_COUNT
-----------
100
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 0
NORMAL TALLY METHOD 0
NORMAL TALLY METHOD 2 0
SYSTEM PROCEDURE TABLE METHOD 15600
ROW_COUNT
-----------
100
T_TEXT DURATION
-------------------------------------- -----------
INLINE TALLY METHOD 0
NORMAL TALLY METHOD 0
NORMAL TALLY METHOD 2 0
SYSTEM PROCEDURE TABLE METHOD 15600
Batch execution completed 3 times.
The next test is more on the actual timing
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @INT_BUCKET INT = 0;
DECLARE @CHR_BUCKET VARCHAR(50) = '';
DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
SELECT COUNT(*) AS ROW_COUNT FROM dbo.TBL_BINARY_STRING;
INSERT INTO @timer(T_TEXT) VALUES('INLINE TALLY METHOD');
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,CONSECUTIVE_ZEROS AS
(
SELECT
BS.BIST_ID
,NM.N - ROW_NUMBER() OVER
(
PARTITION BY BS.BIST_ID
ORDER BY NM.N
) GRP_ID
,NM.N
FROM dbo.TBL_BINARY_STRING BS
CROSS APPLY (SELECT TOP(LEN(BS.BIST_VALUE)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2 /*,T T3*/) NM(N)
WHERE ASCII(SUBSTRING(BS.BIST_VALUE,NM.N,1)) = 48
)
SELECT
@INT_BUCKET = CZ.BIST_ID
,@CHR_BUCKET = CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0) --AS RESULT
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.BIST_ID
,CZ.GRP_ID
INSERT INTO @timer(T_TEXT) VALUES('INLINE TALLY METHOD');
INSERT INTO @timer(T_TEXT) VALUES('SYSTEM PROCEDURE TABLE METHOD');
select
@INT_BUCKET = BS.BIST_ID
,@CHR_BUCKET = ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')+'~'))+n))
from dbo.TBL_BINARY_STRING BS
CROSS APPLY (select n=number, sub=substring(BS.BIST_VALUE,number,len(BS.BIST_VALUE))
from master.dbo.spt_values where type='P'
and (number between 1 and len(BS.BIST_VALUE))) t
where (n=1 or substring(BS.BIST_VALUE,n-1,1)<>'0')
and substring(BS.BIST_VALUE,n,1)='0'
INSERT INTO @timer(T_TEXT) VALUES('SYSTEM PROCEDURE TABLE METHOD');
INSERT INTO @timer(T_TEXT) VALUES('NORMAL TALLY METHOD');
;WITH CONSECUTIVE_ZEROS AS
(
SELECT
BS.BIST_ID
,NM.N - ROW_NUMBER() OVER
(
PARTITION BY BS.BIST_ID
ORDER BY NM.N
) AS GRP_ID
,NM.N
FROM dbo.TBL_BINARY_STRING BS
CROSS APPLY (SELECT TOP(LEN(BS.BIST_VALUE)) N FROM dbo.TBL_NUMBERS) NM(N)
WHERE ASCII(SUBSTRING(BS.BIST_VALUE,NM.N,1)) = 48
)
SELECT
@INT_BUCKET = CZ.BIST_ID
,@CHR_BUCKET = CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0) --AS RESULT
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.BIST_ID
,CZ.GRP_ID;
INSERT INTO @timer(T_TEXT) VALUES('NORMAL TALLY METHOD');
INSERT INTO @timer(T_TEXT) VALUES('NORMAL TALLY METHOD 2');
;WITH BASE_DATA AS
(
SELECT
BS.BIST_ID
,NM.N
FROM dbo.TBL_BINARY_STRING BS
CROSS APPLY (SELECT TOP(LEN(BS.BIST_VALUE)) N FROM dbo.TBL_NUMBERS) NM(N)
WHERE ASCII(SUBSTRING(BS.BIST_VALUE,NM.N,1)) = 48
)
,CONSECUTIVE_ZEROS AS
(
SELECT
BD.BIST_ID
,BD.N
,BD.N - ROW_NUMBER() OVER
(
PARTITION BY BD.BIST_ID
ORDER BY BD.N
) AS GRP_ID
FROM BASE_DATA BD
)
SELECT
@INT_BUCKET = CZ.BIST_ID
,@CHR_BUCKET = CONVERT(VARCHAR(12),MIN(CZ.N),0) + CHAR(45) + CONVERT(VARCHAR(12),MAX(CZ.N),0)
FROM CONSECUTIVE_ZEROS CZ
GROUP BY CZ.BIST_ID
,CZ.GRP_ID;
INSERT INTO @timer(T_TEXT) VALUES('NORMAL TALLY METHOD 2');
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
ORDER BY DURATION;
GO
The results do vary quite a lot but they do reflect the findings above, unfortunately I don't have them at hand but try it out.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply