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

[Poppa Smurf]

Remove the compact on close and give it a test.

 

[GinaWhipp]

Hmm, okay in one of those links I sent earlier it was recommended that you uncheck *Compact on Close*. Since that is still checked I have to wonder what else is still checked, like *Track Autoname Correct* is that checked? I would strongly recommend going thru the items listed in those links as *obvious* as they are there are things in there that can, if left to their devices, slow your database.

 

[InstructionWhich7142]

I'm not sure what I am having trouble explaining, My database is not slow when accessed by a single user!!!

It is only an issue when additional users try to use it simultaneously (this is easy reproducible)

There's ovbiously some bug, probably related to MS server/LDB file access which is causing this

 

[InstructionWhich7142]

sorry that sounded a bit like a rant, I guess it was a bit as this is really frustrating me and I've read about 100 things along the lines of those suggestions trying to google this issue.

I did actually read the "ovbious suggestions" even though most of them were from about 1997 or were just basics of query design etc and have tested with autocorrect on/off, compact on close on/off, with no difference in load time so I put them back on!

 

[GinaWhipp]

No worries, I understand your frustration!

I have created databases that are used by 1 to 50 Users at a time and do not see what you are seeing. When a Client does experience a lag when other Users go in it always turns out to be a Network issue (which I can always guarantee because I follow best practices). But in my early days I *always* had that problem...

This is why I am *harping* a bit on the obvious. If we remove all those from the scenario then it just might be the Network.

Now, those things that you decided to *put back on* turn them back off. Some of them actually cause issues, such as, corruption. Here's my Mantra...

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

Any way you can upload the database? Of course, remove any sensitive information.

 

[MSAccessRookie]

It's set to compact on close,

I just don't understand what changes when other users already have the BE open

Depending on the nature of your Database, Compact on Close is not always a good idea, and could actually be at least a part of your issue. When an Access Query is run, an optimized execution plan is created and saved in the Database to allow the Query to run more efficiently. When a Compact and repair is performed, the Query execution plans are removed and will not be recreated until the next time that the Queries are executed. If the Startup Form has a number of Combo Boxes based on Queries, each of the Queries will need to be reoptimized when the Form is first loaded.

-- Rookie

 

[InstructionWhich7142]

i got annoyed and turned to Wireshark and I've basically found the culprit:

this is with:
no "relinking" so i'm leaving the links there [not going to the lock smith every trip home]
compact on close is disabled
autocorrect tracking is disabled
"no locks" is set

so just performing 2 Dlookups and 1 SQL Insert:


there are 5 separate SMB requests to the laccdb file [edit: there are loads, but the 5 interesting ones:]

each of which take exactly 1 second for the server to reply : SMB Error: STATUS_SHARING_VIOLATION

(dlookups seem to cause 2 each, the SQL update causes 1)

futher testing reveals deleting a linked table causes 3, making a linked table causes 2, all of nearly exactly 1 second, so between all that i can see why it takes over 20 seconds to get going

 

[InstructionWhich7142]

having done a bit of testing, basically with the effect you use in:
Refresh Links/Relink Module with a *Persistent Connected* form

If I open one table from each BE, I get 2 of those 1 second SMB errors, per BE, but thats it, after a non specific delay I can then open as many tables as I like from each BE "instantly"

But that non-specific delay is definitely there, its almost like you can "Surprise" Access by opening 6 or 7 tables and it'll make you wait for each one, but once its gotten used to the idea it'll even let me create links straight away with no delays lol!!!

So maybe I need to consolidate BEs into 1 and work out a way to let it get its affairs in order before I try to do all the re-links - maybe i need a "Loading...." form that links and opens a single persistent table then all my other code can fire....

edit:............or is there a different BE i can use?

 

[GinaWhipp]

Before I answer... cause it could get lengthy... I thought one Access backend but now I see Access and SQL Server? Is that right, cause I missed that...

 

[InstructionWhich7142]

sorry, "1 SQL Insert:" is just a docmd.runsql

I've only got ACCDB back ends at the moment (and I link to FoxPro through ODBC, but that works "ok" considering)

 

[GinaWhipp]

Okay, so I read your replies and the thing that raises a flag for me is...

SMB Error: STATUS_SHARING_VIOLATION

...which translates to...
Sharing violation. A requested open mode conflicts with the sharing mode of an existing file handle.

So, am I thinking correctly when I say that those 2 DLookups and the UPDATE query are running against the linked FoxPro database? Also, I would like to know the permissions on the folder where the BE resides. (Everyone should have FULL permissions.)

Now, in thinking this thru, I'm still not sure why you think you need to relink...

So maybe I need to consolidate BEs into 1 and work out a way to let it get its affairs in order before I try to do all the re-links - maybe i need a "Loading...." form that links and opens a single persistent table then all my other code can fire....

