none
how two transfer data from OLEDB source to OLEDB destination with accepted performance?

    Question

  • Hi dears

    I have 2 physically separated databases to save my data daily and monthly:

    Daily: it saves only daily data

    Monthly: it saves monthly data with some indexes

    I am using the following data flow task to copy data from daily database to monthly database:

     

    But it is very slow and takes too long to transfer data, how can I improve performance? Is there any better solution to transfer data?

    I appreciate your attentions 

    Monday, January 07, 2013 3:40 PM

Answers

  • 1. Why do you think there's unnecessary caching? You're just using a simple source to destination, caching isn't necessary.

    2. If the destination indexes are problematic, you can drop them before the load and add them back later. Another option - if you have Enterprise edition - is to insert in an empty partition and then partition switch this partition into your final destination table.

    3. If you use the Fast Load option in the OLE DB destination you are using bulk inserts.

    Also check the recovery model of your destination database. Try setting it to simple to minimize logging.

    If SSIS is on another server, you're transferring all the data from the server A (as a source) to server B (the SSIS server) and then back to server A (as a destination). This can have quite a network impact.

    Maybe it's just easier to use TSQL to transfer the data.


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 7:10 AM
    Tuesday, January 08, 2013 11:46 AM

All replies

  • we need more details but what i would do is

    1- read http://henkvandervalk.com/speeding-up-ssis-bulk-inserts-into-sql-server/comment-page-1#comment-1976

    2- and http://henkvandervalk.com/reading-as-fast-as-possible-from-a-table-with-ssis-part-ii

    3- Use partitioned table specially in the destination

    4- paralel loading

    5- if using SQL 2012 check what "OFFSET and FETCH" is and how can you use it in your packages

    6- good luck


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Monday, January 07, 2013 4:24 PM
  • There's alot of possible reasons it's slow Farhad.  My first question would be are the source and destination on seperate servers?  Could just be network performance. 

    Try using task manager/performance tab/resource monitor during a load and see if memory or disk I/O is bottlenecked during the load.

    Another question: is SSIS server on the same physical machine as the DB server?  If so, then they could be competing for resources and disk spindles.  It could be a simple "your hardware resources are insufficent".  SSIS will cache in memory as much as it can and then will start spooling and writing temp files while it reads rows.  This can quickly consume more than a few gigs of memory if your rows are large and contain a large number of rows.

    Monday, January 07, 2013 10:13 PM
  • 1) For testing the speed of the source you could temporary replace the destination with for example a row count transformation. If this is still very slow then there is probably very little you can do in SSIS to tweak the performance.

    2) Make sure fastload is on in the destination

    3) try setting network package size:


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, January 08, 2013 6:54 AM
  • Without more information, all I can do is point you to this excellent article:

    Top 10 SQL Server Integration Services Best Practices


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, January 08, 2013 7:44 AM
  • There's alot of possible reasons it's slow Farhad.  My first question would be are the source and destination on seperate servers?  Could just be network performance. 

    Try using task manager/performance tab/resource monitor during a load and see if memory or disk I/O is bottlenecked during the load.

    Another question: is SSIS server on the same physical machine as the DB server?  If so, then they could be competing for resources and disk spindles.  It could be a simple "your hardware resources are insufficent".  SSIS will cache in memory as much as it can and then will start spooling and writing temp files while it reads rows.  This can quickly consume more than a few gigs of memory if your rows are large and contain a large number of rows.

     

    Thank you GSS138

    First question: no the source and destination DBs are in the same physical server

    Second question: yes SSIS is located in another physical machine

    Yes SSIS gets all the RAM, how can I make SSIS RAM usage more efficient, I think caching could be useful if cached data would be used immediately, but caching source data has no scene of optimizing. How can I manage it?

    Regards


    Tuesday, January 08, 2013 11:06 AM
  • 1) For testing the speed of the source you could temporary replace the destination with for example a row count transformation. If this is still very slow then there is probably very little you can do in SSIS to tweak the performance.

    2) Make sure fastload is on in the destination

    3) try setting network package size:


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Thank you I set the Packet Size to 16k, my destination tables are indexed. I think there would be solution to handling data importation to an indexed table. Without indexes the package would be executed very fast. Totally I think I have problems in three area:

    1- unnecessary caching, because cached data would not be used

    2- Destination indexes

    3- The method that I am using to ETL data, there ma be a better method instead of using Data Flow task, something like Bulk insert...

    regards

    Tuesday, January 08, 2013 11:32 AM
  • 1. Why do you think there's unnecessary caching? You're just using a simple source to destination, caching isn't necessary.

    2. If the destination indexes are problematic, you can drop them before the load and add them back later. Another option - if you have Enterprise edition - is to insert in an empty partition and then partition switch this partition into your final destination table.

    3. If you use the Fast Load option in the OLE DB destination you are using bulk inserts.

    Also check the recovery model of your destination database. Try setting it to simple to minimize logging.

    If SSIS is on another server, you're transferring all the data from the server A (as a source) to server B (the SSIS server) and then back to server A (as a destination). This can have quite a network impact.

    Maybe it's just easier to use TSQL to transfer the data.


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 7:10 AM
    Tuesday, January 08, 2013 11:46 AM
  • I like Koen's suggestions above.  Drop your indexes then recreate them at end of SSIS package.  Simple logging is a must imo as well-there's no reason to write a transaction entry for ETL loads.

    And yeah if the source and destination are on the same server, you probably are much better off just using a stored procedure to move from one DB/table to another.  No need to send all of that data across the network twice.  If you want to use SSIS to schedule it that's fine.  Don't use a dataflow, just use SSIS to execute a procedure on the source system.

    Tuesday, January 08, 2013 4:08 PM
  • Hi,

    How about providing more information

    1--How many records you load every day and on month basis? How much time each Data Flow task take?

    2--What are the transformations are being used in each Data Flow. The subject itself say from OLEDB Source to OLE DB destination but still not sure if any other transformation is used. Are you extracting data from one table or multiple tables by using Join?

    3--Is it slow from the very first day or it started behaving slow?

    4--When you say, it has enough memory, What exactly size it has (RAM Size)?

    5--This is the only package that is giving you hard time or other packages also runs with same speed?

    Thank you


    http://sqlage.blogspot.com/


    Tuesday, January 08, 2013 4:22 PM