Queries running painfully slow~split database

[sinisterfrog]

Long story short, I am managing our database and have no proper database managing experience.



The accdb file is ~400mb. Im running office 365 so I imagine it is up to date. Computers are beefy enough that this shouldnt be an issue.

I recently split the database. The front ends are on 3 computers. The back end lives on an SSD attached to the main input computer.

Queries are running painfully slow on the networked computers. On the host computer it runs perfectly fine. The queries run much faster after I have repaired and compacted the back end. This lasts for some amount of time, but ultimately slows down to unacceptable speeds.

Any suggestions would be very appreciated.

Also, Is there anyway to see "file size" of each of the tables?

-Matt

[RuralGuy]

There may be some useful suggestions here: http://www.granite.ab.ca/access/performancefaq.htm

[Micron]

Regarding a table size, this is what I've been led to believe:
Add the field size for each field (e.g. Currency = 8 bytes) and multiply by the number of records. This would be an approximation as it doesn't include any overhead. If you add them altogether, you will not have any meaningful information as there are system tables as well, plus overhead for the db, plus any temporary page files or other space that isn't frequently reclaimed. At best you could know which table was bigger than another.

While I always thought not closing a recordset only affected machine memory allocation, I recently read that it also can cause db bloat amongst other "bad" practices such as constantly deleting and recreating tables rather than deleting all records and repopulating. Suggest you research MS Access db bloat. While the info a the link isn't bad, a lot of it looks out of date to me.

You could always investigate compacting on close when the db gets to a predetermined size and there is only 1 user who has ties to the back end. I would not do this every time it closes.

[CJ_London]

The back end lives on an SSD attached to the main input computer.

this is probably why - although the SSD should be fast, it may be the connection isn't - not to mention the user network connection to the main computer. Have you tried putting the backend on the main computer? why put it on a SSD?

Other thing to consider is indexing - are your tables properly indexed? If not it can have a massive impact on performance.

[Shadow9449]

From my experience with linked tables, the biggest speed boost is keeping a persistent open recordset (first item in RuralGuy's link).

[orange]

you could add this and this to RuralGuy's link.

[sinisterfrog]

The BE is on the C drive (ssd) on the main computer which runs perfectly fine even with multiple users.

I just tried the open recordset and that appears to have solved the issue ( or at least in the 4 hours i've been testing it).

[sinisterfrog]

Sigh.. So the problem continues.

So I have made a hidden form that opens when the DB opens and keeps a consistent connection (?) to the back end. The LACCDB file is created the moment I open the database on any computer and does not appear to be deleted at any point.

Should the front end be creating a LACCDB file as well?

The networked computers see a significant drop in performance when any of the computers edit/add any records and that performance decrease lasts until their version of the front end is closed.

If I open the front end on any of networked computers and I am the only user, than queries/forms load sufficiently fast enough.


Any other thoughts and suggestions?

-Matt

[CJ_London]

Should the front end be creating a LACCDB file as well?

yes but that will be on the users machine

Any other thoughts and suggestions?

Otherwise could be back to your db design - is it designed for multi user use? does it limit the amount of network traffic? As mentioned before are you using indexing?

[sinisterfrog]

The database, to my knowledge, was not designed with multi-user in mind. It does not limit network traffic. I will index more fields. Could lack of indexing explain the slow down after someone else edits/adds a field?

I just find it odd that I can run the DB fine until another user makes a change.

[CJ_London]

it could do - indexing has an overhead in updating indexes when record is changed - conversely lack of indexing means it takes longer to retrieve records. But you can over index - the basic requirement is to index any field which is used in criteria, for ordering or filtering. The exception is a field with few different values (e.g. Boolean). See post #1 on this thread for an illustration https://www.access-programmers.co.uk...ndexing&page=4

I recently rebuilt a clients db which ran very slow over the network. I added indexing, removed others, made sure all tables had a PK and the limited the number of records to be returned i.e. rather than having a form based on an entire table and then subsequently filtering, only records the user was actually interested in are returned - typically 10 or 15 out of a million records. It now takes about a second to refresh the form for new data (previously 2-3 mins). report generation takes a couple of minutes rather than the hour it took before. It also means a user is only potentially locking 10-15 records rather than the whole table.

[Micron]

The back end lives on an SSD attached to the main input computer.

This sounds like the ssd is external, and is probably usb connected. To me, things point to a slow network because the db runs fine locally, but not from any of the other pc's. If this is set up as a home type of network, that could be the issue. Worse, these types of networks are often wifi based, and you should not run an Access db over wifi. If this is a corporate type of network (e.g. wired and using a dedicated Windows server or terminal services architecture) then my suggestion would be to install the fe and be temporarily on each of the 2 problem machines to see if their performance improves. If not, then to me it would indicate a hardware problem since the network/usb/ssd/wifi (whatever) elements would be eliminated. If you do, be sure to re-link the be tables to each local fe. Any data you modify during this test would not be synced, so don't consider any testing activity to be useful to the production data side of things.

[sinisterfrog]

My (very limited) understanding is that when you query an access table, it essentially builds the entire table and then filters what you want... When you limit the number of records, does first only pull the amount of records you want and then filter the results from that?

Is any of that correct?
-Matt

[Micron]

it essentially builds the entire table and then filters what you want

From my limited reading on Jet or ACE (depending on your version of Access), pretty sure that's a no. For Access tables, when a select query has been optimized (actually, it goes through several processes before acting on a table(s)) it returns only the records that match the criteria. To return the whole table then filter it wouldn't make sense, especially if you're only wanting records from one field. That would be even worse if multiple tables were involved in a single select query. I have a Wiki link that might start you out on a research path if you're so inclined
https://en.wikipedia.org/wiki/Micros...atabase_Engine