Calling Oracle SP with TableAdapter very slow

Question

I have a query that runs super fast when executed in the sql editor (oracle): 1ms.

The same query (as stored procedure) when executed by a DataSet-TableAdapter takes 2 seconds. I'm just retrieving 20rows.

Since I'm using a TableAdapter, the return values are stored in a ref cursor.

If I was fetching 2'000 rows I could understand that some time is needed to build the DataSet, but 2 seconds for only 20 rows seems too much for me.

There is a better way to execute SP on oracle or this is the only way? What could I try to do to improve the performances?

Thanks for your help!

Searching in google, it seems that the problem is with the refcursor. Others people faced the same performance issue, but no solution is provided.

score 2 · Answer 1 · answered Dec 10 '08 at 11:28

Which data provider do you use?

Do you reference System.Data.OracleClient or do you use odp.net (Oracle's data provider for connecting .NET apps with Oracle) or do you use devart's (formerly known as corelab) provider.

I have good experiences with odp.net in combination with Oracle 9. You can download odp.net for free on the oracle site. See: http://www.oracle.com/technology/tech/windows/odpnet/index.html

You can use the latest version (11.1.0.6.20) to connect to an Oracle 9 database.

score 0 · Answer 2 · answered Dec 09 '08 at 15:43

0

How long does it take when you use a datareader instead of a TableAdaptor? I would try the datareader. I have never encountered problems with the datareader.

answered Dec 09 '08 at 15:43

tuinstoel

7,248
27
27

score 0 · Answer 3 · answered Dec 10 '08 at 07:42

0

Make sure you're setting the CommandType to CommandType.StoredProcedure.

For example (from MSDN):

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;

answered Dec 10 '08 at 07:42

BQ.

9,393
3
25
35

score 0 · Accepted Answer · edited May 23 '17 at 12:30

Ok. I found what's the problem.

At the beginning I thought it was a problem of DataProvided, but it wasnt. I discovered the same issue in SQLServer 2000....

Searching in google I found out something about the execution plan. Taking this way, I boosted the query performance of 50%.

Brief resume of the problem is that when executing a SP by code, the DBMS has some trouble with the execution plan, and doesn't use the indexes...

A better answer is in this post: Parameter Sniffing (or Spoofing) in SQL Server

I hope this will help you.

Calling Oracle SP with TableAdapter very slow

4 Answers4