Post reply

Import Performance

  • July 31, 2009 at 10:53 am

    #140349

    Hi Guys

    I have a package set up with a data flow task, connection set up to an AS400 db, and a pretty straight forward insert into an existing empty table, keyed. An isert of about 1.5 million rows takes 90 minutes. If i use the import/export wizard it takes 10-15 minutes for the exact same data to the same table.

    Any thoughts??

  • July 31, 2009 at 11:05 am

    #1033079

    I should have checked the topics first........i see i'm not the only one.

    AHHHHHHHHHHHHHHHHH

  • July 31, 2009 at 11:34 am

    #1033094

    Save the package from the Import/Export Wizard, and see how it compares to the one you created. Maybe some critical settings like rows/buffer are different.

    There is a difference between running the package in the IDE and running it separately with dtexec. If you run the Import/Export wizard package from Management Studio, it doesn't have the overhead of running inside the Business Intelligence Design Studio IDE.

  • July 31, 2009 at 11:50 am

    #1033100

    Good thought scott -- tyvm

  • July 31, 2009 at 6:21 pm

    #1033221

    I believe the two methods use two different drivers.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 5, 2009 at 8:39 am

    #1034835

    wanted to post in case anyone was interested in outcome.

    I went through the set up with the import/export wizard and saved it to a file and added the package to a new project.

    Checked the properties, most importantly the connection, and to the best of my ability the properties were all the same.

    However, much better results.....6million rows in 37 minutes.....that would have taken 4-5 hours with the original package.

    thanks for the ideas. Haven't really solved the original problem, but don't argue with success!!!!!

  • August 5, 2009 at 8:49 am

    #1034850

    PeterG (8/5/2009)

    wanted to post in case anyone was interested in outcome.

    I went through the set up with the import/export wizard and saved it to a file and added the package to a new project.

    Checked the properties, most importantly the connection, and to the best of my ability the properties were all the same.

    However, much better results.....6million rows in 37 minutes.....that would have taken 4-5 hours with the original package.

    thanks for the ideas. Haven't really solved the original problem, but don't argue with success!!!!!

    Good job, but that's also why I don't use SSIS or DTS. BULK INSERT will import a 5.1 million row, 20 column file in 60 seconds flat. That doesn't include the upsert to the final table, but it does include a lot of error checking.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply