How to make Database that is open 24/7 faster and smaller? (1 Viewer)

[Pat Hartman]

Which is why for us, "user" was and still is a 4-letter word.

Quite a while ago before the wide aspect ratio had taken hold as the standard, It was really tough to create forms that would render nicely on most monitors. I found Peter's Stretcher/Shrinker and so installed it. My "favorite" user called me in a panic one day soon after when he had managed to shrink a form to a centimeter square and couldn't figure out what he did. Apparently, he had laid something down on his mouse and that sent the code into a frenzy of shrinking and redrawing.

 

[Cotswold]

The only way to get that level of reclaimable bloat is to have a lot of updating or a lot of insert/delete activity. Leaving a DB open but doing nothing (including no timers running in the background) does not cause bloat. Being in edit mode does not contribute to bloat until you actually commit the edit. Opening multiple copies will dink around with the file locking, but if the copies are open AND IDLE they do not contribute to bloat. When an ACCESS database is doing nothing, it is static in terms of I/O load, data growth, and CPU usage.

Your last paragraph is absolutely correct, though. In my first "real" job, we understood that our best testers were the customers of our products, because their people would do things we couldn't even imagine. Which is why for us, "user" was and still is a 4-letter word.

The annoyance that the users provide is that they get to where they are without the vaguest of idea how they got there. Not only that, it is totally effortless to them.

 

[KitaYama]

The annoyance that the users provide is that they get to where they are without the vaguest of idea how they got there.

It's the hole in your application that allows them to go somewhere they're not supposed to. Or do something that causes errors.
They are not supposed to go through a pattern that you THINK is correct.
As a developer, you MUST put a way in front of them and close other doors.
And yet, you blame them for clicking your buttons, not the numerous holes in your own software.

I think there's something wrong with the way the whole Access developers think. They hate users for doing something that causes errors, they hate the boss because they ask something that's hard to achieve or not logical, they hate IT because they're trying to protect the company against attacks and they think everything else outside is against them. Everything outside is wrong and it's only them who are logical.

Me, as a user, am not supposed to understand or remember how it happend. I only click the buttons you have laid in front of me.
You, as a developer are the one responsible for allowing me to do something wrong. Close unrelated items. Hide unwanted objects. Give me a path to follow and achieve the goal. Don't let me do something that causes a problem.
If I do something stupid, it's not my fault. It's your problem because you've put a maze in front of me and I'm trying to solve the mess you call software.

Edit: by "You" I mean developers not personally you(Cotswold).

 

[The_Doc_Man]

Actually, my "user is a four-letter-word" feeling stems from a time before Access. In fact, even from before Windows.

We used to make control systems with specialized keyboards that could be moved around to accommodate operator comfort. One of our bigger customers came in with his chief operator for one of our "incremental acceptance test" visits. We were passing every test in sight until he said, "Time for the Midnight Special test." Then he placed the keyboard at the edge of the operator's specialized stand and sat on the edge where his butt would touch the keys. Took the system about 8 seconds to crash. Call it an early form of "butt dialing" and it perplexed us totally. We had not taken into account that people might place books on the keyboard and the auto-repeat feature essentially overflowed our buffers. The NEXT version fixed that problem by not executing ANYTHING until we got the "GO" key AND if we got more than (I think in their case it was 40 bytes) the longest legal command from that type of keyboard, we just erased the buffer and let them start over again.

I agree with you that we need to close up as many holes as we can, which is why I often advocate the securing of the database to NEVER show the navigation pane or anything else except the dispatcher forms and the forms & reports launched therefrom. You are right that if we let bad things happen behind the scenes, it is on us as developers. But I still say that users are the best testers most of the time because a user is like a feral cat. It LOVES to play with whatever toys it can find and doesn't care that it might be valuable or frangible.

 

[Cotswold]

It's the hole in your application that allows them to go somewhere they're not supposed to. Or do something that causes errors.
They are not supposed to go through a pattern that you THINK is correct.
As a developer, you MUST put a way in front of them and close other doors.
And yet, you blame them for clicking your buttons, not the numerous holes in your own software.

I think there's something wrong with the way the whole Access developers think. They hate users for doing something that causes errors, they hate the boss because they ask something that's hard to achieve or not logical, they hate IT because they're trying to protect the company against attacks and they think everything else outside is against them. Everything outside is wrong and it's only them who are logical.

Me, as a user, am not supposed to understand or remember how it happend. I only click the buttons you have laid in front of me.
You, as a developer are the one responsible for allowing me to do something wrong. Close unrelated items. Hide unwanted objects. Give me a path to follow and achieve the goal. Don't let me do something that causes a problem.
If I do something stupid, it's not my fault. It's your problem because you've put a maze in front of me and I'm trying to solve the mess you call software.

Edit: by "You" I mean developers not personally you(Cotswold).

Methinks you have taken my response too seriously. It was an attempt at humour regarding those odd situations that occasionally happened in the misty past. Those issues arose when it seemed the least able/competent, who complained the loudest, dropped on what was probably something trivial but often excused as a bug. We've all met them, the sky's fallen in, total disaster brigade.

Any professional developer will need to spend nearly as long testing as creating, otherwise they generate the scenario that you described. Writing any software is one of the least forgiving occupations because everything has to be 100% correct. Not like so many others that can be good enough, or near enough. If you do the job right the hours can be cruel at times.

The difficulty of getting software right is easy to see when you add up the time and money already spent on driverless cars by a bunch of propeller heads. Probably more money than it was to put a man on the moon and it is still a bag of spanners, as well as a can of worms and they are still not fit for purpose.

Edit: nothing personal KitaYama

 

[omarrr128]

I worked on a U.S. Naval Reserve Personnel machine that had to be up pretty much no matter what you did to it. But that is not consistent with any normal operating system. Windows, UNIX, OpenVMS, and a few others reach a moment where you ABSOLUTELY POSITIVELY must drop the system to allow for garbage collection and reclamation. Access "bloat" (the phenomenon where the files grow in size) is one example of the garbage collection problem. Call it a cost of doing business. Even during Operation Desert Shield and later Desert Storm, our systems had built-in down time to allow reboots and restarts.

My weekly down time for my Access security-related operations tracking was that we got everyone to agree on a time that the DB could go down with minimum impact. Oddly, because of night-shift operations being busy due to things happening in other time zones, we picked Noon on Fridays. When I took my systems down, the BE would get backed up, then Compressed & Repaired. If there was a new FE pending for release, I would stage that as well. I had one hour to get everything done. As long as I was assiduous in my testing leading up to the maintenance hour, it was not a problem.

Part of the problem is that both Windows and Access (both Microsoft products) use similar memory allocation and reclamation methods, which are inherently imperfect. What causes the bloat is that when you take out a chunk of memory as a scratchpad in which to work, and something else then does the same thing to an adjacent piece of memory, the release process can be unable to re-merge the released memory chunks, so these disconnected and unusable chunks slowly build up.

For Windows, what happens is that either Windows itself or some program just crashes if you waited too long to reboot and the system scratchpad gets depleted. I.e. you don't get a choice. Though I must say that Win10 is FAR better at longevity than Win6 or Win7 ever were. And let's not even talk about earlier versions, OK? The memories are too painful.

For Access, the bloat keeps going until you deplete system virtual memory because too much of the DB is bloat and it clogs up the Virtual Memory (SWAP & PAGE) file. There, a reboot doesn't help. You need to run the Compact & Repair to force proper memory reclamation. You have yourself documented in your request just how much bloat you reclaim.


It is the nature of the beast that you cannot unless you can do away with DELETE and UPDATE operations altogether. But even then, SELECT queries use some scratchpad memory to organize the records to be returned, which would lead to eventual bloating - just not as often and not as bad.

Even the "Big Boys" need down time to do things like optimize indexes and reallocate data bucket loads. Those were ORACLE terms but the concept is similar for other vendors like SQL Server, SYBASE, INFORMIX, and (now defunct) ShareBase. It's a fact of life that running software gets "dirty" and needs periodic maintenance. If you don't build that into your schedule and MAKE IT STICK, you will have a crashed and unrecoverable database on your hands.

thank you for your in depth reply it has been very insightful!

 

[omarrr128]

The backend is also an access file?

If the application is important and runs 24/7, an active database management system (SQL Server & Co.) should definitely be used here. Such an active system is a service, not a stupid file, and also allows maintenance such as backups during runtime and provides much more security.

this sounds very complicated and i am quite new to access. I also do not have adminstration control over the computers so installing new software will be quite hard if not impossible. i dont think SQL servers will be an option for me

 

[omarrr128]

I always created documentation so that anyone else could look after and use the DBs I made?

i plan to do this but also want to not have to resort to this as much as possible. I want to have the database be as independent as it can be.

 

[omarrr128]

Do you mean you put the information used by hospital staff, who need 24/7 access, into a classic Access file?

yes. It is a booking system for them to log job requests for Hospital porters.

 

[omarrr128]

It is unusual to have these issues with a small database.
I'd look at the users. Are they opening multiple copies on the same PC and leaving them open maybe for days? Or are they switching off the PC without correctly closing one or more copies of the software that they have open? Maybe left open in editing mode for hours or days. Do a little spying around and check out how they are using your software. Maybe write something to check on them. Set deletes to hide, so you can check just how many they are doing and why. Record how long a screen is open in editing mode on a per user basis.
Users can be very resourceful in devising methods of doing something that you would never imagine. Your software should only allow the users to do what you allow them to do. If you don't want them to have multiple copies open stop it from happening.

unfortunately this is very likely part of the problem. I work in the A&E department of a very busy hospital with hundreds of different staff using the database. The database will be open for days non stop and is open on at least 20 different computers at once.

what do you mean by Left open in editing mode? Do you mean the design/layout views? or when a form is dirty?

 

[omarrr128]

I've never heard of any IT process not having downtime and I worked at a hospital for six years. I went into labor, seven weeks early, during a scheduled downtime and had to tell the nurses the day of my ultrasound so they could manually look it up.

Pat's suggestion is probably the best option.

if i worked here full time permanently i could set that up but unfortunately i am leaving to go and study. Not many other staff members will have a clue on how to repair the database and Staff also come and go very often. I am not apart of the IT team as well so they wont help.

 

[omarrr128]

Use optimal programming.
Do you have enough self-criticism to appreciate that something put together that works isn't necessarily designed to work WELL?

definitely. I am not a programmer and am very new to access so I wouldnt be surprised if behind the scenes the VBA code is a mess.

How would i be able to tell and how to improve it?

 

[omarrr128]

This is unacceptable. If the application is critical or even important, they will NEED someone to maintain it. NO system is maintenance free.

They need to keep you on retainer, train someone to run the maintenance routine, or hire your replacement. If they fail to do so, they will need to have a back-up plan for when something goes awry - and it will, probably at the worst possible time.

thank you. I will look into teaching someone who will likely be here long term on how to maintain it.

Does maintaining it mean just compacting and repairing the database from time to time?

 

[Pat Hartman]

You can look at the tool available at www.fmsinc.com that will help with automating the compacting for the BE.

The "admin" needs to understand how to add a new user. I'm assuming you have an automated distribution method. If you don't, you need to set that up now.

You need to clearly define Where the master copy of the FE will be stored as well as any passwords that might be needed.

 

[Cotswold]

thank you. I will look into teaching someone who will likely be here long term on how to maintain it.

Does maintaining it mean just compacting and repairing the database from time to time?

This makes me think that only failure will follow. The forum (as I understand it) will assist in the odd and sods that unusually, for the query-poster, go wrong. Or so often the experienced pointing the less knowledgeable in the right direction with the benefit of their experience. It cannot really be expected to provide application support. Nor would it be practical, as members will never understand your application as well as yourself.

I'm not specifically intending to be negative but could you be out of your depth here? Particularly the statement "Does maintaining it mean just compacting and repairing the database from time to time?" and "I'm not a programmer and new to Access" are alarm bells to me. As a for-instance it is a bit like someone deciding to service their car, or rewire their house without any actual knowledge of how to do it.

 

[omarrr128]

