Want to speed up loading of a form

  • Thread starter Thread starter diarmuidq
  • Start date Start date
D

diarmuidq

I have a form, on which I have a tab control. Theres 5 tabs, with four of
the tabs havnig sub forms. It takes between 4 to 8 seconds to open on my PC.
Onsite it can be slow, taking up to two minutes.
Now, I already have setup the standard things for quicker data access - data
split into back end, link open to the backend database, sub datasheet to
off.
So I decided I would make the sub forms invisible, and only make them
visible when the tab is selected. I read somewhere that should make a form
quicker, but it no effect. Even on my PC, it still opened in around the same
time. I put a timer in the old and new versions of the form, and sometimes
the old version opens quicker!
So now I'm confused. Is there anything else I should be trying?
Thanks
Diarmuid
 
Assuming you have bound subforms, you can improve performance by unbinding
subforms on tabs that are not visible...and only binding them when the tab
is visible. Write code in the Change event for your tab control similar to
the following:

For Each pge In Me.tabMain.Pages
If pge.PageIndex = Me.tabMain.Value Then
For Each ctl In pge.Controls
If ctl.Properties("ControlType") = 112 Then
ctl.SourceObject = ctl.Name
End If
Next

Else
For Each ctl In pge.Controls
If ctl.Properties("ControlType") = 112 Then
ctl.SourceObject = ""
End If
Next
End If

Next

This code assumes that the name of the subform control and the actual
subform/sourceobject are the same, i.e., the name of the subform control on
your main form is "fsubWhatever" and the name of the sourceobject is
"fsubWhatever". After you have finished laying out the design of your main
form, unbind all of your subforms by deleting the entry in SourceObject for
all of the subform controls.
 
diarmuidq said:
I have a form, on which I have a tab control. Theres 5 tabs, with
four of the tabs havnig sub forms. It takes between 4 to 8 seconds to
open on my PC. Onsite it can be slow, taking up to two minutes.
Now, I already have setup the standard things for quicker data access
- data split into back end, link open to the backend database, sub
datasheet to off.
So I decided I would make the sub forms invisible, and only make them
visible when the tab is selected. I read somewhere that should make a
form quicker, but it no effect. Even on my PC, it still opened in
around the same time. I put a timer in the old and new versions of
the form, and sometimes the old version opens quicker!
So now I'm confused. Is there anything else I should be trying?
Thanks
Diarmuid
Click to expand...

Here are a couple of things you can do. First, if you're using Access
2000 or later and have the Name AutoCorrect option turned on, turn it
off. That can slow things down even in a low-overhead design.

Second, rather than making your subforms invisible, leave each subform
control's SourceObject property blank until the tab page containing it
is displayed for the first time. At that time, set the SourceObject
property to the name of the form it should display. This way, you only
load up the subform and query its recordsource when it will be needed
for the first time.
 
Paul Overway said:
Assuming you have bound subforms, you can improve performance by
unbinding subforms on tabs that are not visible...and only binding
them when the tab is visible. Write code in the Change event for
your tab control similar to the following:

For Each pge In Me.tabMain.Pages
If pge.PageIndex = Me.tabMain.Value Then
For Each ctl In pge.Controls
If ctl.Properties("ControlType") = 112 Then
ctl.SourceObject = ctl.Name
End If
Next

Else
For Each ctl In pge.Controls
If ctl.Properties("ControlType") = 112 Then
ctl.SourceObject = ""
End If
Next
End If

Next

This code assumes that the name of the subform control and the actual
subform/sourceobject are the same, i.e., the name of the subform
control on your main form is "fsubWhatever" and the name of the
sourceobject is "fsubWhatever". After you have finished laying out
the design of your main form, unbind all of your subforms by deleting
the entry in SourceObject for all of the subform controls.
Click to expand...

Paul -

I don't see the value of unbinding the subform control once it has been
bound. I would set the SourceObject the first time the tab is made
visible, and then leave it set. That is, I'd do that unless I ran into
the "can't open any more databases" error. Do you have some reason for
unbinding the subforms on tabs as they become invisible that I'm not
aware of?
 
Dirk:

Because it is faster! The reason not to leave the subform bound...if you
do, and you move through records on the main form, it will be
slow...especially if the user selects all the tabs. If the subform is
unbound and you move to another record, you're not querying for data you
don't need.
 
Paul Overway said:
Dirk:

Because it is faster! The reason not to leave the subform bound...if
you do, and you move through records on the main form, it will be
slow...especially if the user selects all the tabs. If the subform is
unbound and you move to another record, you're not querying for data
you don't need.
Click to expand...

I'm not convinced, but I think only testing will tell -- have you done
the testing? I must admit I haven't. My (unsubstantiated) feeling is
that the main performance hit is going to be the initial loading of the
form, and the filtering that takes place as you move from record to
record on the main form is going to have much less of an impact on users
than reloading the source object every time you change tab pages.
 
Dirk:

Yes...I've done testing. It made a huge difference in my Good Works app,
which has a very complex form with 10 subforms. The nature/quantity of
subform data may influence the results, but it made a big difference for my
app.
 
Diarmuid,

In addition to the comments made by Paul and Dirk, you have mentioned
about "data split into back end". There are many advantages in doing
this, particularly in a multi-user situation... but speed is not one
of them. On the contrary. If the app is stand-alone, you might
consider putting it back together into the one file, which could
result in a 10-15% speed improvement. (You would have to weigh this
up against the "ease of upgrade" factor!)

- Steve Schapel, Microsoft Access MVP
 
Hi Dirk,

I believe that we've had this conversation before :-)

I'm with Paul on this one - for his reasons and for one other which may not
be as important in a small application but for mine it is crucial - It
prevents me from getting the dreaded "Can't open any more databases. . . "
error. I have several rather complex forms and if two of them are open with
all subforms bound, they suck up all the table resources and I have to close
everything.

. . . carry on . . .
 
Thanks, this made a big difference. Load time went from an average 4
seconds, down to .6 seconds. In my office, with 2 users. Will slow down
onsite with 10 users of course.
Now, another interesting issue popped up. The form is for Members, who work
in Firm, have some Codes and may have some TechInfo. So the source query ,
qryfrmMembers, was like this

SELECT Members.*, Firms.*, CodeSummary.Qualifications,
CodeSummary.Committees, CodeSummary.Interests, TechInfo.TechPRFirmNo
FROM ((Members LEFT JOIN Firms ON Members.MembFirmRefNo = Firms.FirmRefNo)
LEFT JOIN TechInfo ON Members.IDENT_NO = TechInfo.TechIdentNo) LEFT JOIN
CodeSummary ON Members.IDENT_NO = CodeSummary.IDENT_NO;

To try to speed things up, I decided to remove the TechInfo table from the
query, and take TechPRFirmNo off the form. So new query

SELECT Members.*, Firms.*, CodeSummary.Qualifications,
CodeSummary.Committees, CodeSummary.Interests
FROM (Members LEFT JOIN Firms ON Members.MembFirmRefNo = Firms.FirmRefNo)
LEFT JOIN CodeSummary ON Members.IDENT_NO = CodeSummary.IDENT_NO;

But when based on the new query the load time goes up to 3.5 seconds! I
thought removing a table should speed things up? Especially when not a
direct join.
Any ideas? Not essential, I'd just like to figure it out.
Thanks
Diarmuid
 
The LEFT JOIN is slow because it causes a full table scan. It also causes
the query not to be updatable. If there is any way to write your query
without it, you'll be better off.
 
I also had to give users modify permission on each of the sub forms. Any way
around this?
Thanks
Diarmuid
 
Back
Top