SQL Server Performance

Need Better Performance On Large Import

Discussion in 'SQL Server 2008 Integration Services' started by dndaughtery, Jun 10, 2009.

  1. dndaughtery New Member

    I have a flat file where I am importing 18.9 million records. I am using the following transformations inside the package:
    ScriptTask, Unpivot, Union All Lookups, dataConversion.
    I am using the "Out of the box" settings on the dataflow properties. I tried adjusting them by setting the maxRows to 500,000 and updateing the DefaultBufferSize to 100485760 and set the engine threads to 16 since I have 4 quad core processors but all of this made it slower than the default settings. My process is taking 14 hours. Is this good? I sure hope not. What can I do to boost the performance?
    dndaughtery, Jun 10, 2009
    #2

  2. satya Moderator

    Welcome to the forums.
    Are you directly importing the file to the base table itself?
    If so the indexes on that table will have such a performance degradation, so in this case better to put them in STAGING table then clean up the data as per your requirement then import back to base table.
    satya, Jun 11, 2009
    #3

Share This Page