DataGridView has Poor Response with an Access database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 14 of 14

Thread: DataGridView has Poor Response with an Access database

  1. #1
    Join Date
    Mar 2008
    Posts
    61

    DataGridView has Poor Response with an Access database

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    How many records is it adding at anyone time?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Mar 2008
    Posts
    61
    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

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I don't need to tell you the 657 rows is insignificant, even for Access.

    How many fields per row?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Mar 2008
    Posts
    61
    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.
    Attached Images Attached Images

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Bozo View Post
    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.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I forgot to ask what version of Access are you using for this?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  8. #8
    Join Date
    Mar 2008
    Posts
    61
    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.

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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)
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  10. #10
    Join Date
    Mar 2008
    Posts
    61
    Hack,

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

    Bozo

  11. #11
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    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.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  12. #12
    Join Date
    Mar 2008
    Posts
    61
    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

  13. #13
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    An interesting approach...thanks for coming back and letting us know how you resolved it.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  14. #14
    Join Date
    Nov 2009
    Posts
    1
    Quote Originally Posted by JBourgeois View Post

    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

Similar Threads

  1. Converting from access 2002 to access 97
    By wwwursa in forum Database
    Replies: 1
    Last Post: 03-07-2009, 02:20 PM
  2. Access Database, using VB as front end
    By binnymann in forum Database
    Replies: 3
    Last Post: 04-13-2008, 02:23 AM
  3. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 06:01 PM
  4. MS-Access Error - Obsolet Database Format !
    By Makl. Lemmertz in forum VB Classic
    Replies: 2
    Last Post: 10-29-2001, 12:12 PM
  5. Replies: 6
    Last Post: 08-21-2000, 08:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center