0

I have a SQL query which executes within seconds (< 4) in Toad, but it takes around 8 mins when run using C# (System.Data).

What could be the reason ? How Can it be resolved ?

SQL Query

SELECT TAB1.COL1, TAB1.COL2  
FROM   TAB1, TAB2 
WHERE  TAB1.COL1 = TAB2.COL1 
AND    COL2 IN (...)

C# Code

this.DbAdapter.Select(cmdText, dbparams,
                      "TAB1", this.dbConnection.Transaction);

What I referred already and does not help:

Community
  • 1
  • 1
SimpleGuy
  • 2,764
  • 5
  • 28
  • 45
  • 1
    How much data is it? Is Toad actually loading all of it? – nvoigt Aug 26 '15 at 09:58
  • Why dont u use join? – Tharunkumar Reddy Aug 26 '15 at 09:59
  • You haven't provided enough code to give a sensible answer. Your c# code seems to be running in a transaction? Do you run the query in Toad within a transaction? – Daniel Kelley Aug 26 '15 at 10:03
  • @DanielKelly in toad only this query is run. In code my transaction value is NULL. Do tell me if u need more information – SimpleGuy Aug 26 '15 at 10:06
  • @nvoigt yes Toad loads all 200 rows – SimpleGuy Aug 26 '15 at 10:10
  • possible duplicate of [Query runs fast in Query Analyzer but slow in C# application](http://stackoverflow.com/questions/12483064/query-runs-fast-in-query-analyzer-but-slow-in-c-sharp-application) – Dah Sra Aug 26 '15 at 10:27
  • @dahsra No. It doesn't help me. See in question, i have mentioned that i already visited this link – SimpleGuy Aug 26 '15 at 11:20
  • @SimpleGuy The all data is about 200 rows? And where you run the asp code? in the same machine? – CompEng Aug 26 '15 at 12:18
  • Try adding the condition AND ROWNUM <= 200 in the where clause then let us know if you still notice slowness on the C# app. – Alessandro Rossi Aug 26 '15 at 12:44
  • @AlessandroRossi I did but still slow.. And actually I can't add such a condition in code permanently as there might be more rows which I would need – SimpleGuy Aug 26 '15 at 12:58
  • The are too many missing details to help you further. I use generally Oracle ODP .Net version 11.2, but I don't use DbAdapter. I fill a DataTable through an OracleDataReader object (where I tune the FetchSize parameter - I don't know if you may find a similar property in your class) and I can get results from query and show them in WPF DataGrid faster than TOAD. Anyway are you sure that the query is using the same explain plan both times? – Alessandro Rossi Aug 26 '15 at 14:12
  • @AlessandroRossi It would not be possible for me to switch to ODP.NET as the entire project relies on it.. so.. a big task... what do you mean by `plan` times ? – SimpleGuy Aug 27 '15 at 04:30

2 Answers2

1

System.Data.OracleClient is depricated and terrible. Use Oracle's ODP.net (if you can control or know the client setup on the deployed machine), managed ODP.net (if you can't control the client on the machine where the program will run) or Dev Art's dotConnect for Oracle.

http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

I used System.Data.OracleClient back in the day, and there were all sorts of mystery pauses, delays, and unexplained phenomena. For example, simply invoking a bind variable caused a 17 second delay in the execution of any query (we timed it).

Admittedly, 8 minutes is shocking, but as a first pass switch your driver and see how much closer that gets you.

Hambone
  • 15,600
  • 8
  • 46
  • 69
  • Changing from System.Data.OracleClient to ODP would be a big task as it is a legacy project.. so can't do that right now atleast.. – SimpleGuy Aug 27 '15 at 04:31
0

Try this

Use Stored procedures instead of Query.

Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and invocation overhead.

By grouping SQL statements, a stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time.

Stored procedures provide improved performance because fewer calls need to be sent to the database.

For example, if a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.

Pravin Fofariya
  • 346
  • 2
  • 11