Question Split Database sometimes VERY slow (more than 1 user?)

[InstructionWhich7142]

I wish I could explain this better somehow, the performance when already open is not the problem, its the performance when first opening the whole database, and infact MS Access, that is the problem for us, how does a persistent link help that? surely it can't persist when MS Access is closed?

To use the example given, you go for more than one shopping trip in your life and every trip you forget the code when you get home and it takes ages to call the locksmith/explain why you are breaking into your own house, after that drama you then carry the rest of the groceries in with no door issues.

 

[GinaWhipp]

@Tom

OMG... yep I think I will add that just to drive the point home!

Too hot to cook... take out!

 

[GinaWhipp]

Keeping a persistent connection does not help after the database is closed, this is for performance while the database is open. And, yes, we understand that it is the initial opening that is the problem.

 

[tgw7078]

I wish I could explain this better somehow, the performance when already open is not the problem, its the performance when first opening the whole database, and infact MS Access, that is the problem for us, how does a persistent link help that? surely it can't persist when MS Access is closed?

Yes, we understand the main complaint is when subsequent users attempt to open the application, after the first user has opened the application. Trust me, this is abundently clear.

A persistent connection will allow faster relinking, AND generally faster performance while using the application; the benefit is not limited to just faster performance while using the application. You've already told us that you have extensive relinking going on during the startup procedure, so creating persistent connections to all BE databases *before* attempting any relinking should help with the speed of all relinking operations. To repeat: Regardless of your specific issue, I highly recommend that you implement persistent connections to all BE JET databases, for your Access applications.


Tom

 

[sark666]

Hello, sorry to chime in but I have a similar issue. The be is stored on a remote server. Each user has their own front end. There's only 3 users. I tried the persistent connection trick but it actually resulted in worse performance.

Here's what I experience without the persistent connection trick:

This database is built to record absences, without the persistent connection, an ldb file is only created when a user goes into the record absences form, not the main menu.

1st user logs in and goes to record absences, ldb file is now created, it's fast.
2nd user logs in and goes to record absences, it's now slow for both.
2nd user logs out, it's still slow for the first user. (why??)

1st user goes to the main menu, (and the ldb file is deleted) and reenters the record absences form, it's fast again as if they logged out/in.

With persistent connections it's slow as if they are multiple users logged in even though there is only 1.

File permissions look good. I even tested this with two machines at my work and I have full control over this network share but yet the same performance issue remote users are experiencing. Disabled auto-connect, backend db is at root, db names are <= 8 characters.

So in desperation I was looking for a way to always delete the ldb file. If a user is idle on a form and not doing anything I want it deleted. This goes against everyone elses recommendation but I'm desperate to try something.

If that's not possible could anyone see why a persistent connection would cause a performance degradation with just 1 user? So the hidden form creates the ldb but when a user opens a form that would normally create the ldb, does a subsequent write happen to the ldb file even though it's the same user? I'm wondering if a second write attempt to the ldb file is causing my issue, that's why I want it gone/recreated after each query etc/

 

[Simon_MT]

Comparing the performance of a WAN based and a LAN based environment is like comparing apples with pears unless a LAN based Terminal Server is involved.

The point of persistency is still pertinent however the persistent file should be a table with few records I use the companies table.

Forgive me but this is a long thread and I have only picked up the gist of situation. I would suggest that a little experimentation but be required to analyse the performance.

Try persistency with a BE dummy table one record does not have any sensible information in the table. If more than one Access BE create the table in each with the same record..

Link the tables in the FE and use the and create a query in the FE with a join to the dummy files and again buld a Form with the persistency query and see what happens when multiple users try to login.

If this rudimentary structure is still slow let us know.

Essentially you start with a table of one record and built up from a simple baseline and then see what happens when incorporate more files including the ODBC files and functionality.

Link the Access tables without any ODBC files and populate the FE with a Form that uses the the Query with the persistent files.

I have been in situations with impatient users and through a process of elimination like location of the BE on a file server, remote access (run like a pig) until I used a Terminal Server, looking at the Subdatasheet Property on all the tables and always filtering records on Forms to avoid full table reads and then apply whatever filtering was required. By doing the filtering first - record collection was a lot faster. Even Indexing descriptors from a Foreign Table helps performance in some cases - it maybe a case of stuck it and see what happens.

Performance can be a pain but can be resolved one way or another first you have to find the real cause because we all understand the lack of responsiveness but it sometimes hard to identify WHY!

