Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: how to speed up table data transfer thru bcp in?

    for bcp in,
    1. use fixed length format file or delimitered file?
    2. table w/o index including primary key?
    3. sort the text file before bcp in (will it speed up indexes creation after data uploading?)


    which pt will or will not improve the overall bcp in processing?

    thx...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ymho, please do not cross-post.

    Are you using Sybase or SQL Server?
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    certainly if you drop all indexes before the import it will go faster. generally if I am importing more than 30% or so of the rows already in the table, I'll drop the indexes and recreate them after import.

  4. #4
    Join Date
    Jul 2006
    Posts
    115
    georgev, we have both sybase & sql server and hv same questions on data loading. donno will they perform in difference or not...

    jezemine, how about the primary key/clustered index? still drop & recreate it after data import.

    Pls advise

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yes, I would drop and recreate a PK or clustered index. that is unless you happen to know your import file is already sorted - this has never been the case for me though.

    I find that SQL 2005 is quite speedy at creating indexes. for 1 billion rows usually it's a matter of an hour or two on the servers I work with.

Posting Permissions

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