Split Database Performance Issue (A2k SR1, Win 2000 SP2)

Topic

New Reply

We’ve recently deployed a database at a client that is exhibiting performance problems when a second user opens the database, and we’ve been able to duplicate it in our own test environment. The database is split with a front-end on the workstation, and a Jet backend on an NT4 server. As long as a single user is working in the database, the response time is subsecond for pulling up forms, etc. When a second (or third or fourth) user joins the fray, the response time suddenly deteriorates to 5 or 10 seconds. We are using Access security to track the user name, with the system.mdw file located on the workstation so that it is not being shared. We upgraded workstations to the latest SP, and we installed the Jet 4.0 SP6, and neither has had any affect. We do this routinely using a SQL Server back-end, but it has been some time since we have used a 2000 Jet back-end with multiple users.

Has anyone else seen this kind of behavior, or have suggestions in terms of areas to look at – we seem to be pretty much mystified? Thanks in advance.

Reply | Quote

Replies

The only time I’ve seen something like this myself, it was the result of a mixture of things, including a chattering network card, a bad length of wiring leading into the server, and the hubs we were using. I believe they replaced the hubs with routers and the problem disappeared. In any event, the troubleshooting was ugly, time-consuming, and expen$ive.

Since you can recreate it in your own environment, it boils down to code. I certainly have seen performance drop with each additional user, but not on that scale. Are you maintaining an open link to the back end? If not, you might try it and see if that makes a difference. If you’re using linked tables, declaring a global database object and populating it in the startup (remember to destroy it before the database closes) will reduce the time required to initialize other database objects/connections in the application because the current one will be reused.

I assume you’ve decomplied and recompiled the application? One thing we’ve discovered is that a code library, whether an MDE or some other database format, that has a reference in the database, needs to be in a fixed location or the application will NOT be compiled on the target machines, and that definitely affects its performance.

Reply | Quote

Yes, we’ve seen some weird things with network problems, but since we can replicate it at will this is starting to look like some sort of fundamental problem. I say that because the recreation we did was with workstation databases that had no objects other than the linked tables, which are permanent connections.

The actual situation we tested and timed involved first simply opening the table in the back-end which gave us a benchmark. Then we tried opening the table from the linked database on each workstation, but one at a time, which gave us a bit slower response than going directly to the server with consistent times from each workstation. We then opened the database on one of the workstations, but with no table open, and then went to the other workstation and opened the front-end there and timed the response time to open the table, and the response time went up by a factor of at least 4 for both cases. So as I indicated, we are “buffaloed” – a little Colorado humor if you follow college athletics. BTW, at least one other person has reported a similar situation that was puzzling them in a different thread.

Reply | Quote

Yes Wendell, we also had this problem, but on Access 97 SR2 and on NT4. We had about 6 users with both the FE and BE on the Server (not on the workstations). We found that my response time was normal (very good) as well as all the others.

However, one by one, they lapsed until we had the situation that it would take 2-3 minutes (or more) to just open a form. Mine stayed fast as well as another 2 people, however the other 3 went real slow.

One of them had their PC blow up, so they took it away to rebuild it, while another person who had a fast PC (on the response side of things) left the company, so the guy who had his PC “blow up” took the other person’s fast PC and with it the fast response.

What all this tells me is that it did not seem to be a network problem, or did not seem to be.
What we found was that one by one of a period of a few weeks was that the others speeded up (now that just blew our minds, I did not know if I was in fairy land or not).

A couple of months after all of this we converted from Access 97 to Access2000 and there were no more problems, however I’m sure that was not attributed to the upgrade.

Hope this ….. (I’m sure all this does not help one little bit).

Pat

Reply | Quote

Just as a SWAG, have you remembered to turn off subdatasheets? I’ve seen linked tables open *very* slowly on a single workstation with the backend on the same machine when subdatasheets were enabled on some of the tables. Since the database is split, you have to turn them off in both front and back ends.

Reply | Quote

What is a SWAG?

Reply | Quote

Scientific Wild A** Guess!

Reply | Quote

Thank you – I considered trying to answer that politely and thought better of it! I decided I might be admonished for doing so.

Reply | Quote

perfectly acceptable, since it’s right in the middle of Gen 16:12 (RSV)

Reply | Quote

that would be *Scientific* wild-ass guess, as opposed to a mere supposition

Reply | Quote

Re subdatasheets: I think this question has risen before, and I suspect the answer is NO: Is there any way to reset the Subdatasheet Name property from “[Auto]” to “[None]” for linked tables that are NOT A2K (or higher) tables?? E.g., linked ODBC tables that are native FoxPro tables. Even on same machine (let alone on network) these are SLOW to open, and I think the stupid subdatasheet property is a primary culprit. Since subdatasheet apparently falls into the “This property cannot be modified in linked tables” category, if the base table is not an A2K table, there’s no way to turn it off!! (I think this is one reason why performance with A2K front end linked to A97 back end is so lousy.) I don’t know what certified genius decided to make the default “Auto” rather than “None”, if there is a way to disable this idiotic “feature” in A2K for non-A2K linked tables I’d like to know!! (What’s interesting, in design view for a linked table you can “temporarily” save table with subdatasheet property set to “None”, and the table will open w/o the usual delay, but the setting does not “persist” after you close table and then open again.) I don’t suppose this wretched so-called “feature” has been fixed in A2002?

Re performance in general on network with Access back end, it has been my experience that if even ONE other user has back end tables open for updating, performance is significantly degraded, however “optimistic” the record-locking settings are and no matter how “optimized” your application may be. I always assumed the only “fix” is upgrade to something like SQL Server.

Reply | Quote

To answer my own question, further investigation revealed you can set Subdatasheet property to “NONE” in code. Example:

Public Sub SetSubdatasheetProperty()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim prop As DAO.Property
    Dim strErrMsg As String
    
    Set db = CurrentDb
        
    For Each tbl In db.TableDefs
        If tbl.Attributes = 536870912 Then  'ODBC Linked Visual FoxPro table
            tbl.Properties("SubdatasheetName") = "[None]"
        End If
    Next tbl
    Beep
    MsgBox "Subdatasheet property set to [NONE].", vbInformation, "TABLES UPDATED"
    
Exit_Sub:
    Set db = Nothing
    Set tbl = Nothing
    Set prop = Nothing
    Exit Sub
Err_Handler:
    If Err = 3270 Then  'Property not found
        Set prop = tbl.CreateProperty("SubdatasheetName", dbText, "[None]")
        tbl.Properties.Append prop
        Resume
    Else
        strErrMsg = "Error No " & Err.Number & ": " & Err.Description
        Beep
        MsgBox strErrMsg, vbExclamation, "SUBDATASHEET PROPERTY ERROR MESSAGE"
        Resume Exit_Sub
    End If

End Sub
 

This can be modified to cycle thru all tables, not just linked tables, to save you hassle of having to turn off subdatasheets “manually” in a new project. After running this, performance with linked ODBC tables improved significantly.

Reply | Quote

Subdatasheets are alive and annoying in AXP as well. I just cycle through the tables in code and turn them all off. With linked Jet tables, I turn them off in the back end as well.

When I design apps which will be used by multiple users simultaneously, I make sure the design is *highly* relational and I build the interface so that a user can edit a piece of data, say Address, without locking up the user who is entering a telephone number, which means lots of subforms and popups. In all cases, I use No Locks and wrap the updates in transactions so that the edit can be applied at the last possible minute. Without the transaction, the edit lock is applied as soon as the edit method is called, not when the record is actually updated. I’ve also had problems with operators who brought up a form in edit mode and then went off for a coffee break, leaving a lock to drive everyone else crazy.

Reply | Quote

The only use I ever found for subdatasheets is when designing new project they may come in handy to test whether your relationships between tables are set up properly. Otherwise they are useless.

I always use “No Locks” settings for queries, forms & reports. What I’m not sure about is, if a user for whatever reason changes the default record locking settings on his or her machine (Options/Advanced) to setting other than No Locks, or disables record-level locking, then logs onto the shared network database and opens record for editing, will this user’s “pessimistic” settings bog down the program for everyone else who’s logged in?? If this is true is it advisable to use code in startup routine to reset users’ record locking settings if necessary?? Help files aren’t much help on this issue.

Where I work they just don’t take coffee breaks with records open, they take lunch breaks, and lengthy ones at that. Those posts concerning a “time out” function to automatically close form (or database) after a specified period of inactivity are starting to sound like a good idea….

Reply | Quote

Good thought – I hadn’t remembered it, but my partner did. However I’m not sure he turned them off in both the FE and BE. Will check. Thanks.

Reply | Quote

Is it possible that the locking option chosen for some table is overly optimistic or pessimistic?

