?
Solved

Oracle Query Slow with C#

Posted on 2010-11-28
3
Medium Priority
?
1,656 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 800 total points
ID: 34224501
0
 
LVL 7

Assisted Solution

by:jocave
jocave earned 800 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 400 total points
ID: 34225179
Are you using System.Data.OracleClient? DataSet?
Attach the code
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

829 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