Eric M Russell (2/9/2015)
Jeff Moden (2/9/2015)
Eric M Russell (2/9/2015)
axc_67 (2/9/2015)
No I am not joining any tables in the remote server.I am just pulling a simple data extraction from remote server with where conditions....which is why I don't understand why its taking so much time.
Also try doing this using an SSIS package.
Not being bullish here. I don't use SSIS but continue to try to gather information so that when I do finally get to it, I'll have some things that I'd like to check and compare.
With that thought in mind, why have you recommended doing this using an SSIS package? Will it be faster, easier to build, easier to maintain, or is it just because that some people have adopted the mantra that ETL should generally be done using SSIS or ???
I'll admit that I don't know all the internals regarding T-SQL INSERT versus BULK INSERT or a SSIS DataFlow task. However, when I'm doing something like this, I makeup for my lack for knowledge by experimenting with a variety of different approaches before I settle on a final solution. For example, the thing about EXEC() AT extracting twice as fast as OpenQuery(), even when using the same SQL select, is probably something nobody would have expected.
I wouldn't reccomend SSIS for such a simple data transfer, if this involved only local tables. But this involves loading data from a remote source (IBM AS400). SSIS is optimized for loading data between heterogeneous sources, and there are a lot of knobs to tweak. SSIS supports some "fast load" options for OLEDB destinations, which is equivalent to a bulk insert. There are also options to set properties like network packet transfer size, SSIS buffer size, and the batch commit size. If nothing else, it's just an alternative approach to sucking data through a linked server connection.
http://www.sqllion.com/2009/04/faster-extraction-loading-by-ssis/
I can testify that SSIS is easily faster than a linked server. In SSIS you can also do all your transformations in memory and not have to create a temp table. The MERGE join can do the equivalent of a inner or any join type all in the transfer pipeline . It also supports transactions and logging. If something goes bad you have messages helping to pinpoint the issue(s). You can annotate all the tasks within the control flow so it provides a handy ability flor documenting the work as well.
----------------------------------------------------