Post reply

Would Bulk Insert Help??

  • February 27, 2014 at 10:21 am

    #286198

    Hello -

    Currently I have a SSIS package that queries data from one SQL server (A) via Select SQL Command and Inserts it into another SQL server (B) blank table. This happens every two hours.

    SQL Server (A) is our production server and Server (B) is a sort of data warehouse that people connect to via Excel to get their data.

    The issue I'm having is its taking about 2 1/2 hours to complete to insert 817,643 rows. Would I be able to work in Bulk Insert to speed up the process? I'm not sure how Bulk Insert works. Or are there any other suggestions I could do with out having to recreate the wheel?

    Thanks,

    David

  • February 27, 2014 at 10:23 am

    #1692775

    What do you mean by 'Select SQL command'?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • February 27, 2014 at 10:35 am

    #1692784

    In the SSIS package I have a Select Statement to query the SQL Server (A)

  • February 27, 2014 at 10:40 am

    #1692787

    Bulk insert is to insert values from a file.

    You need to identify if the problem is the source or the destination. How long does it take the query to execute? Do you have many indexes on your destination table? How's the network connection?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 13, 2014 at 8:10 am

    #1696800

    Indexing is definately one I would look at. If you have a large number of indexes on the destination table then it will take a while to write the data. you might do better to drop (or disable) the indexes whilst you write and then rebuild them afterwards. If you can force the SELECT query to get your data in the same order as your clustered index you will speed up the insert significantly because there will be no paging issues.

  • March 13, 2014 at 4:51 pm

    #1696989

    david.ostrander (2/27/2014)

    In the SSIS package I have a Select Statement to query the SQL Server (A)

    How many rows does it select at one time? Better yet, can you post the query?

    --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 6 posts - 1 through 5 (of 5 total)

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