This makes me think that only failure will follow. The forum (as I understand it) will assist in the odd and sods that unusually, for the query-poster, go wrong. Or so often the experienced pointing the less knowledgeable in the right direction. It cannot really be expected to provide application support, nor would it be practical, as members will never understand your application as well as yourself.

I'm not specifically intending to be negative but could you be out of your depth here? Particularly the statement "Does maintaining it mean just compacting and repairing the database from time to time?" is an alarm bell to me.

Well as I am not a veteran to handling an active access database I am not sure what "Maintaining" a database would entail.

I have not supervised one over a lengthy amount of time to know what would need to fixed or "maintained".

Assuming that my database is stable and won't crash then I don't see any other problems occurring apart from bloating.

The term "maintain" is very broad so if you'd like to explain what exactly maintaining a database would be then maybe I would be able to tell or not if I am out of my depth.

 

[Cotswold]

The term "maintain" is very broad so if you'd like to explain what exactly maintaining a database would be then maybe I would be able to tell or not if I am out of my depth.

Maybe I could summarise maintaining a database in 2,000 to 3,000 words but even that could be somewhat esoteric. You are basically asking for years of experience to be summarised into a text or an email. Maybe get hold of a copy of the Access Developers Handbook Volume 1, by Litwin, Getz & Gilbert? It can be obtained second hand for a few pounds.

 

[cheekybuddha]

The database will be open for days non stop and is open on at least 20 different computers at once.

I haven't read through the whole thread, so apologies if you have answered this already, but please can you clarify that each of the 20 machines has its own copy of the front-end which each link to a single backend?

From reading what you have written it could be that every one of the 20 machines is connecting to the same copy of the database file.

If so, that's a very bad idea.

 

[CJ_London]

On the subject of faster - take a look at this thread

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

 

[The_Doc_Man]

The term "maintain" is very broad so if you'd like to explain what exactly maintaining a database would be then maybe I would be able to tell or not if I am out of my depth.

I am reminded of the famous quote, originally attributed as an answer to the question "How much did that yacht cost?" - to which the answer was "If you have to ask, you can't afford it." In response to your question, I would say "If you have to ask, you are definitely out of your depth."

Database maintenance comes in many flavors. The biggest "gotcha" is how to protect and recover data given that Murphy's Law applies strongly to software products: Anything that can go wrong, will go wrong.

Does maintaining it mean just compacting and repairing the database from time to time?

Remove the word "just" as it implies "that's all there is." The answer to your question is "NO." Not even close. Your biggest problem is change. In a "living system" that is still in use, the ONLY thing that stays the same is that things change. Your database addresses and solves a problem that was evaluated at a given point in time. Does that mean that the problem will never change? Not a chance in Hell. The only things that don't change are statues. Dead things. And even a statue can erode and crumble over time.

Ordinary maintenance in the strictest viewpoint is providing regular backups and performing corrective maintenance for bloat, and watching for situations where the database is starting to grow naturally in a non-bloat manner (i.e. real data being kept) so that you can anticipate when it is time to upgrade something or verify that you don't need that upgrade quite yet. In other words, proactive monitoring of what is going on. But even that doesn't cover it at all.

Databases with live users evolve as more facets or even brand-new facets of the problem emerge. What will happen when you get a "database in unrecognized format" error and a compact & repair won't fix it? You say your DB is for a hospital. As national rules and healthcare standards evolve, is that hospital going to try to catch up to that moving target? Will new personnel privacy rules come into play? Will new computer equipment and new versions of the operating system come into play that requires adaptation? Will new staff members (and thus new users) join the hospital? What will happen if equipment fails and you have to move the files to a new base location on a new server or file host? What will happen when someone at the hospital says, "It would be nice if we can start tracking X, Y, and Z" for the auditors?

All I can say is that if this database is truly needed by the hospital and they DON'T immediately replace you with someone who is qualified to take over, it will be a case of the fine old Japanese action known as "hara kiri" or "seppuku" - and probably just as bloody.

I know I sound a bit harsh here, but you asked about the reality of database maintenance. If that scared you, I'm truly sorry - but if I am to do my proper duty to answer your questions, I owe it to you to answer as truthfully as I can and hope I haven't forgotten something.