Slow performance over network advice required, (1 Viewer)

[tucker61]

My database is split with a front end that is used by upto 200 people,* size of front end is 12mb, and each user copies this into their temp folder everytime they open the database.

The back end - I actually have 3 different back ends.* 1 that imports data from our system every night, this file is approx 300mb, my forms lookup information from this file,* 2nd file is the data that we input, this is currently 250 mb, and the 3rd is some data we archive daily, this is currently 100mb.

I have a schedule that copies all the back ends to a slave pc every 2 hours, and overnight they all compact and repair.

Backend 1 and 2 are used in the majority of forms, and are stored in different sub folders.
Backend 3 is only used by me and is not linked to any forms, but it is linked to the front end.

In total we have approx 200 people who use the database, the majority of them are based in the same building as the server, but approx 50 if them are based approx 40 mile away and this causes poor performance over the network. A form in head office can take 20 seconds to open, but at the remote sites this can turn quickly into 4 minutes.

Access is not supported by our it team, and I have no control over the network, so need to try and speed up without any internal support

I suppose my question to start with is should I have 3 back ends in different folders, should i move them sll into the same folder, or should these all be in 1 back end. I have 3 back ends as I was thinking it would be quicker to pull the smaller files over the network instead of 1 big file,

Any help appreciated

*

*

 

[CJ_London]

1 backend is generally better than 3 backends. Don't think different folders makes a difference.

I have 3 back ends as I was thinking it would be quicker to pull the smaller files over the network instead of 1 big file,

they shouldn't be being pulled anywhere

Even 20 seconds seems slow to me - I would expect a SLA of 2-3 seconds.

I presume your tables are properly indexed?

take a look at post #1 here - is there anything suggested that would improve things?
https://www.access-programmers.co.uk/forums/showthread.php?t=291269&highlight=index

otherwise it is down to network performance - which should be within the IT remit since it affects all apps that cross the network, not just access.

Otherwise migrate the backend to sql server or express at each site and look at using synchronisation

 

[AccessBlaster]

Make sure you do at least this >5 tips to make your Access database super-fast

 

[The_Doc_Man]

Access is not supported by our it team, and I have no control over the network, so need to try and speed up without any internal support

Hate to break it to you, but NOBODY supports Access on a network. (They tolerate it at best.) Because Access uses Server Message Block protocols, it is indistinguishable from a general Windows File Share. Other than having a folder set aside on a server, there is nothing to support.

AccessBlaster's 5 tips are good. CJ's longer list is good. Read them and then re-read them a couple of times to be sure you understand what is being suggested.

Here is what you need to know about the "indexing" suggestions that appear in both lists, and near the top of those lists. Access databases using JET or ACE have to work on the data by pulling it across the network because the program doing the work is on the machine hosting the front end file. So the DB engine pulls data, diddles with it, and does whatever your app says to do - from your machine. That ALSO means that if there is a writeback, another network operation is required - PLUS the overhead of checking for inadvertent data collisions.

If your tables are very wide (i.e. several long text fields per record), you are doing a LOT of network ops. However, if the tables are indexed and your queries are written to take advantage of the indexes, something neat happens. The engine only reads the indexes first so it can locate the data it wants to see and NOTHING ELSE. I.e. less data gets moved, which means that the query needs less time to draw data to itself.

You will have to take a survey of the queries you use to see which fields are used most often and verify that those fields are indexed. You DO have a limit on indexes AND there is a cost in terms of overhead because if your indexed fields get changed a lot, you will have a lot of work in index maintenance. But when performance is the problem, a good set of indexes is a big part of the answer.

 

[AccessBlaster]

Hate to break it to you, but NOBODY supports Access on a network. (They tolerate it at best.) Because Access uses Server Message Block protocols, it is indistinguishable from a general Windows File Share. Other than having a folder set aside on a server, there is nothing to support.

Access also has automation (macros). Macros can introduce security issues.

 

[isladogs]

Access also has automation (macros). Macros can introduce security issues.

So do Word, Excel, PowerPoint etc. Network staff can lock down options in each program both to prevent macro execution and use of ActiveX controls and to prevent users reversing those settings.

 

[AccessBlaster]

