Post reply

Importing SQL Server Data Using SSIS - Which Option is Fastest?

  • July 25, 2012 at 7:30 am

    #143659

    I was trying to determine which option for importing SQL Server into SQL Server was fastest.

    I would have assumed that a Stored Procedure would be faster nut then I cane across the following article:

    http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

    Has anyone had a similar outcome?

    Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 26, 2012 at 4:35 pm

    #1518221

    Welsh Corgi (7/25/2012)

    I was trying to determine which option for importing SQL Server into SQL Server was fastest.

    I would have assumed that a Stored Procedure would be faster nut then I cane across the following article:

    http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

    Has anyone had a similar outcome?

    Thanks.

    I actually have a reminder to go comment on this article. I read the same article and the title is quite misleading. "Importing" to me means "from an external source", yet the test actually takes data frmo a database and re-inserts it into the same database (or one on the same instance). Who in their right mind would bother bringing the data out into the SSIS memory space to do this work unless there was some value added, e.g. by running the data through a third-party dll method or soemthing not easily done in T-SQL? The article's premise is an odd one IMHO.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 26, 2012 at 4:49 pm

    #1518224

    opc.three (7/26/2012)

    Welsh Corgi (7/25/2012)

    I was trying to determine which option for importing SQL Server into SQL Server was fastest.

    I would have assumed that a Stored Procedure would be faster nut then I cane across the following article:

    http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

    Has anyone had a similar outcome?

    Thanks.

    I actually have a reminder to go comment on this article. I read the same article and the title is quite misleading. "Importing" to me means "from an external source", yet the test actually takes data frmo a database and re-inserts it into the same database (or one on the same instance). Who in their right mind would bother bringing the data out into the SSIS memory space to do this work unless there was some value added, e.g. by running the data through a third-party dll method or soemthing not easily done in T-SQL? The article's premise is an odd one IMHO.

    Agreed - on the same server it really isn't worth it unless there's something very strange needing to be done. That said - I've often had a lot of nice performance when cross-server data movement is happening

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • July 26, 2012 at 6:29 pm

    #1518229

    I also neglected to mention one aside to the article with whihc I took issue. The author rated ODBC quite poorly and likely due to the fact that he did not set the ODBC Destination to bulk load the data which should have bumped it into the area where the OLE DB Destination performed.

    Regarding Welsh's initial question, I agree that procs have the reputation of performing better than ad hoc SQL and in general they do. I'll add that procs should be used wherever possible but there are times when ad hoc SQL will do better for reasons associated with the intrinsic properties of procs, e.g. excessive recompilations, multi-statement procs compiling the first time they run, parameter sniffing, etc.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 26, 2012 at 6:33 pm

    #1518230

    Matt Miller (#4) (7/26/2012)

    opc.three (7/26/2012)

    Welsh Corgi (7/25/2012)

    I was trying to determine which option for importing SQL Server into SQL Server was fastest.

    I would have assumed that a Stored Procedure would be faster nut then I cane across the following article:

    http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

    Has anyone had a similar outcome?

    Thanks.

    I actually have a reminder to go comment on this article. I read the same article and the title is quite misleading. "Importing" to me means "from an external source", yet the test actually takes data frmo a database and re-inserts it into the same database (or one on the same instance). Who in their right mind would bother bringing the data out into the SSIS memory space to do this work unless there was some value added, e.g. by running the data through a third-party dll method or soemthing not easily done in T-SQL? The article's premise is an odd one IMHO.

    Agreed - on the same server it really isn't worth it unless there's something very strange needing to be done. That said - I've often had a lot of nice performance when cross-server data movement is happening

    Agreed. For cross-server transfers SSIS is quite flexible, especially for multi-casting, and performs admirably with no disk I/O in between.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 26, 2012 at 8:02 pm

    #1518245

    Thanks for all of the ideas.

    I got audited today but an outside firm and I got dinged for having the Staging and Data Warehouse on the same server. :blush:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 26, 2012 at 9:24 pm

    #1518262

    Welsh Corgi (7/26/2012)

    Thanks for all of the ideas.

    I got audited today but an outside firm and I got dinged for having the Staging and Data Warehouse on the same server. :blush:

    Thanks for posting. When you get the final audit report I would be curious to know their justification for such a ding if you are at liberty to share.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 26, 2012 at 9:31 pm

    #1518263

    opc.three (7/26/2012)

    Welsh Corgi (7/26/2012)

    Thanks for all of the ideas.

    I got audited today but an outside firm and I got dinged for having the Staging and Data Warehouse on the same server. :blush:

    Thanks for posting. When you get the final audit report I would be curious to know their justification for such a ding if you are at liberty to share.

    I will share everything possible when I get the report. Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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