RE: .Net version 1.1 data fetch rate

Robyn,

Is your developer using DataAdapters or are they processing the data using the DataReader? Also, is your Parse to Execution Ratio very high? These are common issues that I have seen using the Microsoft data providers. I hope that your developer is at least using the System.Data.OracleClient.

I have done several time trials, not scientific at all, but for basic testing. I am pretty convinced that the best method for retrieving data from the database is using Stored Procedures with SYS_REFCURSOR and loading the data into a System.DataTable using the OracleDataAdapter.

FYI: You will not get that much better performance using .Net 2.0 when it comes to the Oracle Client data provider.

As always you should do a little time trial and test your chosen method before using it in your application.

Thanks,
Bryan

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robyn
Sent: Thursday, April 05, 2007 10:27 AM
To: oracle-l
Subject: .Net version 1.1 data fetch rate

good morning all,

We have a .Net application that is experiencing poor performance over the last 2 weeks. The app schema resides within our SAP database, which was all recently upgraded from 9.2.0.6 to 9.2.0.8.

I've been working with the lead developer to improve a search screen function, and using the data in the 10046 trace files, I convinced her to rewrite the query against the tables instead of the existing views, which removed several unnecessary joins. The query alone now completes in .8 seconds instead of 13.+ seconds.

As you may have guessed, the database isn't the problem. (but we always have to prove that point first). The issue is with the data fetch rate; the query returns anywhere from 10 to 700 rows and many many columns, which results in about 40 rows per fetch and a time out on larger queries. I have Mark Williams presentation from Hotsos this year and he addressed the issue; unfortunately, this app was implemented with Microsoft's tools, not ODP.net, and is still on a version 1.1.

Does anyone have any suggestions for how to increase the data fetch rate in version 1.1? An upgrade is planned but not soon enough for this issue.

tia ... Robyn
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 05 2007 - 10:29:02 CDT