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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 46 through 48 (of 48 total)

You must be logged in to reply to this topic. Login to reply