CODEWITHSUNDEEP
.netoracle-databasestrongly-typed-datasettableadapter
Davide Vosti
Davide Vosti

Reputation: 2485

Calling Oracle SP with TableAdapter very slow

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.

Upvotes: 3

Views: 1731

Answers (4)

Davide Vosti
Davide Vosti

Reputation: 2485

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.

Upvotes: 0

tuinstoel
tuinstoel

Reputation: 7306

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.

Upvotes: 2

BQ.
BQ.

Reputation: 9413

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;

Upvotes: 0

tuinstoel
tuinstoel

Reputation: 7306

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.

Upvotes: 0

Related Questions