Speed up the database!! (1 Viewer)

[MickCun1]

Hi all,

I have used this site a few times and found it to be extremely useful.

I have created a small enough access database with a few thousand records. It is hosted on a remote server and the size of it is merely 15596KB at the time of writing this. It is linked with SQL Server backend also I should mention.

It is painstakingly slow though. To switch between forms takes almost 10 seconds or more each time. Is there any ways of quickening the database??

Its on a remote server so multiple users can access it to view their own data. It is just so frustrating that all the work is done but it is operating so slowly.

Thanks in advance for any hints/tips,
Best,
Michael

 

[Mark_]

Quick answer is "Yes".

More indepth, what have you done to identify what is taking a long time?
Is the Front End on the users local machine or are they all sharing ONE copy on the server?
Do you have indexes on your tables and are you using them?
What is the network topology like? Any obvious slow points?
What else is being hosted on the "Remote server" and have you identified if there is a resource issue with the server?
What is going on with the forms that could be slowing them down? Are they looking at ALL records first or are you sure you are only requesting a subset of records?

We would need to know what you've tried, what you are trying, and what your environment is to give better answers.

 

[MickCun1]

Hi Mark_,

Thanks for the response. To answer your questions;

Is the Front End on the users local machine or are they all sharing ONE copy on the server? Yes everyone has a remoteApp they log in too on their C: drive, and access it through the one server. However I am the only one accessing it at the minute and it is still very slow.

Do you have indexes on your tables and are you using them? Yes and yes I am using them.


What is the network topology like? Any obvious slow points? The network seems fine?

What else is being hosted on the "Remote server" and have you identified if there is a resource issue with the server? There is nothing else really on this server.


What is going on with the forms that could be slowing them down? Are they looking at ALL records first or are you sure you are only requesting a subset of records? The records displayed are doing a few calculations, but with the database being so small I was sure this would not hold it up that much??

 

[isladogs]

Can you clarify what this means:

everyone has a remote app they access on their c drive

 

[MickCun1]

Hi ridders,

Apologies!! The file is located in the H: drive under CorpData, as we are all on the one server here. In this folder on the K: drive the users can open a portal that allows them to access the db on the remote server. I hope this better clears things up??

 

[NauticalGent]

Greetings MickCun1,

One point of clarification. On our server, the “H” is a personal virtual drive assigned to each individual user so that their profile has access to semi personal files no matter which computer they log onto. Although not ideal, it is still better than everyone sharing the same FE on a LAN.

Is this the case for you as well?

 

[MickCun1]

Yes NauticalGent! Do you have any idea why my db is so slow?

 

[NauticalGent]

A BE on a LAN, as others have said, is not the best of situations. Too many moving parts...

With your FE being on the LAN as well, even though everyone has their own copy, you have “doubled down” on the moving parts.

I would move everyone’s copy to the C drive and then re-assess from there. If it is still slow and you are certain you have the proper indexes and your forms are optimized, I could only surmise that you are a victim of a slow LAN.

Do you know what kind of setup you have?

 

[NauticalGent]

Here is a thread I started a few years ago when I asked the same question you are asking now. Lots of good stuff provided by the members of this forum and a really good article by Albert K which explains why your watching grass grow.

https://access-programmers.co.uk/forums/showthread.php?t=283922

 

[MickCun1]

Thank you NauticalGent!! It is literally one of the most infuriating things ever knowing the code is done but the program itself is too slow.

 

[Mark_]

Just to clarify though,

Do your end users have copies of ACCESS installed on their local machines or are they using RemoteApp to run an instance on the server?