server vs ssis - speed difference - why?

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).
--
PLDo 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|||jerome.deville@.gmail.com wrote:
> Do you have the description of you SSIS Package ?
Description? What do you mean? I have full administrative access to the
package so...
> 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).
> You can use the .dtsConfig to configure the connection string of you
> different servers
Could you be more specific?
Thank you for response.
--
PL|||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.
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fj15hc$s79$1@.news.onet.pl...
> jerome.deville@.gmail.com wrote:
>> Do you have the description of you SSIS Package ?
> Description? What do you mean? I have full administrative access to the
> package so...
>> 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).
>> You can use the .dtsConfig to configure the connection string of you
>> different servers
> Could you be more specific?
> Thank you for response.
> --
> PL|||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
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fj15hc$s79$1@.news.onet.pl...
> jerome.deville@.gmail.com wrote:
>> Do you have the description of you SSIS Package ?
> Description? What do you mean? I have full administrative access to the
> package so...
>> 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).
>> You can use the .dtsConfig to configure the connection string of you
>> different servers
> Could you be more specific?
> Thank you for response.
> --
> PL|||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