July 25, 2020 at 2:08 pm
When I switch the datediff metric to nanoseconds and run the script over and over again the tally and cte are equal at 0 ns up to about 500 rows. After 500 rows the tally is always faster. Do you have some metrics which would support the cte being faster in certain cases? Based on the script (which just a spot test) there's either no difference when row count is low or the tally is always faster when row count is high.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 25, 2020 at 2:31 pm
I will stop now as it is obviously important for you to prove that tally is the only solution. Your tests are based on the fact that the function tally has already been compiled and cached. My argument is based on the fact that the tally function - hopefully - is used so little that it also has to be compiled when the tally version is used. So including the total compile and execution time for both solutions, the limit will be different from what you are arguing for - 23 ms. It is necessary to include all factors and as I wrote yesterday, I do not think that there are many who have a server with so much ram that all plans remain in cache - azure and non-azure! This will be the same as testing a query based on all data being loaded and no one else executing statement on the same server while testing.
July 25, 2020 at 3:41 pm
I will stop now as it is obviously important for you to prove that tally is the only solution. Your tests are based on the fact that the function tally has already been compiled and cached. My argument is based on the fact that the tally function - hopefully - is used so little that it also has to be compiled when the tally version is used. So including the total compile and execution time for both solutions, the limit will be different from what you are arguing for - 23 ms. It is necessary to include all factors and as I wrote yesterday, I do not think that there are many who have a server with so much ram that all plans remain in cache - azure and non-azure! This will be the same as testing a query based on all data being loaded and no one else executing statement on the same server while testing.
Not sure who/whom you are addressing but I agree. "It Depends". If Azure is built in such a fashion that the compiled version of the Tally function is forced out of cache all the time but the need for it is frequent, then other considerations need to be made. You also have to consider which is worse when such limited resources are available... a 23 ms compile time or the number of reads the rCTE causes.
The first consideration, though, would be to figure out why it's being forced out of cache so often. In sounds like either there's way to little memory allocated to the system for what it needs to do or a preponderance of nasty code that is causing cache to clear way too often.
If the code is being used to resolve much larger datasets than the paltry six or so we've been working with, so far, the 23ms can certainly be worth it. Like I said, "It Depends".
In all honesty, if this is a frequent problem with Azure, my next step would be to write a proposal to move to something else but I suspect the problem is not Azure itself. A too low amount of memory or seriously performance/resource challenged code is more likely and that needs to be fixed instead having such small compiled code being forced out of memory all the time.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2020 at 5:25 pm
“One good test is worth a thousand expert opinions.” - Wernher Von Braun
If anyone would like to do a 1M row test... Please feel free to use the following. It will produce 1M rows of randomly generated values ranging in length from 0 to 47 (including brackets) along with values with and without brackets.
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.TestData', 'U') IS NOT NULL
BEGIN DROP TABLE dbo.TestData; END;
CREATE TABLE dbo.TestData (
id int NOT NULL,
bracket_string varchar(50) NOT NULL,
CONSTRAINT pk_testdata_id PRIMARY KEY CLUSTERED(id)
);
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),-- 10
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),-- 100
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n1 b),-- 10,000
cte_Tally (n) AS (
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n3 a CROSS JOIN cte_n3 b-- 100,000,000
)
INSERT dbo.TestData (id, bracket_string)
SELECT
id = t.n,
--rnd.rnd1,
--rnd.rnd2,
--rnd.rnd3,
--s1.string_1,
--s2.string_2,
--s3.string_3,
bracket_string = CONCAT(s1.string_1, '[' + s2.string_2 + ']', s3.string_3)
FROM
cte_Tally t
CROSS APPLY ( VALUES (
ABS(CHECKSUM(NEWID())) % 10 ,
ABS(CHECKSUM(NEWID())) % 30,
ABS(CHECKSUM(NEWID())) % 5
) ) rnd (rnd1, rnd2, rnd3)
CROSS APPLY (
SELECT
STRING_AGG(CHAR(cn1.char_num_1), '')
FROM
cte_Tally t1
CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn1 (rnd_num_1)
CROSS APPLY ( VALUES (CASE WHEN rn1.rnd_num_1 > 57 THEN rn1.rnd_num_1 + 7 ELSE rn1.rnd_num_1 END) ) cn1 (char_num_1)
WHERE
t1.n <= rnd.rnd1
) s1 (string_1)
CROSS APPLY (
SELECT
STRING_AGG(CHAR(cn2.char_num_2), '')
FROM
cte_Tally t2
CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn2 (rnd_num_2)
CROSS APPLY ( VALUES (CASE WHEN rn2.rnd_num_2 > 57 THEN rn2.rnd_num_2 + 7 ELSE rn2.rnd_num_2 END) ) cn2 (char_num_2)
WHERE
t2.n <= rnd.rnd2
) s2 (string_2)
CROSS APPLY (
SELECT
STRING_AGG(CHAR(cn3.char_num_3), '')
FROM
cte_Tally t3
CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn3 (rnd_num_3)
CROSS APPLY ( VALUES (CASE WHEN rn3.rnd_num_3 > 57 THEN rn3.rnd_num_3 + 7 ELSE rn3.rnd_num_3 END) ) cn3 (char_num_3)
WHERE
t3.n <= rnd.rnd3
) s3 (string_3);
--===============================================================================================================================
SELECT
td.id,
td.bracket_string
FROM
dbo.TestData td;
July 25, 2020 at 6:14 pm
My previously posted code reformatted into an iTVF and replacing the tally function with an inline tally...
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
CREATE FUNCTION dbo.split_bracket_values
/* ===================================================================
07/25/2020 JL, Created: Just a test
=================================================================== */
--===== Define I/O parameters
(
@string varchar(50)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT
combo_value = CONCAT(sec.beg, bv.bracket_val, sec.[end])
FROM
( VALUES (
NULLIF(CHARINDEX('[', @string), 0),
NULLIF(CHARINDEX(']', @string), 0)
) ) br ([open], [close])
CROSS APPLY ( VALUES (
REPLACE(SUBSTRING(@string, 1, ISNULL(br.[open], 8000)), '[', ''),
REPLACE(SUBSTRING(@string, br.[open], br.[close] - br.[open]), '[', ''),
REPLACE(SUBSTRING(@string, ISNULL(br.[close], 8000), 8000), ']', '')
) ) sec (beg, mid, [end])
CROSS APPLY (
SELECT TOP (LEN(ISNULL(sec.mid, 0)))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM ( VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
) x (n)
) t (n)
CROSS APPLY ( VALUES (SUBSTRING(sec.mid, t.n, 1)) ) bv (bracket_val);
GO
Test results in next post...
July 25, 2020 at 6:22 pm
My test harness...
SET NOCOUNT ON;
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT(CONCAT(CHAR(13), CHAR(10), N' wait a moment...', CHAR(13), CHAR(10)));
WAITFOR DELAY '00:00:01';
GO
DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(CONCAT(N' Start Time: ', @_clock_start, N' Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 1'),
REPLICATE(N' ', 100)), 148), N'¦', CHAR(13), CHAR(10), N'+', REPLICATE(N'-', 148), N'+')); SET STATISTICS IO ON;
-- ____________________________________________________________________________________________________________________________________________
-- ?????? place tsql here ????????????????????????????????????????????????????????????
DECLARE
@cv varchar(50);
SELECT
@cv = sbv.combo_value
FROM
dbo.TestData td
CROSS APPLY dbo.split_bracket_values(td.bracket_string) sbv
-- ?????? place tsql here ????????????????????????????????????????????????????????????
-- ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(STUFF(CONCAT(N' Finish Time: ', @_clock_stop, N' Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
/ 1000000.0, N' secs. ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'¦
+', REPLICATE(N'-', 148), N'+'));
GO
And the results. Note that the best time (52 secs) was achieved by adding a nonclustered index keyed on the "bracket_string" column.
first execution (cold cache with capture actual plan on and no index to prevent sort node)
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2020-07-25 13:53:16.5672257 Test Name: Test Query 1 ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 3828, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestData'. Scan count 1, logical reads 4965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2020-07-25 13:54:22.2230683 Duration: 65.655840 secs. 65655.843000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Completion time: 2020-07-25T13:54:22.3406983-04:00
second execution (cold cache with capture actual plan on and the following index: "CREATE NONCLUSTERED INDEX ix_td_bracketstring ON dbo.TestData(bracket_string)")
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2020-07-25 14:01:15.6915205 Test Name: Test Query 1 ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestData'. Scan count 1, logical reads 4599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2020-07-25 14:02:16.4727630 Duration: 60.781240 secs. 60781.243000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Completion time: 2020-07-25T14:02:16.5635195-04:00
3rd execution: cold cache DBCC DROPCLEANBUFFERS... no nc index.
wait a moment...
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2020-07-25 14:10:07.1936631 Test Name: Test Query 1 ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 3828, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestData'. Scan count 1, logical reads 4965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2020-07-25 14:11:09.4280271 Duration: 62.234360 secs. 62234.364000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Completion time: 2020-07-25T14:11:09.3477543-04:00
4th execution: cold cache DBCC DROPCLEANBUFFERS... with nc index
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2020-07-25 14:13:45.4726695 Test Name: Test Query 1 ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestData'. Scan count 1, logical reads 4599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2020-07-25 14:14:37.7694618 Duration: 52.296790 secs. 52296.792000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Completion time: 2020-07-25T14:14:37.7017084-04:00
July 25, 2020 at 7:03 pm
Jason A. Long the "s3 string" characters which appear after the brackets do not appear in any of the OP's example rows. Imo that's a made up requirement which most of the solutions don't address. I removed the s3 string and timestamped the code as follows
IF OBJECT_ID('dbo.TestData', 'U') IS NOT NULL
BEGIN DROP TABLE dbo.TestData; END;
go
CREATE TABLE dbo.TestData (
id int NOT NULL,
bracket_string varchar(50) NOT NULL,
CONSTRAINT pk_testdata_id PRIMARY KEY CLUSTERED(id)
);
go
declare
@test_start datetime2=sysutcdatetime();
print ('TestData start: '+cast(@test_start as varchar));
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),-- 10
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),-- 100
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n1 b),-- 10,000
cte_Tally (n) AS (
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n3 a CROSS JOIN cte_n3 b-- 100,000,000
)
INSERT dbo.TestData (id, bracket_string)
SELECT
id = t.n,
--rnd.rnd1,
--rnd.rnd2,
--rnd.rnd3,
--s1.string_1,
--s2.string_2,
--s3.string_3,
bracket_string = CONCAT(s1.string_1, '[' + s2.string_2 + ']')
FROM
cte_Tally t
CROSS APPLY ( VALUES (
ABS(CHECKSUM(NEWID())) % 10 ,
ABS(CHECKSUM(NEWID())) % 30,
ABS(CHECKSUM(NEWID())) % 5
) ) rnd (rnd1, rnd2, rnd3)
CROSS APPLY (
SELECT
STRING_AGG(CHAR(cn1.char_num_1), '')
FROM
cte_Tally t1
CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn1 (rnd_num_1)
CROSS APPLY ( VALUES (CASE WHEN rn1.rnd_num_1 > 57 THEN rn1.rnd_num_1 + 7 ELSE rn1.rnd_num_1 END) ) cn1 (char_num_1)
WHERE
t1.n <= rnd.rnd1
) s1 (string_1)
CROSS APPLY (
SELECT
STRING_AGG(CHAR(cn2.char_num_2), '')
FROM
cte_Tally t2
CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn2 (rnd_num_2)
CROSS APPLY ( VALUES (CASE WHEN rn2.rnd_num_2 > 57 THEN rn2.rnd_num_2 + 7 ELSE rn2.rnd_num_2 END) ) cn2 (char_num_2)
WHERE
t2.n <= rnd.rnd2
) s2 (string_2);
declare
@test_end datetime2=sysutcdatetime();
print ('TestData end: '+cast(@test_end as varchar));
print ('Elapsed time: '+cast(datediff(s, @test_start, @test_end) as varchar));The result
TestData start: 2020-07-25 18:57:54.8187398
(1000000 row(s) affected)
TestData end: 2020-07-25 18:59:29.7256354
Elapsed time: 95
So it takes 95 seconds to generate the million row test table.
The itvf for splitting expanded to 50 characters:
drop function if exists dbo.fnTIDprefix_Expand;
go
create function dbo.fnTIDprefix_Expand(
@TIDprefix varchar(50))
returns table with schemabinding as
return
select
iif(br.br_start>0, concat(left(@TIDprefix, br.br_start-1), substring(@TIDprefix, n+br.br_start, 1)), @TIDprefix) TIDprefix_Expanded
from
(select
charindex('[', @TIDprefix) br_start,
charindex(']', @TIDprefix) br_end) br
cross apply
dbo.fnTally(1, iif(br.br_start>0, (br.br_end-br.br_start-1), 1)) t;
go
To run against the test table and insert into a temp table
drop table if exists #TestData
go
CREATE TABLE #TestData (
id int NOT NULL,
split_string varchar(50) NOT NULL);
go
declare
@split_start datetime2=sysutcdatetime();
print ('Split start: '+cast(@split_start as varchar));
insert #TestData
select
td.id,
tx.TIDprefix_Expanded
from
dbo.TestData td
cross apply
dbo.fnTIDprefix_Expand(td.bracket_string) tx;
declare
@split_end datetime2=sysutcdatetime();
print ('Split end: '+cast(@split_end as varchar));
print ('Elapsed time: '+cast(datediff(s, @split_start, @split_end) as varchar));
Results
Split start: 2020-07-25 19:02:02.0859428
(14543485 row(s) affected)
Split end: 2020-07-25 19:02:42.6643727
Elapsed time: 40
So 95 seconds to create 1 million rows of test data and 40 seconds to split into 14,5 million rows in a temp table.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 25, 2020 at 8:10 pm
The OP bailed out of the conversation long ago but not before establishing a constant pattern of moving the goal posts. The "after string" simply presented a more robust solution.
Your use of a nested function (dbo.fnTally) makes impossible to for anyone else to recreate and to a head to head test on the same machine. That is the reason I dumped the use use of dbo.tfn_Tally from my original code when creating my version of the function.
Without any of the STASTICS_IO information, we can't even see if one function is doing more work than the other,
With all that said, 40 secs is an excellent time on any machine, so well done. Especially considering that you are writing the values to a #temp table rather than doing a @variable dump.
July 25, 2020 at 8:34 pm
Steve,
After plugging in one of my in house tally functions (dbo.tfn_Tally_10k) into both functions, your version is MUCH faster.
Using my function, on my DEV box, using a @variable dump... The time to execute your version dropped to 26 seconds., while mine remained in the 50 second range.
Very nice!!!
Here are some results using my harness...
wait a moment...
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2020-07-25 16:32:11.7645910 Test Name: dbo.fnTIDprefix_Expand ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'TestData'. Scan count 1, logical reads 4714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2020-07-25 16:32:40.6084923 Duration: 28.843900 secs. 28843.901000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
wait a moment...
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2020-07-25 16:32:42.6241246 Test Name: dbo.split_bracket_values ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'Worktable'. Scan count 280, logical reads 1874821, physical reads 0, read-ahead reads 3579, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestData'. Scan count 1, logical reads 4714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2020-07-25 16:33:41.4994099 Duration: 58.875280 secs. 58875.285000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Completion time: 2020-07-25T16:33:41.4320004-04:00
July 25, 2020 at 9:07 pm
Jason A. Long the "s3 string" characters which appear after the brackets do not appear in any of the OP's example rows. Imo that's a made up requirement which most of the solutions don't address.
Although I agree that the presence of String3 was not the OP's request(s), your code also produces things that are not in the OP's original or secondary posting of data. There are times when only String1 is present and times when only string 2 is present. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2020 at 9:42 pm
Aaah that was true before I borrowed the charindex section of Phil's code.
drop function if exists dbo.fnTIDprefix_Expand;
go
create function dbo.fnTIDprefix_Expand(
@TIDprefix varchar(50))
returns table with schemabinding as
return
select
iif(br.br_start>0, concat(left(@TIDprefix, br.br_start-1), substring(@TIDprefix, n+br.br_start, 1)), @TIDprefix) TIDprefix_Expanded
from
(select
charindex('[', @TIDprefix) br_start,
charindex(']', @TIDprefix) br_end) br
cross apply
dbo.fnTally(1, iif(br.br_start>0, (br.br_end-br.br_start-1), 1)) t;
go
select * from dbo.fnTIDprefix_Expand('AC[123]')
union all
select * from dbo.fnTIDprefix_Expand('AXX');
Results
TIDprefix_Expanded
AC1
AC2
AC3
AXX
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 26, 2020 at 8:35 pm
Steve, I wasn't talking about the expansive output. I was talking about the test code generator.
Shifting gears, the OP provided no example of a left string having more than one character. I wonder what the requirement for output would actually be for that because, technically and with any result > 2 characters, there is no way to tell what the left or right strings would be.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply