[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vb.net handling databases efficiently (shouldn't be that hard)

Posted on 2006-05-01
6
Medium Priority
?
165 Views
Last Modified: 2010-04-23
I have a friend who I am writing a database application for.

The problem is that the application runs fine on my 3.5ghz machine, and even on a 1ghz machine he tested it on, but his laptop is like a p3 500 or something crazy slow like that.

I am specifically having a problem where it seems to fall behind after 2 seperate database calls in the same function.

Any ideas on how to make the program pace itself  so that it doesn't run into problems like this?


The other question is this.  My application is a bunch of tabs, each tab has a combobox populated from the db, and selecting an item on the combobox populates a listview.  Each time I select the tab it updates the combobox.  Each time I make a call to the database I open the connection, initialize it, querry it, close connection and trash the object.  Is this the best way to do it or should I open the database connection say at the start of the program and close it at the end?  


Please help.
0
Comment
Question by:Brothernod
  • 3
  • 3
6 Comments
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 16584364
To me, the crucial question here is whether the app (which appears to be single-user) is accessing a single-user database on the same machine.  If the database is single-user, then (subject to any memory constraints) the way to go is to load all the data once, at the start, and refresh the database at the end (or only in response to a specific user-request to do so; or at intervals with a timer, in case of crashes).  Although the initial load might take a bit of time, the app could display a splash screen or progress bar.  After that, working from its own dataset/s would eliminate calls to the database (which seems to be the principal time-consumer) and any amendments would be reflected in that dataset as soon as they were made by the user.  Although they would not get back to the database until some time later that would not matter if the database was single-user.  Whilst it may be sensible to keep revisiting a database to refresh the app's data if other users might be altering it in the meantime, or to fetch data in dribs and drabs as needed if collecting it all in one go would produce memory problems, to do so otherwise can waste time and resources.  With fast, high memory machines this may not matter.  But with small and/or low memory ones, it does.

If - e.g. because the database is multi-user or there are memory problems - you do have to visit the database more frequently than the above approach would suggest, then keeping a connection open may be a good idea.  This is because there is a time and resource overhead every time a connection is established.  It differs for different databases, but it is always there.  You can minimise that by just doing it once.  Having said that, if the database does not reside on the same machine as the app, keeping an external connection open can raise other issues.

Roger

0
 
LVL 1

Author Comment

by:Brothernod
ID: 16584495
I'm sorry I completely forgot to mention that.  The database is MS Access and will be single user.  Currently the database will have 300 records or so and may grow by 500 to 1500 records each year.  Maybe a little faster, but not fast enough to be a problem for access.
0
 
LVL 1

Author Comment

by:Brothernod
ID: 16584509
And the database will be local to the computer.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:Sancler
ID: 16584598
In that case, I would definitely go down the "load all data at the start" route.  I've got an app in a similar single-user same-machine MSAccess db scenario that happily handles eight tables, some (albeit only two-fields) with >35k records, on this basis on a really old laptop - < 200 Mhz, I think, with only 64Mb RAM.

Roger
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16584600
I say "happily handles".  It's slow - but that's not the program's fault ;-)

Roger
0
 
LVL 1

Author Comment

by:Brothernod
ID: 16592352
Unfortantly that sounds like a decent rewrite of the app lol.  I have a data access class I'm using but I don't know if i'll be able to convert it to deal with this change.

No big deal :) The bigger deal is learning to do threads so I can have the timer for backup, and learning to work with and querry datasets.

But that should all come from searching the site.

Thank you.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question