none
bulk insert RRS feed

  • Question

  • There are over 15 million records in a table called tblMain
    The first four fields are as follows:

    ...
    CONSTRAINT [PK_tblMain] PRIMARY KEY CLUSTERED
    (
     [MyDate] ASC,
     [MyID] ASC,
     [MyRS] ASC,
     [MyC] ASC
    )

    There is a non clustered index on ValueDate

    Each download is around 20 records except one which is around 100 records.
    Each download is done using bulk insert...
    If I do the downloads one by one from smallest to largest file then all works fine BUT if I first download the biggest file, then I get a timeout error.

    Do you know why I get this timeout error onlt when I first download the big file?
    Thanks

    Monday, January 9, 2012 1:59 PM

Answers

  • Hmm, I think we insert the data from the file into a table 

     

    -- Run the insert with appropriate hints

    BULK INSERT db.dbo.tablename FROM 'E:\file.csv'

    WITH (

       ROWS_PER_BATCH = 50000000,

       DATAFILETYPE = 'char',

       FIELDTERMINATOR = ',',

       TABLOCK

    )

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 9, 2012 2:08 PM
    Answerer
  • There may be some confusion of terminology here. When you say "bulk insert" do refer to the specifically the BULK INSERT command, or do you just mean you load many rows at a time?

    If you are indeed using BULK INSERT, the question may seem silly, but we have been fooled more than once by people who have used "bulk insert" to insert many rows at time with the INSERT statement.

    For the record, BULK INSERT reads file a loads them directly to a table in SQL Server. It is specifially designed for large filers with maybe million of records. Using it for 20-row files is a bit of overkill. Then again, if you have a file lying there, reading it with BULK INSERT is legit, as long as permissions are not a problem.

    You say you get a timeout. I assume that you get the time out in the application, as there is no timeout in BULK INSERT.

    In any case, it would help if you could clarify a number of things:
    1) What you mean with "bulk insert".
    2) What you mean with "download" (beause that carries no meaning to use who don't know your application.)
    3) Exactly where you get the timeout.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 9, 2012 3:23 PM
  • If it is not blocking, I don't know what the reason might be. I can't see that the order you load the files matter, not the least when the files are that small.

    Please keep in mind that there is a ton of things that we don't know about your setup. For instance the word "download" carries no meaning to us.

    Unfortuantely, with the information you have shared, there is not much we can do to help you. You will need to debug a little harder. Hav you verified that the timeout appears when you run the BULK INSERT statement?

    Where are the files located? On a local disk, or a network share?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by arkiboys Tuesday, January 24, 2012 11:46 AM
    Tuesday, January 10, 2012 10:00 AM
  • BULK INSERT from file to database table is commonly referred to as UPLOAD.

    Consider using the SSIS Import/Export Wizard instead of BULK INSERT:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Thursday, January 19, 2012 2:49 PM
    Moderator

All replies

  • What do you mean by 'download'? A queryout command to move the data into a file? 
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 9, 2012 2:01 PM
    Answerer
  • What do you mean by 'download'? A queryout command to move the data into a file? 
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    By download I am referring to a file to be downloaded.
    Monday, January 9, 2012 2:04 PM
  • Hmm, I think we insert the data from the file into a table 

     

    -- Run the insert with appropriate hints

    BULK INSERT db.dbo.tablename FROM 'E:\file.csv'

    WITH (

       ROWS_PER_BATCH = 50000000,

       DATAFILETYPE = 'char',

       FIELDTERMINATOR = ',',

       TABLOCK

    )

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 9, 2012 2:08 PM
    Answerer
  • Hmm, I think we insert the data from the file into a table 

     

    -- Run the insert with appropriate hints

    BULK INSERT db.dbo.tablename FROM 'E:\file.csv'

    WITH (

       ROWS_PER_BATCH = 50000000,

       DATAFILETYPE = 'char',

       FIELDTERMINATOR = ',',

       TABLOCK

    )

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    What does this sql refer to exactly? i.e. tablock, rowsperbatch, etc?

    Thanks

    Monday, January 9, 2012 2:58 PM
  • There may be some confusion of terminology here. When you say "bulk insert" do refer to the specifically the BULK INSERT command, or do you just mean you load many rows at a time?

    If you are indeed using BULK INSERT, the question may seem silly, but we have been fooled more than once by people who have used "bulk insert" to insert many rows at time with the INSERT statement.

    For the record, BULK INSERT reads file a loads them directly to a table in SQL Server. It is specifially designed for large filers with maybe million of records. Using it for 20-row files is a bit of overkill. Then again, if you have a file lying there, reading it with BULK INSERT is legit, as long as permissions are not a problem.

    You say you get a timeout. I assume that you get the time out in the application, as there is no timeout in BULK INSERT.

    In any case, it would help if you could clarify a number of things:
    1) What you mean with "bulk insert".
    2) What you mean with "download" (beause that carries no meaning to use who don't know your application.)
    3) Exactly where you get the timeout.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 9, 2012 3:23 PM
  • There may be some confusion of terminology here. When you say "bulk insert" do refer to the specifically the BULK INSERT command, or do you just mean you load many rows at a time?

    If you are indeed using BULK INSERT, the question may seem silly, but we have been fooled more than once by people who have used "bulk insert" to insert many rows at time with the INSERT statement.

    For the record, BULK INSERT reads file a loads them directly to a table in SQL Server. It is specifially designed for large filers with maybe million of records. Using it for 20-row files is a bit of overkill. Then again, if you have a file lying there, reading it with BULK INSERT is legit, as long as permissions are not a problem.

    You say you get a timeout. I assume that you get the time out in the application, as there is no timeout in BULK INSERT.

    In any case, it would help if you could clarify a number of things:
    1) What you mean with "bulk insert".
    2) What you mean with "download" (beause that carries no meaning to use who don't know your application.)
    3) Exactly where you get the timeout.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi,
    Replies:
    1- BULK INSERT
    2- bulk insert the file into the table
    3- in the application code.

    Monday, January 9, 2012 3:29 PM
  • Hi All,

    As I understood correctly, he is referring to Bulk Insert Task in the Control Flow. Not Bulk Insert SQL. He has some performance issue with it.

    Monday, January 9, 2012 7:48 PM
  • Thanks for the clarification.

    Loading 100 rows with BULK INSERT should in no way take 30 seconds (which I assume is your default, since it is the default). The most likely reason is blocking.

    A simple way to check for blocking is to run sp_who2 from a query window. If you see a number in the Blk column, that spid is blocking the spid on that row.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 9, 2012 7:49 PM
  • Hi All,

    As I understood correctly, he is referring to Bulk Insert Task in the Control Flow. Not Bulk Insert SQL. He has some performance issue with it.


    No,
    this is to do with bulk insert in t-sql
    Thanks
    Tuesday, January 10, 2012 9:26 AM
  • Thanks for the clarification.

    Loading 100 rows with BULK INSERT should in no way take 30 seconds (which I assume is your default, since it is the default). The most likely reason is blocking.

    A simple way to check for blocking is to run sp_who2 from a query window. If you see a number in the Blk column, that spid is blocking the spid on that row.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    There is no block.
    Note that there are several files to be downloaded every morning.
    If I start with the smallest and work my way up to the largest file then all bulk insert queries work.

    BUT, If I start with the largest file then there is a timeout.

    Tuesday, January 10, 2012 9:27 AM
  • If it is not blocking, I don't know what the reason might be. I can't see that the order you load the files matter, not the least when the files are that small.

    Please keep in mind that there is a ton of things that we don't know about your setup. For instance the word "download" carries no meaning to us.

    Unfortuantely, with the information you have shared, there is not much we can do to help you. You will need to debug a little harder. Hav you verified that the timeout appears when you run the BULK INSERT statement?

    Where are the files located? On a local disk, or a network share?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by arkiboys Tuesday, January 24, 2012 11:46 AM
    Tuesday, January 10, 2012 10:00 AM
  • BULK INSERT from file to database table is commonly referred to as UPLOAD.

    Consider using the SSIS Import/Export Wizard instead of BULK INSERT:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Thursday, January 19, 2012 2:49 PM
    Moderator
  • Thank you all.
    Tuesday, January 24, 2012 11:46 AM