none
MSDAORA vs. OraOLEDB.Oracle large performance difference RRS feed

  • General discussion

  • Hi,   I was in a situation where I could do an apples to apples comparison of the c# Microsoft driver for Oracle DB and the c# Oracle driver for Oracle db.   I guess MSDAORA is deprecated, but that's a shame because at least in the 32 bit realm it has smoking performance?  Anyway by just toggling the driver in my config file I was seeing that Provider=MSDAORA was 5 times faster that Provider=OraOLEDB.Oracle in a c# Console project binary.  The machine it's running on and the Oracle database it's going against are constants.    Anyone else experiencing that the Oracle driver is quantumly slower than the Microsoft driver?

    By the way this may be a case of the developer wanting to use the most complex tools but you may want to consider doing bottleneck, high volume ETL jobs with c#.   With MSDAORA we are pumping out ~ 18 million rows to a 1 gig ascii file in 6 minutes from a complex Peoplesoft Oracle database.   I have run the same SSIS job and it takes an hour to do the same work.  I don't have Datastage in our environment but I'd like to try that.

    • Changed type Fred BaoModerator Monday, November 25, 2013 1:17 AM It should be a discussion
    Friday, November 22, 2013 1:35 AM

All replies

  • Hi,   I was in a situation where I could do an apples to apples comparison of the c# Microsoft driver for Oracle DB and the c# Oracle driver for Oracle db.   I had heard that MSDAORA is deprecated, is that true?  Anyway by just toggling the driver in my config file I was seeing that Provider=MSDAORA was 5 times faster that Provider=OraOLEDB.Oracle in a c# Console project binary.  The machine it's running on and the Oracle database it's going against are constants.    Anyone else experiencing that the Oracle driver is quantumly slower than the Microsoft driver?

    By the way this may be a case of the developer wanting to use the most complex tools but you may want to consider doing bottleneck, high volume ETL jobs with c#.   With MSDAORA we are pumping out ~ 18 million rows to a 1 gig ascii file in 6 minutes from a complex Peoplesoft Oracle database.   I have run the same SSIS job and it takes an hour to do the same work.  I don't have Datastage in our environment but I'd like to try that.

    • Merged by Caillen Friday, November 22, 2013 9:36 AM duplicate
    Thursday, November 21, 2013 6:55 PM
  • One other clarification I should make is that both the drivers I'm using here are 32 bit.  The production server we have here is 32 bit although I have other machines available to me that are 64 bit.

    • Edited by etlman Thursday, November 21, 2013 10:41 PM
    Thursday, November 21, 2013 10:41 PM
  • I suspect that the Managed Data Providers forum would get you better quality answers to this sort of question

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    As far as deprecated goes, this page is quite explicit http://msdn.microsoft.com/en-us/library/windows/desktop/ms675851(v=vs.85).aspx


    Paul Linton

    Friday, November 22, 2013 12:36 AM
  • Thanks, looks like MSDAORA is deprecated and that's a shame because at least here, it's a lot faster.  I moved this question over to the dotnet Providers forum.
    Friday, November 22, 2013 1:37 AM
  • Hello,

    Have you tried to do other operations like insert and update to see whether it will has the same result?

    It sometimes performs badly for SELECT queries that returned some result.

    For this, please have a look at this article below:

    http://odenysenko.wordpress.com/2011/10/28/slow-oracle-net-from-windows-7vista/

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 22, 2013 8:41 AM
    Moderator
  • Fred,

         I have thought long and hard about doing inserts/update from a c# program.   In SSIS and Datastage it's common to read one database and write another.    But with c# I can't get my head around what sql syntax and binding you would do to make high volume database inserts from c#.  An insert  into tablea values (1,'a',3) just wouldn't cut it.  c# writes ascii files well and ms sql bulk insert can read at least a million rows a minute into a table so for high volume etl a c# write from one source and bulk insert into the ms sql target seems like the ticket.

         Back to my original problem (slow OraOLEDB.Oracle).   The news is getting worse.  Right now I'm running a 64 bit compile using the 64 bit OraOLEDB.Oracle driver (I assume) and I'm still 5 times slower than MSDAORA on the same machine against the same Oracle database.  So here's one developer asking Microsoft to dust off MSDAORA and make a 64 bit version of it.  It's hard to believe Microsoft can read and process Oracle database faster than Oracle but I'm willing to present my findings to anyone who wants to look at them.  Here is the environment I am experiencing the slow OraOLEDB.Oracle performance:

    execution Server: Windows Server 2008 - 64bit

    compiler: Visual Studio c# 2010 - 32 bit and 64 bit compiles

    Provider=OraOLEDB.Oracle

    Oracle database version: Oracle 11G release 11.2



    • Edited by etlman Friday, November 22, 2013 8:28 PM
    Friday, November 22, 2013 8:25 PM
  • We use Microsoft's OLEDB Provider for Oracle in legacy apps and the ODBC driver for linked tables in Microsoft Access. Yes, these drivers have been deprecated and do not support newer features such as the BLOB and CLOB Oracle types. In order to support BLOB and CLOB we use Oracle's OLEDB Provider. I haven't noticed any significant issues with respect to performance in these environments.

    With respect to .NET we are currently using Microsoft's native .NET provider for Oracle. This library has been deprecated as well so I would recommend using Oracle's ODP.NET library. Going forward I don't believe Microsoft will be doing any development with respect to Oracle libraries and I don't really see any reason to use OLEDB drivers/providers for Oracle under .NET.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, November 25, 2013 4:08 PM