Simon

 

[Rx_]

Just going to recommend something that worked for me on my Citrix server.
Had these really strange delays that couldn't be explained.
Bob Larson told me to create a new blank DB, import all my objects from the production DB into the blank DB. It is the same process most of us use when we update to a new version of MS Access. This process also seems to weigh heavy on the re-index.

The fist thing noticed is how the new DB is really compact where the "compact and repair" leaves off. This made a delay problem I had just go away.
This is in the category: "It couldn't hurt"

This is a very interesting thread. It would be great to see a Word document with the key steps posted in the Tip section. There are some really great troubleshooting ideas here.

 

[tgw7078]

Hello, sorry to chime in but I have a similar issue. The be is stored on a remote server. Each user has their own front end. There's only 3 users. I tried the persistent connection trick but it actually resulted in worse performance.

Hi SARK666,
Sorry, I just saw your reply, when I decided to return to this thread. For whatever reason, I did not receive a notification email when you, Simon or Rx posted back.

I have never witnessed a persistent connection causing a degradation in performance. How exactly did you implement your persistent connection attempt? Please be specific, explaining in detail.

Also, by "remote server", are we talking WAN (Wide Area Network)? If so, JET was simply not designed to be used in this environment; if this is what you have, then quite frankly you are risking serious corruption to your data. See this article, by Access MVP Albert Kallal, for more details:

Using a wan with ms-access? How fast, how far?
http://www.kallal.ca/Wan/Wans.html

Here's what I experience without the persistent connection trick:

This database is built to record absences, without the persistent connection, an ldb file is only created when a user goes into the record absences form, not the main menu.

This behavior is consistent with an unbound main menu form. Does your main menu have a Recordsource (ie. bound form), or no Recordsource (unbound form)?

1st user logs in and goes to record absences, ldb file is now created, it's fast.
2nd user logs in and goes to record absences, it's now slow for both.
2nd user logs out, it's still slow for the first user. (why??)

Don't really know offhand, but one of the first things worth checking is that all users have the same service packs installed, for Windows, Office and the JET database engine. To one copy of the FE application file: Delete all linked tables, compact the FE application (hold down the <shift> key to bypass any startup routines, and then re-create the linked tables from scratch (instead of simply refreshing them). Also, make sure any code compiles without errors. Then distribute this FE file, with the newly linked tables, to your other users.

... Disabled auto-connect, backend db is at root, db names are <= 8 characters.

Did you mean Disabled Name Auto Correct?

So in desperation I was looking for a way to always delete the ldb file.

I think you are barking up the wrong tree. This simply should not be necessary. I do not advise attempting to delete the locking database file, unless it remains after the last user has closed the application. If that is the case, this signals possible corruption of your database.

....does a subsequent write happen to the ldb file even though it's the same user? I'm wondering if a second write attempt to the ldb file is causing my issue, that's why I want it gone/recreated after each query etc/

Really doubtful. You might (?) get additional writes, from the same user, as they edit records. After all, the purpose of the Locking Database file is to track which user is editing which record, so that two users don't step on each other editing the same record.

 

[tgw7078]

...create a new blank DB, import all my objects from the production DB into the blank DB....

The fist thing noticed is how the new DB is really compact where the "compact and repair" leaves off. This made a delay problem I had just go away.
This is in the category: "It couldn't hurt".

I believe this observation of reduced file size is due to the format of your JET database. Is it in the Access 2002-2003 file format? See this KB article:

Database bloat is not stopped by compacting database with Access 2002 format
http://support.microsoft.com/?id=810415


Note also that the KB article states "This article applies only to a Microsoft Access database (.mdb)." I'm not so sure I believe that, since the .accdb format is based very heavily on the .mdb format. Microsoft would have had to "undo" the changes they made between Access 2000 and Access 2002, which is where this issue first cropped up, in order for that statement to be true. My guess is that they simply have not revisited this article, since Access 2007 was released.

 

[InstructionWhich7142]

Turn off the sharing violation notification delay
You can turn off the sharing violation notification delay to improve file server performance. To do this, follow these steps on the file server that stores the Access or Jet database engine-based program database.

Some Feedback:

The above worked a treat, performance is "almost" instant, probably 2-3 seconds, so 10 times quicker,

I've not had any time to tweak code but I plan to put in a kind of Prefetch form that does the previously mentioned persistent links to tables when the database first opens which should speed it up even more!

Thanks for all your helps