Back-end Performance (2003 SP2)

Topic

New Reply

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.

Reply | Quote

Replies

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 and the thread starting at post 356,901.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Check out the thread beginning at post 544,415. 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.

Reply | Quote