Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> Thanks in advance.
    Wendell

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> 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.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    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

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    What is a SWAG?

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    >>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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

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

    <pre>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
    </pre>

    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.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Scientific Wild A** Guess! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    Thank you - I considered trying to answer that politely and thought better of it! I decided I might be admonished for doing so. <img src=/S/duck.gif border=0 alt=duck width=23 height=23>
    Wendell

  15. #15
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •