|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:24 PM
Points: 32,
Visits: 176
|
|
(This has also been posted on MSDN Forums so apologies for the replication)
I am trying to get the best performance driver for transfer of data from AS400 to SQL Server 2005 using SSIS. I am comparing it to DTS packages using Client Access ODBC Driver (32-bit) which I'll call std ODBC
.Net Provider/ODBC works but is VERY slow. I understand that Microsoft has simply put the .Net layer on top of the std ODBC driver and this overhead is slowing it right down (to a crawl in my opinion)
.Net Oledb/ IBM DB2 for iSeries IBMDA400 works and is about 2.5 times faster than .Net/ODBC, but still about 3x slower than std ODBC
.Net Oledb/ IBM DB2 for iSeries IBMDASQL is similar.
Microsoft OLE DB Provider for DB2 is supposed to be the fastest but it requires upgrading to Enterprise Edition, which is not a trivial cost.
Can anyone give me feedback on the performance I can expect with Microsoft OLE DB Provider for DB2 compared to std ODBC.
Thanks
Alister
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, September 02, 2010 12:20 PM
Points: 97,
Visits: 370
|
|
Alister,
I am using the ibmdasql provider to extract data from 2 tables on the AS400. I can bring across 24,000 rows in about 30 seconds. That includes deleting the rows on the SQL side first. This is the only SSIS package I have so I can't compare other providers. Seams fast enough to me.
Quinn
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Monday, August 23, 2010 2:45 PM
Points: 364,
Visits: 295
|
|
Can you ask the AS400 admin to transfer data into a file format, ship it in a DVD and then u use SSIS from there.
I used the ODBC provider for moving around 1000 tables from AS400 to SQL Server (but Enterprise)...so no probs.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:24 PM
Points: 32,
Visits: 176
|
|
Thanks Quin...
It depends on how big your rows are. For instance, if your row size is about 1/4 of mine then your transfer rate is about the same.
My row size is 145 bytes.
Again, my standard for comparison is the 'old' DTS package with ODBC. I would like at least the same performance, but in reality expect a better performance due to the more advanced technologies of SSIS.
Cheers
Alister
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:24 PM
Points: 32,
Visits: 176
|
|
I'm pretty sure this would work. My tests with csv files of the same size go like a rocket. However, there are logistical and political reasons why I can't ask for the data to be dumped to csv first.
Thanks for the input though.
Cheers
Alister
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, September 02, 2010 12:20 PM
Points: 97,
Visits: 370
|
|
How do you figure out the byte size of a row? By adding up the bytes of each column?
Thanks
Quinn
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, September 02, 2010 12:20 PM
Points: 97,
Visits: 370
|
|
Alister,
Here is the byte count for the 2 tables.
ATReal 19672 36 6112 KB
ATSales 4386 12 392 KB
Table name, Rows, Columns, Byte count
I figured out how to get byte count.
Thanks
Quinn
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:24 PM
Points: 32,
Visits: 176
|
|
In that case Quinn, you're getting very good transfer rates.
Every record won't have the full number of bytes of course (varchar fields only use the bytes as needed, plus 2, for instance), and your network won't be the same as mine, but even so your figures are way out of my league.
Can you give me the more details of the IBMDASQL driver you're using? Maybe I'm using an older version or something.
Cheers
Alister
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, September 02, 2010 12:20 PM
Points: 97,
Visits: 370
|
|
Alister,
I am having trouble locating where that file is located.
I will let you kow when I find it.
Quinn
|
|
|
|