I ask from my inexperienced-&-hence-clueless-with-databases state.

Reply | Quote

>>One thing we’ve discovered is that a code library, whether an MDE or some other database format, that has a reference in the database, needs to be in a fixed location or the application will NOT be compiled on the target machines, and that definitely affects its performance.<<

I'm not sure I understand what you mean by this?

Reply | Quote

We have an MDE code library that is referenced in our applications. We’ve discovered that if we install the MDE to the application folder when the setup runs, then the database itself will be partially uncompiled (in A97 at least–I haven’t tested it in later versions). If we install the MDE to a fixed location, that is the same location is was in on the machine that created the setup, the app is still in a compiled state when the setup is complete. We’ve experienced this on all versions of Windows from 9x to XP.

Reply | Quote

What happens if you establish a reference to the MDE database when the primary database opens?

Reply | Quote

I believe the same thing … the database will not be properly compiled and its performance will suffer. I haven’t tested that since we distribute a commercial, runtime application.

Reply | Quote

I was looking at ways to best protect a distributed Access database. Obviously, an MDE is best way, except it has some shortcomings, like not being able to resolve reference problems. Mike Groh (Access Advisor Magazine) had suggested moving all code to an MDE that was referenced from main MDB database. The MDB could be protect by Access security, but even if this was broken, the MDE would still be “secure”. So far, I’ve not need to implement this solution, but your comments have given me pause.

For one thing, I don’t know what problems would be created for how I usually hande network situations. I usually put a copy of the frontend on the server, linking all tables via UNC designations to backend. Then, I have a .bat file in each workstation’s startup folder that copies the frontend to the local drive. The workstations then don’t even have to relink. But then what about this reference to the MDE? Obviously, I’d like it to be on the local drive also.

Reply | Quote

We haven’t played around with that kind of setup, so I’m not sure what the effect would be … except that it would at least partially decompile the front end.

Our network installations are made with the front end and MDE library on the local drive but we use a setup created with Wise installer, which puts all the necessary libraries in place on each target machine and insures that the MDE will be in the expected location. The MDE really has to be on the local drive unless you’re using a Citrix server or MTS. The performance would be miserable otherwise.

Reply | Quote

Is your frontend an MDB file? And how do you handle updates (that is, new forms/reports, changes to existings ones, etc.)?

Reply | Quote

The front end is an mdb and we have a rather elaborate, table-driven method of updating both front ends and back end data structures. Since the code is copyrighted, I can’t share it with you, but it involves front end “template” tables for new objects and a table that holds information on actual design changes to be made to the database structures, with a table in the backend that logs changes made in order to avoid unnecessary activity every time the user changes the link to another backend database.

Reply | Quote

