Discussion:
linked server vs ssis - speed difference - why?
(too old to reply)
Piotr Lipski
2007-12-03 09:44:46 UTC
Permalink
Hi.

There is a SQL 2005 Server Std with linked server to iSeries. There is a
task that imports a huge (~30mln recs) table from iSeries each weekend.
I can do the import in two different ways:

1.
INSERT INTO [local].[sqlserver].[table]
SELECT ... FROM [linked].[server].[table].[name]

or

2.
Define SSIS package that does more or less the same thing - standard
data transfer between two sources, with column mappings etc. etc.

The amount of data is the same each week (+- 1%)
Method 1 takes ~4h20m to execute. Method 2 takes ~2h50m.

Both use the same odbc driver for iSeries.

I prefer using the first method because it's easier to implement and
causes no problems when moving the import job between servers but method
2 seems to be much faster. So I wonder what causes the difference and
how to speed up method 1 (if possible at all).
--
PL
j***@gmail.com
2007-12-03 14:32:14 UTC
Permalink
Do you have the description of you SSIS Package ?
If you use bloc insertion, it's much faster than insert into a linked
server !
You can use the .dtsConfig to configure the connection string of you
different servers
Piotr Lipski
2007-12-03 14:57:44 UTC
Permalink
Post by j***@gmail.com
Do you have the description of you SSIS Package ?
Description? What do you mean? I have full administrative access to the
package so...
Post by j***@gmail.com
If you use bloc insertion, it's much faster than insert into a linked
server !
I'm inserting FROM linked server, not into. What's bloc insertion? I use
ODBC provider for .NET for source data (iSeries) and OLEDB for
destination (mssql). The 'AccessMode' property of destination connection
is set to 'OpenRowset Using FastLoad' (default).
Post by j***@gmail.com
You can use the .dtsConfig to configure the connection string of you
different servers
Could you be more specific?

Thank you for response.
--
PL
Jay
2007-12-03 20:41:56 UTC
Permalink
I think (but am not sure) he means to open the package, click on View->Code
to get the XML that defines the SSIS package.

Looking at it for a package I have, I don't see how this could help, so it
makes me doubt myself. Still, it's all I see.
Post by Piotr Lipski
Post by j***@gmail.com
Do you have the description of you SSIS Package ?
Description? What do you mean? I have full administrative access to the
package so...
Post by j***@gmail.com
If you use bloc insertion, it's much faster than insert into a linked
server !
I'm inserting FROM linked server, not into. What's bloc insertion? I use
ODBC provider for .NET for source data (iSeries) and OLEDB for destination
(mssql). The 'AccessMode' property of destination connection is set to
'OpenRowset Using FastLoad' (default).
Post by j***@gmail.com
You can use the .dtsConfig to configure the connection string of you
different servers
Could you be more specific?
Thank you for response.
--
PL
Russell Fields
2007-12-03 21:12:02 UTC
Permalink
Piotr,

I suspect that the SSIS package is probably using some flavor of the BULK
methods, perhaps OPENROWSET Bulk RowSet methods, which do run much faster
than an INSERT statement. (That is probably what jerome was referring to.)

In the SQL Server Books Online, you can read about bulk inserting
performance at:
http://technet.microsoft.com/en-us/library/ms190421.aspx

Of course, that is written as if you were coding it yourself, but it is a
peek into the tools that SSIS can use.

RLF
Post by Piotr Lipski
Post by j***@gmail.com
Do you have the description of you SSIS Package ?
Description? What do you mean? I have full administrative access to the
package so...
Post by j***@gmail.com
If you use bloc insertion, it's much faster than insert into a linked
server !
I'm inserting FROM linked server, not into. What's bloc insertion? I use
ODBC provider for .NET for source data (iSeries) and OLEDB for destination
(mssql). The 'AccessMode' property of destination connection is set to
'OpenRowset Using FastLoad' (default).
Post by j***@gmail.com
You can use the .dtsConfig to configure the connection string of you
different servers
Could you be more specific?
Thank you for response.
--
PL
Jerome
2007-12-04 08:45:11 UTC
Permalink
Yes that's what i mean (like Russel), bloc insertion is Bulk insert
(sorry for my poor english).
The SSIS package is certainly using BULK INSERT which is much faster
than the simple insert

Loading...