Slow Database

[ohmydatabase]

Update:



I have implemented a method similar to the OpenDatabase() Method shown in the article provided by Shadow. In two test cases (on a user with a very slow connection) I've noticed a drastic increase in performance! A 5 sec load time was reduced to 2 sec, and a 65 sec wait was reduced to 40 sec. Still not perfect, but this is a large improvement. I believe that transferring large amounts of data through our VPN is the issue here. I plan on having the users who are experiencing this issue remote into a machine on the same network that the back-end resides. In a test case, this has almost completely solved this issue. Now I just need to find some idle computers that people can jump on. With these two improvements (while not perfect) will most likely fix the majority of my speed issues... 10/10 would recommend.

I have not found a way to close the database (i.e. Set db = Nothing) upon closing the application. Is there some event handler in VBA that can accomplish this?

[CJ_London]

it should not matter since the app is closing, but it can be done by using a form close event (e.g. if your navigation form stays open all the time it could go there, or if you have a login form that is hidden once a user has logged it). But it won't be triggered if the machine has a power outage or similar event. But then you'll be rebooting anyway....

[ohmydatabase]

I've been meaning to make a hidden login form for some time now. This may be the motivation I needed. Thank you Ajax!

[ohmydatabase]

Oh, forgot to mention...

I removed all declarations and set procedure from all of my forms and modules!

Dim db as DAO.Database
Set db = CurrentDb()

I now open the database in my AutoExec Macro and I have experienced significant gains in performance. However as mentioned before, a slow network is a slow network. Not much you can do about that. The OpenDatabase Method (or variations of it) are worth implementing when lots of communication with the back-end is required and there are many users.
As far as networks are concerned, I've noticed that transporting data through either a VPN or another location (basically a different \\whatever than the one the back-end resides in) is pretty dang slow. To remedy this I've guided users that using VPN's or in different locations to use the Remote Desktop Connection to remotely log into a machine on the \\whatever network. From there the transport of the data is quick and all data crunching is being done by that machine. Only the display, keyboard, and mouse are being sent via VPN. This showed the most greatest increase in speed. The problem now is finding idle machines that I can have outside users remote into.

I'm flirting with the idea of an "offline" mode where the afflicted users can copy a version of the back-end to their local machine and do all the functions they need. The any legitimate inputs/changes to tables would then be synchronized with the "live" back-end on the network. I haven't put the time into developing that yet, but that may be in my future.

[ohmydatabase]

Lastly,

I've noticed that poor, cumbersome code WILL bog down the database. Idk if you have this specific problem, but it is something I'm currently having issues with.

[CJ_London]

I'm flirting with the idea of an "offline" mode where the afflicted users can copy a version of the back-end to their local machine and do all the functions they need. The any legitimate inputs/changes to tables would then be synchronized with the "live" back-end on the network. I haven't put the time into developing that yet, but that may be in my future.

you need a fair number of queries (around 4-6 per table, but can be build in VBA) and you will need additional columns in your tables - a GUID to produce a truly unique ID for synchronising (don't be tempted to use this as a PK tho, it is too big and has other issues when used as a PK) a date/time column for date of last sync and another to show date/time of last insert or update. Also you will need to change your PK field to random rather than incremental.