Wendell,
….. Hello again, and on a similar subject! Remember our talk about Acc97 & Win2000, and my recent msg to the forum, the solution to the problem that Charlotte ID’d. However, remember at that time that you and I felt we had experienced _some_ problem behavior w/ Acc97 (mine is SR-2) and Win2000 (mine is SvcPk 2), while Charlotte had not experienced any problems w/ the combination. I was still having some frustration, and, I returned w/ the express purpose of entering a msg to Charlotte, listing some of the problems I’ve had. First, though, I scanned threads to see if anyone had any problems similar to mine, and found your thread — for a later version of Access, though. Also, my problems may not be related to this current discussion, but I felt that it might perhaps.
…… So the rest of this msg is to Charlotte: Just for starters, Charlotte, I pop up my Active Directory “console” window (uh, I’m a lightweight on our network — if you want more explanation here) and highlight an Access 97, 1.5 + meg database somewhere out on the network, and click on “properties.” It takes a good 4 minutes for the properties to respond. Next, I click on the “securities” tab, which takes another minute to respond. Next, I close properties, rename the .mdb to .mdb.old, and pop up properties, and they pop up lightening fast — and, are lightening fast if I pop up a similar-sized or larger file’s properties. What gives? This happens all over the network — so, you could think the network problem, if there is one, resides locally . However, that wouldn’t explain the lightening speed for objects _not_ named .mdb.
…. Secondly, the experience people have had w/ the 2nd person opening forms, I have had w/ opening properties (I assign securities on some of these things, and, since we are in the process of going over to 2000, I have been doing a lot of this for a while). If I open properties once for an .mdb, it is slow. If I open them again, it is _verrrrry_ slow. If I open them again, they might not respond back for an hour. If someone else opens properties while I’m looking at it, the slowdown in response also occurs. Something is going on besides just loading, linking, locking, and code.
….. Also, linking tables while on Win2000 is excrutiatingly slow just to begin w/. I don’t do a fell-swoop re-link anymore, I link one table, then the next, then the next. Really, though, I don’t link on a Win2000 desktop — I do all my linking on a 95 machine (which, btw, is still pointing to tables on the 2000 network, but the network doesn’t seem to be the issue if your machine is not Win2000.
…… Just thought I’d add my 2 cent-vent, just for you all’s edification, consideration, or mystification. Or, maybe one of you might have an idea — and, yes, it might involve the network, or Active Directory, or _something_ that I am not educated on.
thx
Pat

Reply | Quote

I’ve noticed the long delay in bringing up shortcut menus and property windows in the Windows Explorer on my machine at work, which is networked, but not on my Win2k machine at home, which is not. In both cases, it the files may be local. I’m not sure if you’re talking about Active Desktop, but if by Active Directory console you mean the Windows Explorer, then this doesn’t have anything to do with Access that I’ve seen. In fact, I’ve experienced it at work just getting a list of folders to select from. It may very well have something to do with whether you have all that nasty HTML stuff turned on in your explorer windows. I set mine for Classic style and lose all that overhead.

4 minutes is extreme and you really need to talk to your network administrator. Keep i mind, though, that the dialog you actually see depends on the file extension that Windows recognizes, so you will see a different dialog if you rename the file to something.old than you would if it were named something.mdb–regardless of what the file actually contains.

I’m not sure what you mean about opening properties for the database. Are you talking about bringing up a properties dialog from the desktop? What kind of server are you running? What you see happening in the Windows desktop or explorer windows isn’t an Access phenomenon that I’ve seen, it’s Windows/network and you need to talk to your network people. In fact, the more you describe your problems, the more network-related they sound. I have no trouble with relinking on any of the Win2k machines I work on. You have something seriously wrong with either your installation of Windows or something very peculiar in your databases.

Reply | Quote

Charlotte,

Sorry it took so long to get back to you — the msg has been sitting half-finished on my desktop for a few days.

> I’m not sure if you’re talking about Active Desktop, but if by Active Directory console you mean the Windows Explorer

_Probably_ simply the Explorer (or is it “Active Directory” or Explorer _with_ Active Directory? — I hate to expose the holes in my knowledge like this — I’m in “user” mode right now w/ these pieces — just using what I need to know to get around). I have the ability to set/view some network securities for certain folders and files.

> whether you have all that nasty HTML stuff turned on in your explorer windows. I set mine for Classic style
None of that nasty stuff! “Don’t give me beauty, give me speed.”

> 4 minutes is extreme and you really need to talk to your network administrator.

Yes, but I have to tell you — the next day, I “propertied” one over and over again, and it took less than a minute each time. Of course, larger files w/ varying extensions (“sav”, “150”, “old”) popped up immediately, from the same subdirectory on the network. So, — hmmm. I don’t know.

> Keep i mind, though, that the dialog you actually see depends on the file extension that Windows recognizes
It seems to be very consistently _only_ Access databases that have the slow-down — although, as I mentioned just previous to this, it’s not always be excrutiatingly slow — I wonder if Windows is not recognizing the .mdb format on our Win2000 network? Would that be possible?

> In fact, the more you describe your problems, the more network-related they sound. I have no trouble with relinking on any of the Win2k machines I work on. You have something seriously wrong with either your installation of Windows or something very peculiar in your databases.

It is possible that our Windows / network setups are different (we currently have dual login, since we are still moving from our previous network). The databases come from all different backgrounds . I guess what I’ll do is spend a little time documenting the response times, and bring it to the network people. I vary between thinking it’s a network issue and not, because I only see this behavior on Access databases — come to think of it, though, I haven’t run properties on any other MS products like large spreadsheets — focused on Access as I was. hmmm..
Well, thanks for allowing me to vent, and giving me some suggestions. Sorry once again for not responding sooner. I’ll get back to the forum if I can discover anything!

thx
Pat

Reply | Quote

[indent]

---

the next day, I “propertied” one over and over again, and it took less than a minute each time.

---

[/indent]This alone suggests the problem is network/traffic related. I often saw something similar in my last job, which was in a govenment office where NT workstations were running over Novell.

Reply | Quote

Wendell, did you get to the bottom of this one?
Pat

Reply | Quote