So do Word, Excel, PowerPoint etc. Network staff can lock down options in each program both to prevent macro execution and use of ActiveX controls and to prevent users reversing those settings.

Yes Network Staff can. But they are not inclined to help employees punch perceived holes in their Network.

It's their world your just passing through.

 

[isladogs]

Yes Network Staff can. But they are not inclined to help employees punch perceived holes in their Network.

It's their world your just passing through.

Yes I remember it only too well and I don't miss the battles with network staff one iota.
Now that I no longer work for anyone else, I 'only' have to worry about convincing network staff about the safety of my commercial apps on their networks when demonstrating apps before purchase.
Sometimes I fail ... which means no sale. However if organisations do purchase my apps, they are made aware of exactly how the apps need to be setup.

 

[The_Doc_Man]

If you are lucky, all your IT staff does is set up the requirement that you explicitly trust a file once. Thereafter, if you don't move it or update it, you can use even the macros because it is now trusted.

 

[AccessBlaster]

If you are lucky, all your IT staff does is set up the requirement that you explicitly trust a file once. Thereafter, if you don't move it or update it, you can use even the macros because it is now trusted.

Getting IT to appreciate the suitableness of your statement is the challenge we all face.

 

[The_Doc_Man]

Getting IT to listen to ANYTHING is a challenge we all face.

 

[Steve R.]

Your current situation is almost a repeat of mine before I retired (years ago now). Interesting how the same stories repeat themselves. :banghead:

Essentially, I was able to resolve the slow connection problem by having the back-end moved to Microsoft SQL Server. The IT staff, obstructed this approach, so I was only able to get one database moved to Microsoft SQL Server before I retired.

If the IT staff were to allow you (insert laugh track of IT staff laughing) to run your own SQL server, you could use MySQL or MariaDB. I'm using MariaDB at home.

 

[AccessBlaster]

If you ask for space on a older server you are more likely to be granted permissions. My DB's resides on ADOSQL2, the flagship server is not named anything like that.

The point being they can isolate you away from the main server.

 

[Steve R.]

The point being they can isolate you away from the main server.

When I suggested that isolation approach to the IT staff, I was hit with a barge of no-can-do excuses.

 

[AccessBlaster]

I remember 20 years ago I was able to surf the directory of any server at our organization. No Mas, the new switches went in the firewalls went up and the new server software was installed. Everything changes sooner or later.

 

[The_Doc_Man]

When I faced that problem, I got the government manager involved and told him why we needed a file server for what we were doing. Once he realized that my database's reports were driving a deliverable monthly document, he had a little chat with IT, who suddenly saw the light. Sometimes the solution is to find the person who wants something that you can give him or her, then stand back and watch the fur fly.

 

[isladogs]

@SteveR
Just out of interest, why not use SQL Server Express (free) at home rather than MariaDB?
I've never used the latter. Does it offer any advantages over SQL Server?

 

[cheekybuddha]

@Colin,

MariaDB is a fork of MySQL (created by the original creator of MySQL after it was bought by Oracle).

Originally a drop-in replacement, it has actually forked quite far now.

Why use it over SQLServer express? It's also free, and not crippled like SQLServer Express (number of cores, users, etc, etc ...).

Really, a case of what you're comfortable with.

hth,

d

 

[isladogs]

Why use it over SQLServer express? It's also free, and not crippled like SQLServer Express (number of cores, users, etc, etc ...).
Really, a case of what you're comfortable with.

The reason I asked was that SteveR had used SSE at work so I presumed he was indeed familiar/comfortable with it

Whilst SSE is cut down, I'd hardly describe it as crippled.
With its 10GB size limit its been more than enough for almost all of my clients over a number of years.

 

[Steve R.]

@SteveR
Just out of interest, why not use SQL Server Express (free) at home rather than MariaDB?
I've never used the latter. Does it offer any advantages over SQL Server?

First, I am boycotting (to the extent practical) Microsoft products. Currently I am using Linux (Ubuntu), so I selected an open-source database that would function under Linux.

At the time I set-up my database with MySQL (circa 2012) before migrating to MariaDB; it was my understanding the SQL Server did not function under Linux. A quick search indicates that Microsoft SQL Server became Linux compatible in 2017.

Unfortunately, I do not know enough about SQL Server to compare it to MySQL/MariaDB.