Performance of Microsoft OLE DB Provider for DB2

Question

Performance of Microsoft OLE DB Provider for DB2

ahneave

Right there with Babe

Points: 754
More actions
April 21, 2009 at 10:34 pm

#155591

(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

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

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

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 1

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

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 2

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.

ahneave Right there with Babe Points: 754 More actions · Answer 3

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

ahneave Right there with Babe Points: 754 More actions · Answer 4

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

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 5

How do you figure out the byte size of a row? By adding up the bytes of each column?

Thanks

Quinn

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 6

Alister,

Here is the byte count for the 2 tables.

ATReal19672366112 KB

ATSales438612392 KB

Table name, Rows, Columns, Byte count

I figured out how to get byte count.

Thanks

Quinn

ahneave Right there with Babe Points: 754 More actions · Answer 7

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

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 8

Alister,

I am having trouble locating where that file is located.

I will let you kow when I find it.

Quinn