Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: Increase performance for bcp

    Hi,

    I am using bcp utility to extract data from database. I have extracted 14324590 rows from sybase database but it takes 17 minutes to download.

    Could you please give suggest to how to increase performance for bcp out utility?


    Thanks,
    Michael Holding.Y

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Try using bigger or smaller batch-sizes (more or less rows per io), the -b parameter.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Martijnvs View Post
    Try using bigger or smaller batch-sizes (more or less rows per io), the -b parameter.

    I guess the OP is extracting data, so in that case -b has no relation as its only related to bcp in. The only thing I can think of using a higher packet size to see if it helps.

    Example:

    bcp pubs2..titles out table_out -A 2048

    sets the packet size to 2048 bytes for this bcp session. packet_size must be between the values of the default network packet size and maximum network packet size configuration variables, and it must be a multiple of 512.

    Use network packet sizes larger than the default to improve the performance of large bulk-copy operations.

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    In addition:
    Also try and drop index on the table you are trying to bcp out if you have any.

    Thanks,
    Meet

  5. #5
    Join Date
    Jun 2010
    Posts
    2
    Thanks,

    i used -A 8192 parameter in bcp out. it gives better performance compare then previous bcp opeeration.

    Without -A 8192 parameter : it takes 17 minutes for 1.5 cr records
    with -A 8192 parameter:it takes 8 minutes for 1.5 cr records.


    Thanks for your help!

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Also, do you really need to be extracting 15m rows from the database on a regular basis? I can understand this as a one off but it would be interesting to find out why you're doing this more regularly.

Posting Permissions

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