Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Posts
    24

    Unanswered: Improving bcp utility performance

    Hi,

    We are using bcp utility to load data from a file to the database.The number of records dumped will be 1 million rows for every 3 minutes.

    It is like for every 3 minutes the file will contain 1 million records and we will dump into database.We will create a new table for every thirty minutes.

    I kept the recovery model as simple so the logging overhead will be removed.

    bcp is loading only 3000 rows per second so to import 1 million rows it is taking 6 minutes of time.

    Please advice me how to increase the performance of bcp.

    Thanks
    Kiran

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    disable/drop indexes before loading, and load in batches, then enable/rebuild indexes after load. Are you eventually going to end up with 1000s of tables, or do they get dropped after that data is processed/transformed ? just a concern.
    Last edited by PMASchmed; 07-10-09 at 11:59.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Also make sure the file to loqd/unload is to a local drive to the server as well
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2008
    Posts
    24
    Thanks for your replies

    For every thirty minutes i will create a new table and the old table will be closed.

    Actually i followed your suggestion disabling the index --> load --> enabling the index, that was great.Now i am able to dump 21000 rows per second.If a client query is accessing that table while loading the data it will not have any indexes so it will take a very long to process.Is there any way i can obtain the lock to the table while loading the data so it will not be available for read ?

    Kiran

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You don't happen to have any triggers on this table, do you?

    Also, what version of SQL Server is this? I attended a presentation on large database performance, where they demonstrated partition switching in SQL 2008. Check the ALTER TABLE article in BOL.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A suitable clustered index should be faster than a heap I believe.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •