Convert CSV values in three columns to rows

• Pavel Pawlowski

  Ten Centuries

  Points: 1244

  More actions

  October 7, 2010 at 12:48 pm

  #1233878

  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:

  

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  October 8, 2010 at 6:30 am

  #1234182

  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)
  

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  April 1, 2011 at 6:30 pm

  #1306742

  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)