Unless, it because of FoxPro? Because under normal conditions there is no need. Even with the FoxPro you can just refresh.

 

[InstructionWhich7142]

So, am I thinking correctly when I say that those 2 DLookups and the UPDATE query are running against the linked FoxPro database?

No, sorry to have added complexity mentioning FoxPro, none of the "housekeeping" or primary stuff goes near foxpro, foxpro only gets involved with some reports right at the very end, I'm having problems with the pure MS Access startup bit.

Also, I would like to know the permissions on the folder where the BE resides. (Everyone should have FULL permissions.)

just logged on to check, Everyone does have Full permissions

Now, in thinking this thru, I'm still not sure why you think you need to relink...

Unless, it because of FoxPro? Because under normal conditions there is no need. Even with the FoxPro you can just refresh.

I've stopped doing that, or at least stopped actively deleting links, the delays and "STATUS_SHARING_VIOLATION" occur with the DLOOKUPs and INSERTS against the already linked tables

 

[GinaWhipp]

What kind of Server? I'm thinking if we make whatever is creating the error message go away, problem solved. See...
http://support.microsoft.com/kb/2707576/en-us

 

[InstructionWhich7142]

Server 2003 32bit
I'm connecting from a win7 64bit

 

[GinaWhipp]

Well, that's not it... My gut just tells me that error message holds the key but as I have never run into before I have no reference point. Hmm, going to ask around, I'll be back!

 

[InstructionWhich7142]

Thank you! :-D

 

[The_Doc_Man]

OK, let's get something straight here. SMB is the file-sharing protocol. You get sharing errors when you share a file or directory improperly.

If you are doing ODBC to an SQL flavor of server, you have nothing to share because SQL itself owns the files you are accessing. You don't access them directly in that context. SQL is your middleman. You connect through a TCPIP socket somewhere in the port 1300+ range, I forget the exact number at the moment, and you send your SQL in, get your answer records out, no extra files ever get touched by your system or by the file access listener - because that's a different port number.

Therefore, it ain't SQL. However, I could see a helluva bit of contention if at least one of your BE databases is a JET-type file. THAT, you would have to share, and there is where your sharing violation could come into play. Do you have at least one JET back-end, i.e. where the BE file is merely what you got when you split one Access DB into two parts and the table-part did NOT get converted to SQL?

In that case, you might be running into a little something called access minimization, and no, I'm not talking about shrinking a database window. When you access something through a Windows file share, you have two sets of permissions - the native permissions on the folder that always apply to local users, and the permissions on the file-share declaration that publishes the folder to the network. Though they point to the same place, their permissions are independently declared and - here's the minimization - if they are not set consistently, the one that is more restrictive is the one you see. Your permissions are the minimum of the share vs. the native directory.

It has to be something like this because SMB isn't an issue for the SQL BE cases. Totally different protocols.

 

[GinaWhipp]

The_Doc_Man... You are the MAN! I have been trying to find out about this error as this is the first time I have ever run into it. And, I was sure this needs to be fixed as I beleive it is the root of the problem.

You'll always be an MVP in my book!

 

[InstructionWhich7142]

OK, let's get something straight here. SMB is the file-sharing protocol. You get sharing errors when you share a file or directory improperly.

~~~ I could see a helluva bit of contention if at least one of your BE databases is a JET-type file. THAT, you would have to share, and there is where your sharing violation could come into play. Do you have at least one JET back-end, i.e. where the BE file is merely what you got when you split one Access DB into two parts and the table-part did NOT get converted to SQL?

Yes, I do have Jet BEs, I didn't do the automated split, if there is one, I've just created 3 ACCDBs to store tables all my users's local FEs access

In that case, you might be running into a little something called access minimization, and no, I'm not talking about shrinking a database window. When you access something through a Windows file share, you have two sets of permissions - the native permissions on the folder that always apply to local users, and the permissions on the file-share declaration that publishes the folder to the network. Though they point to the same place, their permissions are independently declared and - here's the minimization - if they are not set consistently, the one that is more restrictive is the one you see. Your permissions are the minimum of the share vs. the native directory.

I've just logged in to check, ALL folders have Everyone set to full access in the Security Tab, on the Permissions of the Shared folder the Everyone group is Set to Full access

This doesn't sound like the problem anyway as the SMB Error: "STATUS_SHARING_VIOLATION" does NOT happen for the first user that accesses one of the BEs, ONLY for subsequent users, and specifically ONLY when SMB sends some kind of request to the LACCDB - LCK file, the ACCDBs answer correctly.

 

[GinaWhipp]

You are checking the first part, you need to review the second part...

...you have two sets of permissions - the native permissions on the folder that always apply to local users, and the permissions on the file-share declaration that publishes the folder to the network...