Convert CSV values in three columns to rows
-
I've also made a comparison of the CLR methods when using parallel vs forced serial plan. I run it on my Core2 Quad 2.5 GHz. and the CLR beneffits a lot from the parralel plans as shows below test. But even in case of forced serial plan the results are still a lot better than the Tally in my previous tests. Run on the table with 1333 items per line.
--============== PARALLEL PLANS ======GO
--CLR fn_SPlitString3
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT
@RowNum = D.RowNum,
@ItemNumber = V.RowID,
@ItemValue = V.Value
FROM dbo.CsvTest3 D
CROSS APPLY dbo.fn_SplitString3(D.CsvParameter, ',', 10) V
GO
--CLR RegEx
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT
@RowNum = D.RowNum,
@ItemNumber = V.RowID,
@ItemValue = V.Value
FROM dbo.CsvTest3 D
CROSS APPLY dbo.fn_RegExMatches2(D.CsvParameter, '\d+') V
GO
--============== SERIAL PLANS (FORCED)======
GO
--CLR fn_SPlitString3
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT
@RowNum = D.RowNum,
@ItemNumber = V.RowID,
@ItemValue = V.Value
FROM dbo.CsvTest3 D
CROSS APPLY dbo.fn_SplitString3(D.CsvParameter, ',', 10) V
OPTION(MAXDOP 1)
GO
--CLR RegEx
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT
@RowNum = D.RowNum,
@ItemNumber = V.RowID,
@ItemValue = V.Value
FROM dbo.CsvTest3 D
CROSS APPLY dbo.fn_RegExMatches2(D.CsvParameter, '\d+') V
OPTION(MAXDOP 1)
GO
Profiler results:
Corresponding plans:
-
Very cool, Pavel. Thanks.
--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) -
Just thought I'd do a followup on this. I've managed to fix the problem with the apparent Tally Table slowdown as it approaches 8k. I've got an article in the works.
As a side bar, I sure do appreciate all of the testing that some of you folks went through with me on this thread. Thank you all again.
--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)
Viewing 3 posts - 46 through 47 (of 47 total)
You must be logged in to reply to this topic. Login to reply