[VB.NET] DataAdapter.Fill = SLOOOOOOW

[James Rose]

Hello gang,

We're translating a VB6 app into .NET 2005 using Oracle's ODP.NET provider. The issue is we have a stored proc that returns multiple resultsets/recordsets/cursors/(whatever you want to call em) In Toad or other SQL tools the query comes RIGHT back... however in the .NET code the .FILL method is slow.

cmGet = New OracleCommand

dsGet = New DataSet

daGet = New OracleDataAdapter(cmGet)

daGet.Fill(dsGet) '10 seconds here!!

I have spent the last hour+ reading a ton of posts on newsgroups and not a single one gave an answer (or I've gone blind... oh no, Ive gone blind!!!)

Any thoughts?

[LRoling]

Let's get the easy problems out of the way first. Are you testing this on a single machine or on the actual server? Most SQL tools will be much faster than the actual development environment testing as well, so it's not fair to compare them. Testing in a similar environment to the actual production environment will give you a better feel for how slow it really will be.

Is the SQL a problem? Specifically, does it contain a lot of joins or search for a lot of string?

Here's an example to try as well:

ds = null;
ds = new DataSet();
DataTable dt = new DataTable();
da.Fill(dt);
ds.Tables.add(dt)

Now, with that example as a foundation, you COULD fill a DataTable with a DataAdapter, then add it to the DataSet. See if that has any type of performance benefit.

There is also some SQL queries I've seen that involve Date Ranges that cause the Fill to become very slow. If you have date ranges in your Stored Proc that use SQL Parameters. If you do use Parameters, there are tons of problems that were solved if you search Google for them.

[James Rose]

Yes we are testing this against a server, in fact 3 different servers. One on site (windows based) and two off site (CoLo) one of these is our production db.

The SQL Query is rather plain (Im at home now and dont have the exact sql) In Toad or SQL*Plus the results from this proc come RIGHT back. So we know it's not the connection or execution. We have found that one of the queries in this proc (there are 11) is pulling back 26000 rows. This particual query is taking 7 seconds of the .FILL (damn!)

And yes, really the app should not be pulling back all 26 THOUSAND rows (I just got here, we bought the software and now me and my team-mates have to fix/improve it.

I'll try you code snipet in the morning, so thank you VERY much for your time.

We're also gonna try another ADO.NET provider (http://www.datadirect.com/products/net/net_for_oracle/index.ssp) and see if this works any better. Our other option is to use the DataReader and only use the DataTable when nessasary. (Decent sized application, so there is a lot for us to work out)

Peace,

James

  LRoling said:

Let's get the easy problems out of the way first. Are you testing this on a single machine or on the actual server? Most SQL tools will be much faster than the actual development environment testing as well, so it's not fair to compare them. Testing in a similar environment to the actual production environment will give you a better feel for how slow it really will be.

Is the SQL a problem? Specifically, does it contain a lot of joins or search for a lot of string?

Here's an example to try as well:

ds = null;
ds = new DataSet();
DataTable dt = new DataTable();
da.Fill(dt);
ds.Tables.add(dt)

Now, with that example as a foundation, you COULD fill a DataTable with a DataAdapter, then add it to the DataSet. See if that has any type of performance benefit.

There is also some SQL queries I've seen that involve Date Ranges that cause the Fill to become very slow. If you have date ranges in your Stored Proc that use SQL Parameters. If you do use Parameters, there are tons of problems that were solved if you search Google for them.

[azcodemonkey]

Does Toad or SQL*Plus actually return all 26000 rows at once, or does it just fill the grid lazily, i.e. all 26K rows are viewable instantaneously, or do they fill as you scroll?

[azcodemonkey]

Actually, what I should be asking is if you're profiling, or just looking at first result sets. I'm completely ignorant of both Toad and SQL*Plus, so I'm shotgunning this. ;)