DataAdapter.Fill very slow

[lance]

I am currently having a problem with a DataAdapter.Fill performing
very slow or timing out.

Our code is written in C#, platform Windows 2003 and the database is
SQL Server 2000. However after reviewing other postings this seems to
be an ado.net problem and not tied to SQL Server. groups.google.com
search: DataAdapter Fill Slow

We use Typed DataSets and stored procedures in our code. The query is
very simple. It queries three tables that are left joined and has a
where clause for an ID, and two dates. The tables have been indexed
appropriately. I have tried multiple things to include:

Changing from Typed DataSets to the regular DataSets.
Creating performance counters to see what is happening on the
machines.
Running the query in Query Analyzer and Enterprise Manager runs in .5
seconds or faster.
Changed databases and machines.
Rebooted machines, refreshed SQL Server services, restart SQL Server.

When I debug in DotNet it always points to the Fill of the
DataAdapter. If anyone has any suggestions it would be appreciated.

 

[William \(Bill\) Vaughn]

It sounds like you've followed most of the advice that I would
suggest--except for looking at the profiler. Of course, when you execute a
SP the compiler builds a query plan that's cached in memory for an
indefinite period of time. If subsequent queries use different parameters,
they might not work well with the existing (cached) plans.

Let's see what the profiler shows ADO.NET is using to execute the SP.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

Oh, and how many rows are you returning?

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

2700 rows is quite a few for the Dataset, but I've seen it process 30000 in
less time. However, I was using SQL Server and perhaps the schema was
simpler. Try returning the data in an untyped DataSet and see if that helps.
Yes, simplify is my first inclination.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Lance Farquhar]

Using a non typed dataset had the same affect. That was one of the first
things I tried in a test app. Due to time constraints, management
decided to change the feature to something else that performs better.
Thanks for your help. If I do find out what the problem was I will post
it for others to see.

Lance

 

[Kawarjit Bedi [MSFT]]

Hi Lance,

I'm assuming that the concerned DataTable/TypedDataTable has
PrimaryKey/UniqueKey constraints defined on it. If that is indeed the case
then try the following:

1. Removing the PrimaryKey / UniqueKey constraints (if any) from the
DataTable/TypedDataTable being filled.
2. Perform the fill operation by invoking DataAdapter.Fill
3. Add the PrimaryKey/UniqueKey constraints that were removed (if any) in #1

Use the above, only if you are absolutely certain that the incoming data is
valid, no 2 rows have the same primarykey value, else you will end up with
primarykey constraint violation when performing step #3 above.

Hope it helps.

Thanks,
Kawarjit Bedi

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Matt]

I was having a very similar problem. My resultset was only 10 rows
with 34 columns (many of the columns had null values but type of
varchar 50-200). Query analyzer ran the stored proc in less than a
couple of seconds but my DataAdapter.Fill would time out. I changed
the stored proc to return the 10 rows but with only the critical
columns (16 total) and the DataAdapter.Fill seemed to work fine.

I'm guessing the types and number of the columns really impact the
fill method but I do not know what the practical/technical limits are.