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!

Answer 1

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.

Answer 2

No. Even using a DataReader it takes too many seconds, eveny for a simple Count that's fast in the query editor.

Answer 3

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;

Answer 4

I was already set to StoredProcedure...

Answer 5

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

Answer 6

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.

Answer 7

Maybe you get the point. I installed ODP.NET (the latest version), but in my connection string I'm referencing System.Data.OracleClient.

Now I try to modify this thing...

May I contact you by email (davide.vosti gmail.com)? If we find a solution we could then paste it here...

Answer 8

There is a special odp.net forum for questions: http://forums.oracle.com/forums/forum.jspa?forumID=146

Answer 9

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: http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server

I hope this will help you.