.NET Questions (CLOSED)

Questions and Answers on any aspect of .NET. Now closed.

This discussion group is now closed.

Have a question about .NET development? Try stackoverflow.com, a worldwide community of great developers asking and answering questions 24 hours a day.

The archives of .NET Questions contain years of Q&A. Even older .NET Questions are still online, too.

DataAdapter.Fill is slow

My .NET 2.0/WinForms app has several UI data displays ("Views") which share data access code through a base class ("DataViewBase").  The View initialization code looks like this:

[Object]View
- Build SQL string specific to this View

DataViewBase
- Create IDbCommand
- Create DataAdapter
- Create DataSet
- DataAdapter.FillSchema(DataSet, SchemaType.Source)

[Object]View
- Bind DataSet to UI control (e.g. Janus GridEX)

The View gets refreshed (DataAdapter.Fill(DataSet)) when the View is initially loaded, and again on user interaction or a timer.

Performance of this design is great, except for one View with a large number of records (~27k).  The FIRST DataAdapter.Fill call takes 2-3 seconds.  All subsequent calls take 30-45 seconds.  The SQL query, when run in SQL Studio, takes 1-2 seconds regardless of the number of times run.

The problem View's query is to a single table (no joins).  Other Views have complex table joins (up to 5 tables), but run with no problems.  This leads me to believe that the issue is the number of rows being returned.  The problem View has +27k rows, other Views have 500 or less.

Some posts suggested that using IDbParameters can slow the call to DataAdapter.Fill.  However, none of the Views are using IDbParameters.  I've tried switching SchemaType from Source to Mapped, with no luck.  This problem has been reproduced on two demo machines, so I'm pretty sure it's not a configuration issue on my dev computer.

This problem has me stumped.  Any help or leads (no matter how obvious) would be GREATLY appreciated.
BrandonK
Monday, April 21, 2008
 
 
> The problem View has +27k rows, other Views have 500 or less.

It's not the DataAdapter that's slow.  It's the time to retrieve 27000 rows!

I assume this is SQL Server 2005.  This article is for a webform, but the idea holds for a winform application as well.

Custom Paging in ASP.NET 2.0 with SQL Server 2005
http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
Art Metz Send private email
Monday, April 21, 2008
 
 
"It's not the DataAdapter that's slow.  It's the time to retrieve 27000 rows!"

The DataAdapter returns 27k rows in under 3 seconds during the first Fill, but takes 45 seconds for every Fill after.  3 seconds to load the screen is more than acceptable performance from an end-user perspective.

Paging is one option; completely re-creating the DataAdapter is another I've heard suggested.  Before making radical changes to how the Views load data, I'd like to understand why the current design isn't working.
BrandonK
Monday, April 21, 2008
 
 
I should add that the DataSet bound in the UI is de facto read-only.  None of the Views call DataAdapter.Update.
BrandonK
Monday, April 21, 2008
 
 
Can't you filter the rows using the select statement on the database?  If not...  it sounds like you are having a constraint checking problem. Try the following pattern:

MyDataTable.BeginLoadData
MySqlDataAdapter.Fill(MyDataSet, "tbMyTable")
MyDataTable.EndLoadData

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
Jim Brooks Send private email
Monday, April 21, 2008
 
 
Now this is interesting...

Test 1:
- commented out the code that binds the DataSet to the UI Grid
- All DataAdapter.Fill calls now take less than 2 seconds.

Test 2:
- re-activated the data binding code
- 1st call to DataAdapter.Fill took 0:03
- Repeated calls took 3:45 (in VS 2005 IDE)

@ Jim Brooks
I thought about requiring the user to specify at least one filter criteria before displaying anything.  However, what I'm seeing in demos is that users like "snooping" round in the data.  More than one user has said that they learned something just by looking around.

