Post reply

Data in tthe same order in which it is inserted

  • March 31, 2012 at 6:21 am

    #1467294

    PS I do not use BULK INSERT, but I am not finding any articles claiming MAXDOP 1 will have the desired effect. I am reading that TABLOCK may be part of the puzzle. Any references or demo code on using BULK INSERT with MAXDOP 1 to help maintain an ordered insert?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 31, 2012 at 7:46 am

    #1467305

    opc.three (3/31/2012)

    PS I do not use BULK INSERT, but I am not finding any articles claiming MAXDOP 1 will have the desired effect. I am reading that TABLOCK may be part of the puzzle. Any references or demo code on using BULK INSERT with MAXDOP 1 to help maintain an ordered insert?

    Not that I know of. I'm going 100% based on personal experience on this one. I don't have any demo code on the subject, either. Guess I've got another thing to do.

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

  • March 31, 2012 at 8:01 am

    #1467306

    opc.three (3/31/2012)

    Jeff Moden (3/30/2012)

    opc.three (3/30/2012)

    It will actually do it properly, provided the commit size is 1

    It's been a while but, IIRC, if you set MAXDOP to 1 and you have no indexes to start with, you can get an IDENITY column to number the rows correctly. Of course, that pretty much defines a staging table.

    Sorry, I missed this post because it came in at the time when I was writing my last post and I just saw the email notification.

    I think you're right, Jeff! I knew there had to be other ways as well. If my understanding is correct the MAXDOP 1 hint will force SQL Server to BULK INSERT the data using only 1 CPU, thereby guaranteeing no parallelism, and no commits happening out of order from what is in the file. As far as I know this type of setting is not available when using SSIS or bcp (although -h ORDER BY looks interesting) which is why you must set the commit size to 1. If it were available in SSIS or bcp I would love to know.

    Again... nothing concrete on this but I've not had a problem with it. I think, just by it's nature, BCP is "single threaded" meaning that parallelism doesn't occur when using it even for batch sizes larger than 1. I could certainly be wrong but, like I said, I've never experienced a problem with "out of order" inserts with it even when it's called through something else like xp_CmdShell.

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

  • March 31, 2012 at 8:49 am

    #1467310

    Jeff Moden (3/31/2012)

    opc.three (3/31/2012)

    Jeff Moden (3/30/2012)

    opc.three (3/30/2012)

    It will actually do it properly, provided the commit size is 1

    It's been a while but, IIRC, if you set MAXDOP to 1 and you have no indexes to start with, you can get an IDENITY column to number the rows correctly. Of course, that pretty much defines a staging table.

    Sorry, I missed this post because it came in at the time when I was writing my last post and I just saw the email notification.

    I think you're right, Jeff! I knew there had to be other ways as well. If my understanding is correct the MAXDOP 1 hint will force SQL Server to BULK INSERT the data using only 1 CPU, thereby guaranteeing no parallelism, and no commits happening out of order from what is in the file. As far as I know this type of setting is not available when using SSIS or bcp (although -h ORDER BY looks interesting) which is why you must set the commit size to 1. If it were available in SSIS or bcp I would love to know.

    Again... nothing concrete on this but I've not had a problem with it. I think, just by it's nature, BCP is "single threaded" meaning that parallelism doesn't occur when using it even for batch sizes larger than 1. I could certainly be wrong but, like I said, I've never experienced a problem with "out of order" inserts with it even when it's called through something else like xp_CmdShell.

    I think you're right about bcp being single-threaded in terms of reading the file line by line in sequence, it's SQL Server parallelism that creates the issues with unordered commits.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 31, 2012 at 8:56 am

    #1467311

    http://connect.microsoft.com/SQLServer/feedback/details/278452/there-needs-to-be-a-way-to-preserve-order-when-bulk-loading-a-file

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 5 posts - 31 through 34 (of 34 total)

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