• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1698
  • Last Modified:

Oracle Query Slow with C#

Dear experts,

I wrote a C# program connecting to a huge Oracle table (500 GB or more) and making a select query. The Oracle table is well designed and uses indexes. The problem is that the query returns the result set very slow. It takes approximately 7-8 min. However, when I run the same query in Toad, it takes 20 seconds. (Auto-trace is set, all the results are being fetched) I tried ODP.Net, again it did not make sense.

How can I keep up with this performance issue? Any recommendation welcomes. Even you can offer me a solution through different programming languages.

3 Solutions
Are you certain that the query being executed in Toad is actually identical?  Are you using bind variables in the C# application, for example, while you are using literals in Toad?  Are you certain that there are no formatting differences between the queries?  If you are generating the same SQL_ID from both Toad and your application, are you seeing multiple query plans for that SQL_ID in the shared pool?

How much data are you fetching from Oracle?  I'm assuming you're not selecting the entire 500 GB.  But are you selecting thousands or millions of rows?  Or are you selecting dozens of rows?

Are you using System.Data.OracleClient? DataSet?
Attach the code
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now