• I got something about the use of an illegal operand in a TOP clause when I used Lynn's code. And, I'm sorry, I don't have the time to troubleshoot it.

    When I used my function, it took 34 seconds and returned the correct number of rows. I spot checked the first 2, last 2 and a couple of rows in the middle and everything seems to be fine. A secondary split at the comma level took about 22 seconds and returned the correct number of rows and data, as well.

    However, before I post such code, I have to tell you that splitting over a million charcters in 56 seconds is still horribly slow... Tally tables and MAX datatypes just don't get along and just using MAX makes the code twice as slow even if it fits within the 8k limit.

    So let me ask... where does this data come from? Is it in a file somewhere because if it is, it'll take less than half a second to load the data in its final form with my old friend, BULK INSERT. For example, here's the BULK INSERT code I used on the example file given by the OP (stored in C:\Temp)

    CREATE TABLE #MyHead

    (

    Col1 VARCHAR(10),

    Col2 DATETIME,

    Col3 DECIMAL(9,2),

    Col4 DECIMAL(9,2),

    Col5 DECIMAL(9,2),

    Col6 DECIMAL(9,2),

    Col7 BIGINT,

    Col8 DECIMAL(9,2)

    )

    ;

    BULK INSERT #MyHead

    FROM 'C:\Temp\DataSample.txt'

    WITH (

    CODEPAGE = 'RAW',

    DATAFILETYPE = 'CHAR',

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '#',

    TABLOCK

    )

    ;

    ... and here's the CPU and duration stats for that tiny bit of nasty fast code...

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 3 ms.

    SQL Server Execution Times:

    CPU time = 437 ms, elapsed time = 475 ms.

    (20485 row(s) affected)

    And that's on an 8 year old desktop box. 😉

    If this data starts out in a file, then use BULK INSERT to load it. If the data DOESN'T start out in a file, consider putting it there before you load it.

    --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)