How do i speed up the performance of VB 2005 programs??

Question

Hi, im making an accounting program using vb 2005, my database is MDB (access 2003), the problem is when loading the forms, it connects to the database (which is on the server computer) it takes too much time, like 10 seconds to open each form, but next time u open it it opens quickly, i removed the myTableAdapter.Fill(myDataset) from the Form_Load sub but its still slow, any idea???

Note: the program must work on network, 5 or 6 users will be using the same DB at same time, im afraid it will be too much slow

Answer 1

The probllem is not the DB, although I would agree that access is not a good choice ESP multi-user. The only reason most people even use it is that it comes with office.



OK so theres the rant, here's your sol.



When you write your DAL you have to write custom code to ONLY pull back the needed info, NOT the whole DB to manipulate. Every time you write or read the information back to the server everyone gets to wait. Try using a Datareader as opposed to a Dataset on some of your calls, most people could use this instead and you would get a substantial speed increase.. Most importantly make sure you use well formed SQL statements that give you only what you need and nothing more.



It opens quickly the second time because you grabbed the whole DB and put it in a dataset. Question is when do you write the changes back to the original DB and how do you do this.



Access was not designed for multi user control. It can be done but its asking more than it was designed for.



SQL Lite for the other guy that posted is only for 2 users and puts everyone else in line. It's not secure and no host I know of lets you use it online.

Answer 2

I recommend creating a data portal to handle your transactions, because, as you know, connecting to a db every time you open a form is expensive (even worse on a network)



Try using a data layer or "portal" to handle cached data. For example, User A requests information from the last fiscal year. He/she makes a request to the data layer, which then queries the database. If the information is found, it is instantiated in a DataSet or other object and sent over the network through .NET remoting services to the client program on User A's computer.



Then User A can work on this disconnected set of data. When User A is finished, the client program then sends the data back to the data layer, which it can queue along with other transactions from the other users heading back to the database. Or you could choose to commit data at specific intervals, keeping the connection closed in the meantime.



This works especially if the users are working with one set of data and not making numerous, random queries.



Rockford Lthoka's VB.Net 2.0 Business Objects from Apress deals with this topic very well.

Answer 3

Have you tried using SQL server instead of an access database. SQL server 2005 express edition is FREE.



Also, it sounds like your forms are doing large queries when the form loads. I would check to make sure that the tables accessed have the appropriate indexes on them.

Answer 4

Use and send cookies!

Avatarxz