none
performance comparision between IS and direct load using SQL Query

    Question

  • Hi Team,

    I have a question on the performance of the SSIS compare to normal load..

    its a direct load but when i am doing using an SSIS Package its taking lot of time to load compare to direct load using sql.

    the total records in a table is 4M when i load using a sql query to another table it took some 5 mins but when i try doing using ssis it took 5 mins to load 20k records....

    can somebody tel me why is this? I am very new to IS!!

    Thanks,

    Eshwar

     

    Saturday, September 25, 2010 1:53 PM

Answers

  • 5 mins to load 20K records? There may be many reasons to have such a performance.

    Key points of fast loading is Bulk insert and minimal logging.

    Ensure that you use Fast Load option on OLE DB Destination when using SSIS. Where are you executing the SSIS package? There might also be a network issue, since the records are moved to SSIS engine first and then loaded into destination. However, when using TSQL, if the source and the destination tables are on the same instance, you will not have a network issue.

    I would strongly suggest you to go over the following great paper

    http://msdn.microsoft.com/en-us/library/dd425070.aspx

    Sedat

    Saturday, September 25, 2010 2:27 PM

All replies

  • 5 mins to load 20K records? There may be many reasons to have such a performance.

    Key points of fast loading is Bulk insert and minimal logging.

    Ensure that you use Fast Load option on OLE DB Destination when using SSIS. Where are you executing the SSIS package? There might also be a network issue, since the records are moved to SSIS engine first and then loaded into destination. However, when using TSQL, if the source and the destination tables are on the same instance, you will not have a network issue.

    I would strongly suggest you to go over the following great paper

    http://msdn.microsoft.com/en-us/library/dd425070.aspx

    Sedat

    Saturday, September 25, 2010 2:27 PM
  • It sounds like the source and the target tables are on the same server.  Almost always you will get better performance by using a standard query over using SSIS running on the same computer as the database server.

    Let's say that you are running SSIS on a different machine from the database server.  You will get even worse performance using SSIS than using a standard query.  Think about it.  SSIS is going to get every row from the database server.  This is going to involve the network.  Then SSIS is going to send the data back to the database server, another network hit.  If you run a standard query, SQL Server is going to do everything it can in memory.  No network delays.


    Russel Loski, MCT
    Saturday, September 25, 2010 4:48 PM
  • Does your ssis package has any other transformations inside?

    there are resource consumer transformations such as MultiCast or UNION ALL. did you used them?

     

    Of course a direct sql query has better performance than using ssis, because transformation all will be done in database server.

    but when you use SSIS package, all data will load into to SSIS server first, do transformations there, and at last data saved on destination.

     


    http://www.rad.pasfu.com
    Sunday, September 26, 2010 4:58 AM
    Moderator
  • Eshwar

    If you are using SQL Server 2008 and onwards see the below artcle

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   ---Minimal Logging changes in SQL Server 2008


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 26, 2010 1:33 PM
  • Why don't you try to isolate the problem. Delete step by step a component from the package and put a wordtcount at the end. 
    Sunday, September 26, 2010 4:46 PM