I'll give your advice about PKs a try now.
BrandonK
Monday, April 21, 2008
 
 
Given the problem description, it sounds like the issue is related to the fact that you already have 27K rows. The first fill is fast and following ones are slow. The obvious difference is that the first fill is also hitting an 'empty' object. Are you clearing before filling? Your code would suggest not, but I seem to recall a ClearBeforeFill property that might make that automatic.
Ron Porter Send private email
Monday, April 21, 2008
 
 
And, of course, if it's actually the binding or UI that is slow instead of the fill itself, you may have success with things that suspend  or remove and replace binding, UI updates, etc.
Ron Porter Send private email
Monday, April 21, 2008
 
 
You definitely want to suspend binding while you're filling the DataSet.  Every row that you add to the DataTable raises multiple events that the BindingSource is handling to push data out to the controls.
Robert Rossney Send private email
Monday, April 21, 2008
 
 
"The DataAdapter returns 27k rows in under 3 seconds during the first Fill, but takes 45 seconds for every Fill after"

If you're not retrieving 27K rows in _way_ under 3 secs then something's f_ed up with your DB layer (SP or script)
jonathan
Monday, April 21, 2008
 
 
@Jonathan
Agreed.  My previous posts were inaccurate, in that the 3 second load time included GUI initialization and other UI related code.  Timing the DataAdapter.Fill call alone comes to 1.2 - 1.4 seconds.

The slow-down appears to be related to having the GUI control bound to the DataSet, then calling DataAdapter.Fill.  Another post in their forums suggested:
- removing the data binding
- calling DataAdapter.Fill
- re-creating the data binding

This has gotten the total re-load time down from 3-4 minutes to under 3 seconds (~1.5 to Fill the DataAdapter).

I've got a post in their forum asking if this was the "correct" thing to do.
BrandonK
Monday, April 21, 2008
 
 
Since this is a WinForms app then if you use a BindingSource control, you can suspend the raising of events. This is probably a better practice than removing/re-adding the bindings.

See here:

http://windowsclient.net/blogs/faqs/archive/2006/07/12/what-is-a-bindingsource-and-why-do-i-need-it.aspx

So then you just do

bindingSource.RaiseListChangedEvents = false //before load
//do your load
bindingSource.RaiseListChangedEvents = true //after load

Monday, April 21, 2008
 
 
From my experience:

1) Filling a GUI component is the slowest
2) Filling the dataset is next
3) Reading the data last

Note: Returning 27k rows with no joins should be 0s (yes 0).

Stating your Grid is read only tells me you can do a lot of optimization. Most Grids have options for read-only etc. which gets rid of a lot of crap that it would normally need for updates.  Alternately, they may be a Grid type control thats a basic read only/simple display grid.

Do you have to use a dataset?  I think you can bind to a Datareader which is ALOT faster, just ensure you close the reader.

To accomplish all in one go, put in some filter the user can select as someone else stated if possible (excluding "all")
Anon cause I fear the real world
Monday, April 21, 2008
 
 
y dont u fill this data in view state or session,
insteadof picking up agin from data base.

may this will increase u r performence.

sorry,if any thing wrong
degala prasad Send private email
Tuesday, April 22, 2008
 
 
sorry i thought its in webapp
i am not that much aware of winapp
degala prasad Send private email
Tuesday, April 22, 2008
 
 
Thanks to everyone for their posts.  The problem seems to be with the interface between the DataAdapter and the Janus GridEX.  I'm swapping posts in the Janus forum about the problem and will report back.

RE: Using a DataReader
In the current version, the query will only show one table.  Future versions will show child tables.  To do that, the Janus GridEX needs a DataSet.  Agreed that a DataReader would be much quicker.  Your post got me thinking about adding an option to bind a DataReader instead of a DataSet for Views not needing hierarchical data displays.
BrandonK
Wednesday, April 23, 2008
 
 
What are you doing with 27K rows?  I hope you're not showing them to the user to filter through manually.  You might try asking for more search criteria.

If you can't do that, are you already pulling down the full table, or is this just a segment of it?

If it's just a segment, what is your WHERE clause like?  Do you have good indexes on the fields used?

If it's the entire tale, could you pull it in when the application first starts up, and hide the fill time with a splash screen?

Where is the database located?  If it's on the local machine or a lightly trafficked network, 27K should be almost instant.  But if this is a WAN or an already busy network, 27K could be torture.
Joel Coehoorn Send private email
Friday, May 02, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz