Performance of Microsoft OLE DB Provider for DB2

  • ahneave

    Say Hey Kid

    Points: 694

    (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

  • Quinn-793124

    SSCrazy

    Points: 2785

    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

    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

    Say Hey Kid

    Points: 694

    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

    Say Hey Kid

    Points: 694

    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

    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

    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

  • ahneave

    Say Hey Kid

    Points: 694

    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

    Alister,

    I am having trouble locating where that file is located.

    I will let you kow when I find it.

    Quinn

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

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