Slow in filling .NET DataSet from Oracle 9i

Question

Hi,

This is a problem that my friend asked over the phone. The C# 3.5 program he has written is filling a Dataset from a Patient Master table which has 350,000 records. It uses the Microsoft ADO.NET driver for Oracle. The ExecuteQuery method takes over 30 seconds to fill the dataset. However, the same query (fetching about 20K records) takes less than 3 second in Toad . He is not using any Transactions within the program. It has an index on the column (Name) which is being used to search.

These are some alternatives i suggested :-

1) Try to use a Data Reader and then populate a Data table and pass it to the form to bind it to the Combo box (which is not a good idea since it is likely to take same time)

2) Try Oracles' ADO.NET Driver

3) Use Ants profiler to see if you can identify any particular ADO.NET line.

Has anyone faced similar problems and what are some ways of resolving this.

Thanks, Chak.

Answer 1

Without knowing the actual code he uses to accomplish his tasks and not knowing the number of rows he's actually fetching (I'm hoping he doesn't read all 350K of them?) it's impossible to say anything that's gonna help him.

Have him add a code snippet to the question for clarity.

Answer 2

Toad would typically only fetch the first x rows (500 in my setup). So double check if the comparison is valid.

Then you should try to seperate the db stuff from the form stuff if possible to see if the db is taking up the time.

If that's the case, try the Oracle libraries if that is any faster, we've seen 50% improvements between the latest Oracle driver and the standard Microsoft driver.

Answer 3

You really need to do an extended SQL trace to see where the slowness is coming from. Here is a paper from Cary Millsap (of Method R and formerly of Hotsos) that details doing this:

http://method-r.com/downloads/doc%5Fdetails/10-for-developers-making-friends-with-the-oracle-database-cary-millsap