DataGridView has Poor Response with an Access database

[Bozo]

Hello,

I am new to vb .NET. I have done some VB6 programing and now I am trying to expand my skills in the latest technologies.

I have written an application that allows me to enter data into an entry screen and when I have completed the screen it save the data into an Access database. That piece is working just fine.

The part I am having trouble with is the second form that has a DataGridView object on it. When I call the form the DataGridView loads extremely slow. I am using a custom control called "DataGridViewAutoFilter" which allows filters on the columns. I am not sure if this may be causing my problem or not. Any help would be appreciated.

TIA
Bozo

[Hack]

How many records is it adding at anyone time?

[Bozo]

Hack,

Thanks for the quick reply. My Access database has 657 rows in it. The program is loading these rows into the DataGrigView as readonly. The update to the database are being handled by a separate form and there is no delay while updating.

Bozo

[Hack]

I don't need to tell you the 657 rows is insignificant, even for Access.

How many fields per row?

[Bozo]

Thanks Again for the reply.

I understand the data is minimal. I have also attached the table layout. Very basic, other than the memo fields.

[Hack]

Originally Posted by Bozo

the memo fields.

I think we may have found the culprit.

Lets do a test.

Copy your MDB to a testfile name.

Open up the textfile name mdb and remove the memo field.

Attach it to your project and see how it loads. If you see a significant gain, then do another test.

Recopy your production mdb to a testfile name (can be the same one you used for the last test)

This time, change the field type from Memo to straight text and see how that works against your datagrid.

[Hack]

I forgot to ask what version of Access are you using for this?

[Bozo]

Hack,

Thanks for the help. You hit it on the head. It is the size of the data fields. Now, would you have any suggestions on how to speed the query up? I need to store this much data in the fields. I am using this to keep track of the support tickets I work and the fields need to be large.

By the way. I am using Access 2007.

Bozo.

[Hack]

How many records do you need to display at any one given time?

Perhaps rather than using a data bound grid which will pick up more than you probably need, think about switching to a non-data bound control (like a listview or something) and populating that control through code (an SQL SELECT query which would stipulate exactly what criteria had to be met in order to be returned to you)

[Bozo]

Hack,

Thank you for the suggestion. I will play around with the list view and see what I can come up with there.

Bozo

[JBourgeois]

Just to add my grain of salt, this is a typical mistake made by programmers moving from VB6 from VB.NET.

In VB6, when you executed a query, the connection was kept active with a cursor that returned the rows 50 at a time (unless you asked to do otherwise). The system automatically fetched the other rows as needed by your request through move commands or by scrolling through a databound grid.

In .NET, the connection is not kept open. All the rows are read and stored in memory. That means that first it takes longer to get the results to the computer, and once they are there, specially if you have a lot of data (memo fields are typically big), even a relatively small number or rows can eat up the memory and force the system into using the swap file, which slows the things even more.

The trick is thus to retrieve only what you really need, and Hack suggestion is a valid one.

Since the users very often do not need all the table, it is quite easy to implement a filter controlled by a user. I often use a ComboBox over a DataGridView. The user selects a valie in the ComboBox, and that triggers a query to retrieve only the data for that value. If the user selects a new value, we go back to the database with a new query.

Since you retrieve only a subset of the database each time, you limit the amount of data that must transit on the system and reside in memory. This is usually a lot more efficient than retrieving the whole table and filtering as needed in the interface.

[Bozo]

All,

I want to thank everyone for there input on this topic. My solution was to call a spread sheet with a formatting macro in it from my .net fron end. This retrieves and formats my data very quickly.

Bozo

[Hack]

An interesting approach...thanks for coming back and letting us know how you resolved it.

[developerforum]

Originally Posted by JBourgeois

Since the users very often do not need all the table, it is quite easy to implement a filter controlled by a user. I often use a ComboBox over a DataGridView. The user selects a valie in the ComboBox, and that triggers a query to retrieve only the data for that value. If the user selects a new value, we go back to the database with a new query.

Since you retrieve only a subset of the database each time, you limit the amount of data that must transit on the system and reside in memory. This is usually a lot more efficient than retrieving the whole table and filtering as needed in the interface.

Except that you need to manage virtual mode, datapaging etc, don't tell me ADO.NET is a progress as for Winform Databinding, they don't even provide a Paging control like in ASP.NET.

The truth is Microsoft may be willing to kill the pure desktop client / server for the client / webservice schema as long term they want to deport the infrastructure of their clients to their cloud.

Otherwise I can't see any rationale of why they didn't include both facilities for disconnected mode which is great for the web and connected mode for the desktop.

So that if you want to do things as easy and above as performing as before you have to use some third parties libraries or hack it yourself see for example
http://www.udaparts.com/devguide.htm