Solved

Oracle Query Slow with C#

Posted on 2010-11-28
3
1,504 Views
Last Modified: 2012-05-10
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.

BR,
0
Comment
Question by:GurcanK
3 Comments
 
LVL 5

Accepted Solution

by:
roxviper earned 200 total points
ID: 34224501
0
 
LVL 7

Assisted Solution

by:jocave
jocave earned 200 total points
ID: 34224988
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?

0
 
LVL 3

Assisted Solution

by:abdkhlaif
abdkhlaif earned 100 total points
ID: 34225179
Are you using System.Data.OracleClient? DataSet?
Attach the code
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question