none
System.Data.OracleClient.OracleDataAdapter.Fill() method is very slow RRS feed

  • Question

  •  

    Hi,

    Please find the issue with details below -

    System Details
    Visual Studio 2003

    .NET Framework 1.1

    Oracle Client 9i

    System.Data.OracleClient.dll  V 1.0.5000.0

     

    Issue - System.Data.OracleClient.OracleDataAdapter.Fill(Dataset) takes very long time to execute.

    Description
    A Dataset needs to be filled with multiple (15) datatables by executing a a stored procedure with 15 refcursors as output parameter. Stored Proc when individually executed returns results within 1 to 6 seconds based on varying input parameters (it has 7 input parameters). But, System.Data.OracleClient.OracleDataAdapter.Fill(Dataset) takes around 3 to 4 minutes to execute the same stored procedure!

    Code steps

    //Create Connection object.

    //Create & Prepare Command object. Command Type = Stored Proc

    //Create and Add Input and Output DbParameters to Command object

    //Create OracleDataAdapter object. OracleDataAdapter adap = new OracleDataAdapter(command object)

    //Fill Dataset using OracleDataAdapter.Fill (dataset) /*Takes long time to execute */

    Approches tried

    1) Use of cmd.ExecuteReader() to return a OracleDataReader instead of using System.Data.OracleClient.OracleDataAdapter.Fill(Dataset). The results come within 1-6 seconds. But as we need dataset for implementing the business logic, converting datareader into a dataset is again a performance issue (Looping though Datareader is a big pain!!)

    2) Use of ODP.NET providers instead of Microsoft's Oracle Data Provider. 

    Tried below versions of Oracle.DataAccess
    Oracle.DataAccess.dll Version - 9.2.0.700
    Oracle.DataAccess.dll Version - 10.2.0.100

     

    Still the performance shows no improvement in OracleDataAdapter.Fill(Dataset) method.


    References

     http://forums.oracle.com/forums/thread.jspa?messageID=1046276
    http://www.developmentnow.com/g/7_2009_4_0_0_663386/Oracle-Ref-Cursor-Issue-in-ADO-NET.htm
    http://forums.oracle.com/forums/thread.jspa?threadID=322624
    http://objectmix.com/csharp/118614-oracledataadapter-fill-slow.html
    http://weblogs.asp.net/rosherove/archive/2004/01/22/61541.aspx

    Please let me know if you need further details.

    Any suggestions/inputs for issue resolution are welcome.

    Thanks in Advance,
    Sonali.
     

     

     

     

     
    • Edited by SBohora Wednesday, May 27, 2009 11:18 AM
    Wednesday, May 27, 2009 11:14 AM

All replies

  • Is there a reason why you need to bring all the data into the application from 15 tables? I'm not surprised that this take several minutes. With a DataReader you're fetching a row at a time. When using a DataSet you're grabbing all the data at once. Depending upon how many rows are in the tables this could take quite a while, not to mention potentially using up quite bit of memory on the client.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, May 27, 2009 1:13 PM
  • Hi Paul,

    Thanks for the reply!

    15 cursors do not get data from 15 different tables as such. Each cursor gets data by applying joins and filter conditions on few required tables.
    We need data from the 15 cursors for generating one of the reports in the application.

    Even when none of the cursors are returning records > 500 rows with the record count in each cursor variying from 0 to 500 for different input parameters the time taken to fill the dataset using OracleDataAdapter is very high!

    As an alternative I tried to execute each query seperately puting the query as a string in code, using commandType=Text instead of storedprocedure. For the queries retreiving records>200 the OracleDataAdapter.Fill(dataset,"tablename") method took a lot of time! eg. For 272 record count it took nearly 1.5 minutes to execute.

    Any Suggestions/Inputs?

    Thanks,
    Sonali.

    Wednesday, May 27, 2009 4:18 PM
  • Since you seem to be using a number of tables would it be possible for you to create a View of the data in Oracle and then query that from your application, instead of all the individual tables?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, May 27, 2009 5:53 PM
  • Paul, I am having the same issue filling a dataset. Using VS2010, I'm calling a view, which calls a view, which calls a view, which calls data. It takes 15 seconds to pull 49 records. But if I query a table, I can get 109 records in less than a second. I have no control over the views. Ideas?
    Wednesday, August 11, 2010 11:51 AM
  • Is it any faster using a DataReader or do you have to use a DataSet/DataTable?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, August 11, 2010 12:55 PM
  • It is no faster at all. Same timeframe. Running the query in TOAD, takes 8 seconds for 43 records. In the VS 2010 application, using da.fill(ds) or (dt), takes :42 seconds. That's crazy.
    Wednesday, August 11, 2010 12:57 PM
  • Are you using Microsoft's System.Data.OracleClient or Oracle's ODP.NET library (or a third-party library)? Just an FYI, System.Data.OracleClient is being phased out after .NET Framework 4.0 so you may want to consider switching if possible.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, August 11, 2010 1:28 PM
  • I have tried both and they are identical. I am now going to uninstall the 10.2.0.100 OracleDataAccess.dll and install the 11.x.x.x version and try that. Oh yea on the 'phase out' - that's why I made the change. :)
    Wednesday, August 11, 2010 2:23 PM
  • My problem was a view, which I had no control over. That's good. Note: have switched to the OracleDataAccess.dll.

    Thanks you all.

    Edward

    Wednesday, August 18, 2010 11:22 AM