Improve FE - BE performaces on LAN (1 Viewer)

[823]

Hi at all,

I use an access DB already splitted: 1 BE on server, n. 5 FE on clients on my LAN network.
BE is 80 MB
FE is 7 MB
It works well when only one user is logged in; But it becomes slow when 2 or more user is logged in.
I'm not a coder, (the db has been made by a friend), but now I would like to uderstand how can improove the db speed when more than user is loggeed in (if it is possible).
I have already done some attempts, but with no good results:

1
I've tried myself to migrate the BE to SQL using this tool:
Microsoft SQL Server Migration Assistant for Access
but the FE becomes very slow ....much slower than with access BE .
So I retoruned back to access BE.

2
Reading an article on web:
I've splitted the BE in two (and then three) Backends file (30 mb each)
This hasn't improve the speed so I came back to 1 BE.

3
Today I've opened on the server 4 FE simultaneously, and they are very speed!!!
With this last test I suppose that the problem is my LAN or the way access comunicate in LAN, or maybe my network adapters, or something concerning my LAN.
What do you think?
How can I do?

Maybe some access options or client settings can be set to improve the speed?


Thanks in advance at all!

 

[823]

I forgot:

- I'm using Access 2016
- We have already implemented Persistent Conncetion

 

[Pat Hartman]

1. Jet/ACE are super fast for low volumes of data. Faster than SQL Server especially if you use old style Access techniques like binding forms to tables and then using local filtering to find desired data. You also nee proper primary keys/foreign keys and indexes as appropriate.
2. Spliting the BE into multiple files doesn't have anything to do with speed. All it does is prevent you from implementing Referential Integrity. So, put the files back together and add the appropriate RI.
3. Sounds like your LAN could use tuning

 

[Mark_]

Where does each client save their copy of their FE? Just making sure they are not all trying to use the same copy of the FE stored on the server.

 

[823]

FE copy is stored in each Hd client

 

[CJ_London]

Take a look at this link -

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

 

[823]

Thanks for your answer CJ_London

about your helpful link "addressing performance issues"

Access
17. Has name autocorrect been turned off? --->yes
18. has front end been compacted? ---> always when BE or FE is closed "auto"

contrariwise for

Tables, queries, Forms/reports, Modules,

I haven't the competence and experience to chek if in my db they are ok, or not

 

[Minty]

Is the backend file in a trusted location for all the clients?
I've seen that cause poor performance.

Are you on a gigabit network?
100Mb will be surprisingly slower.

 

[823]

Thanks Minty for reply,
tomorrow I will test my LAN with iperf (I found this LAN test on web....I hope It is ok)

How can I check this?
Is the backend file in a trusted location for all the clients?

 

[Minty]

How can I check this?
Is the backend file in a trusted location for all the clients?

Open Access on a end user machine.
Goto file¬Options

There is a a section called Trust Center.
Click on the Trust Center Settings... option
Within that is Trusted Locations:

Make sure you tick the Allow trusted locations on my network box, and add the path of the BE file.

 

[823]

I didn't know that option in access..... tomorrow I will add trusted location at the folder where BE is placed.
Maybe can help. Thanks Minty

 

[The_Doc_Man]

When you are discussing slow-down issues, what (generically speaking) are your users doing. Are they opening forms? If so, are the forms bound to tables or to queries? Please tell us that your users aren't DIRECTLY interacting with tables or queries. Are they entering data or editing data or merely reading/reviewing data?

It is bizarre, but I have found that I got better performance if my forms were bound to queries. I cannot tell you why it is so because Access is not OpenSource. We cannot see the code to know why this happens, but I've observed it. In the past when I created a query that presented exactly the same fields as the table would have presented, I got better results. It may sound odd but making a single-table query helped stabilize the system.

It may have to do with the idea that opening a table means you have to negotiate on the network while searching for the right data to gain access to the disk blocks holding that table, which is on the remote disk. Both Windows and Access file locking have to interact multiple times. However, when you open a query, it makes a list of where everything is and keeps that list in the workspace of the FE file - which is LOCAL rather than on the LAN. In that case, you don't have to search for anything and the interaction has more local elements, which are always faster than network elements. I have no idea why you would have BETTER performance when you opened that back end four times simultaneously but I might GUESS that it is related to Windows caching the data on your shared drive being used by four users at once.

 

[823]

Thanks,
unfortunately I don't know if my users are directly interacting with tables or queries ( I don't have the competence to understand this). I suppose and hope no
Please note that the good speed performance with 4 FE open there are only if they are open into the server (no LAN required).

 

[The_Doc_Man]

Please note that the good performance with 4 FE opened there are only if they are opened into the server (no LAN required).

Are you saying that your four users just logged in to the server directly and ran Access in that context? If they are running the same copy of the FE then you SERIOUSLY risk data corruption doing that. However, if you did exactly what I think you did, your slowdown symptom indicates file locking issues. Your four users, when connected directly to the server, still had the same locking issues but because they were not network-based locks, they resolved about 100 times faster. Anything done locally (directly on the host) runs at memory speed or local-disk speed. Anything done over the network runs at memory speed or local-disk speed on the host PLUS network speed between client and host, and that network is often anywhere from 100 to 1000 times slower than the memory and disk bus on your host.

I don't know if My user are directly interacting with tables or queries ( I don't have the competence to see this).

You need to learn how to manipulate the interface enough to answer questions like this because the kind of fixes that this might require will be beyond your capacity to perform the necessary work. No disrespect intended, but you have a LOT of research and reading ahead of you if you are to maintain this DB. There is a steep learning curve because for better or worse, Access lets you look at everything in your DB files. The sheer volume of what you can see is a major contributor to the learning curve.

See if you can find a book on the subject of Access. There is an "Access for Dummies" but there are other books as well. Or start looking up on-line videos on YouTube or some other provider on the subject of forms. Access objects have properties and that tells you what they are doing (sometimes).

To answer my question about tables/queries as a form's basis, you want to see something called the form's .RecordSource property. That property will either be the name of a table, the name of a query, or an SQL statement. You SINCERELY hope it isn't an explicit SQL statement.

 

[823]

Thanks, Running 4 FE on the server (where BE is placed) has been only a test I've made myself.

 

[theDBguy]

I didn't see it mentioned (apologize if I missed it), but have you tried using a "persistent connection" to the BE?

 

[823]

Thanks for reply
Yes, we have added persistent connection to be.

 

[nector]

This sounds like the index issues here, if the tables are properly indexed and you are using wired local area network access works faster. Never use WIFi in this case.

Shifting to SQL Server is like climbing mount Everest, if you really want performance forget about that SQL server its just a story for kids use MYSQL the performance is super actually you cannot tell whether you are using local Access DB in terms of speed. The tuning thing others are advising is pain, forget it unless you are ready to spend more, then its fine. To sum it all SQL Server will never beat MYSQL in terms of performance it will always be superior.

The choice is yours

 

[Minty]

To sum it all SQL Server will never beat MYSQL in terms of performance it will always be superior.

That will be why no one uses SQL server then. What a stupid generalised statement.

I'd like it backed up with some actual evidence. Preferably using the same hardware and similarly enabled software package.

 

[823]

If really mysql has best performances (for my case) I could import BE into a Mysql server and use only FE Access.

I've imported access BE into SQL server last week and it was very slow so I come back to access BE.

But if MySQl is different (i don't know) I could try.... what do you think?