Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Back-end Performance (2003 SP2)

    I am setting up a split database (back-end / front-end). During development, I had both on my local computer and performance was just fine. I have now moved the back-end out to a LAN server and re-linked the tables (to the new location). The performance has degraded significantly. I notice this particularly when loading forms that need back-end tables for the form's Record Source. What loaded in several seconds now takes 45 -60 seconds. Once the form is open, performance is not too bad, although a bit sluggish. The back-end database is currently very small -- about 1.5 MB. This database is set up with user/group level security (the mdw file is on the server) and I access the back-end tables with "Run with Owner's Permissions" queries. The network server seems to perform well for "normal" traffic (a 12 MB file takes about 12 seconds to be copied over).

    Are there some "usual suspects" I should look for or be aware of that may improve the performance in this environment?

    Thanks (in advance) for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back-end Performance (2003 SP2)

    1) Make sure that the anti-virus program on the local computer(s) don't scan network drives (it's OK if the network servers do)

    2) Try creating a persistent connection between frontend and backend. See for example <post#=539,786>post 539,786</post#> and the thread starting at <post#=356,901>post 356,901</post#>.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back-end Performance (2003 SP2)

    Thanks for your suggestions (and pointing me to others'), Hans.

    Indeed, the virus scanner was set to scan network drives. I turned this off for the backend file. The autocorrect was on for both the front- and back-end. I turned these off. And subdatasheet view was set to [Auto] for all tables (linked and unlinked) in the front-end and all the tables in the back-end. I changed these all to [None]. I do have a persistent form (startup "spash" form that set to not visible after a few seconds) that I had been using to log the session start and stop time (I capture the stop time in the form's unload event). However, this form was not bound to any back-end table. I have now innocuously bound it to one of those tables (via the OWOP query). Needless to say, I expected a dramatic improvement (all the suspects were guilty!). Well, it is improved, I think, but still slower than it seems it ought to be -- about 30 seconds to load the form mentioned in my original post.

    Sigh.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back-end Performance (2003 SP2)

    The rest might be due to the relatively slow network: 12 MB in 12 seconds works out to 8 Mbps (megabits per second), so perhaps you have a 10 Mbps NIC. 100 Mbps is not unusual nowadays

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

    Re: Back-end Performance (2003 SP2)

    After having done all you've done, slow performance for opening forms can usually be tied to large recordsets behind forms and with having alot of subforms linked to the mainform. However, these usually only cause problems when you have large databases, with tens of thousands of records and more. If you are having problems with such a small database, I'd strongly suspect the network connection. The frontend is still local, isn't it?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back-end Performance (2003 SP2)

    Check out the thread beginning at <post#=544,415>post 544,415</post#>. I had a similar problem a few months ago and unbinding the forms made a huge difference for laoding from about 45 seconds to about 2.

    If you have several users on simultaneously (this database regularly has 8 people with it open) consider logging them off when the database is idle for a set period. In my instance, I discovered that the workers were essentially leaving the database running all day even if they were away from their desks for hours at a time. They are now logged out after idle time of 30 minutes and the last time I was in I noticed much quicker response times for everything when I was working on the DB.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back-end Performance (2003 SP2)

    Mark,

    Yes, the front end is still local. I may try putting in some time stamps into the Form_Open and Form_Load events at various stages to try and localize where the delay is coming from (or to see if it's all before the events even start processing).

    Also, for this database, I don't yet have any global relationships defined (the "Relationships" window is blank). Would adding these likely buy me anything in this regard?

    Thanks.

  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: Back-end Performance (2003 SP2)

    The relationships should all be defined in the backend. It is a good idea, as it helps protect the integrity of the data, but I don't think it would affect performance. How many subforms on this form? That often is the cause of performance issues.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back-end Performance (2003 SP2)

    Mark,

    I have four subforms defined.

    I've delved into this a bit deeper by time-stamping various points in the code that executes during the Open, Load, and Current events when this form is opened. I found a real time hog in one routine where I was determining the status of each record based on various date fields that had values or didn't have values, counting subordinate records in a linked table, etc. The way I was doing this (shame on me!) was with a user defined function in an update query. That function actually opened a recordset, then found the record, then determined the status (it was a little more complicated than this, but this is the gist of it). So, (gasp) the update query was opening and closing the back-end table once (at least) for every record it was creating. Since I didn't see much of a performance issue when the back-end was local, it didn't occur to me to see how efficient (or inefficient!) this code was. Now I've revamped the code so that the back-end table only has to be opened once. When the back-end is local, this process (the old way) took about 15 seconds (slower than I would have guessed); when on the network it took about 60 seconds. With the revamped code, it's about 0.5 seconds on the network!

    The whole form opening process still takes 5-10 seconds which, for the time being, is acceptable for me, but it would probably behoove me to scour the system for other similar inefficiencies. I may also try unbinding the record sources for the form and its subforms per your guidance to see how much better it gets.

    Thanks.

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

    Re: Back-end Performance (2003 SP2)

    You don't have to create an unbound mainform. You just need to reduce the number of records in the recordset behind the form. See this article Create Powerful Bound Forms. As for the subforms, instead of using the Master/Child links in the subform control (which means each subform is requeried whenever a new mainform record is displayed, even if the subform is not currently visible), just put a reference to the linking control on the mainform in the query behind the subform.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back-end Performance (2003 SP2)

    Mark,

    This is really dredging up old posts, but my current problem is the same as in the old days, slow, opening forms. What caught my attention in your post was
    " instead of using the Master/Child links in the subform control (which means each subform is requeried whenever a new mainform record is displayed, even if the subform is not currently visible), just put a reference to the linking control on the mainform in the query behind the subform.".
    How exactly do you do that?

    Again, my apologies for dragging up old history,
    Cheers,
    Andy

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back-end Performance (2003 SP2)

    Leave the Link Master Fields and Link Child Fields properties of the subform on the main form blank.
    Let's say that the subform should be linked to the main form frmMain by the value of the text box txtSomething.
    Create a query to act as record source for the subform (or edit the existing query if you already have one).
    Set the criteria for the relevant field to

    [Forms]![frmMain]![txtSomething]

    (Substitute the actual names)

    You'll have to requery the subform when necessary.

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

    Re: Back-end Performance (2003 SP2)

    What Hans said.

    The "trick" is to know when (and when not) to requery.

    For example, you might have a tab control (named tabCtl, for example) that has one or more subforms on each of several pages. In your tab control's Change event you would have code something like this:

    Select Case tabCtl.value
    Case pg1.pageindex
    subform1A.requery
    subform1B.requery

    Case pg2.pageindex
    subform2.requery

    ... etc.

    End Select

    So, when the user manually clicks on a tab page, the system will requery the subforms it makes visible. You just need to make sure that you also add this line of code to your form's Current event so it will requery the subforms on the currently displayed tab page when you display another (or new) record.

    tabCtl_Change
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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