Odd subform performance problem

[Guest]

I have a complex form with lots of popups and other data that is bound across
tables. Performance is JUST acceptable. On the current version of the form it
takes about 1 to 1.5 seconds to open the form.

One of the form elements is a subform that runs a query to find existing
inventory. I'm making a new version of this display that does grouping and
sorting a little differently. When I put this query into the subform it takes
about 2 to 3 seconds to open the form, just long enough to be annoying. So I
tried removing the subform, and that definitely speeds it back up, faster
even than the original.

But here's the thing, while the query in the subform is complex, but it's
not slow. If I run it with no WHERE it takes about 1 second to draw,
returning about 1200 rows. So basically even if my link wasn't working, it
still shouldn't make it run THAT much slower. But there is a working link,
and it filters out everything. If I add that WHERE to the SQL and run it in
Query Analyzer, it's instantaneous.

So there's the mystery... how is it that this subform is taking up so much
time to draw even though there's nothing in it and the query is instant?

Maury

 

[DAVID]

Subform data is loaded first, before the main form
data.

Then the subform is re-queried, to match the master
field on the main form.

Then the main form is filtered or selected, and the
subform is re-queried, to match the new value of the
master field on the main form.

To speed this up, remove the subform from the subform
control, and set the form property of the subform
control only after the form has finished opening,
in the load event.

This does make things a little tricky sometimes:
some versions of Access reset the master/slave
fields when you do this! I don't use the master/slave
fields anymore: i use special criteria in the
subform recordsource instead, or use dynamic SQL
in the subform recordsource.

Dynamic SQL (written in the load or open event) is
also a good way to speed up form and subforms,
because it alows you to set the SQL in order, only
after the conditions are settled, so you don't have
lots of requeries as the controlling fields are
filled or selected.

Combo boxes are another control with the same
kind of problem. Their worst feature is that they
hold up painting a datasheet form until the whole
recordset is loaded. So on datasheets, avoid using
combo boxes. Use a joined table to find and show
read-only related values, and use a separate
update form if you want to use combo boxes for adding
or editing records.

(david)

 

[Guest]

Subform data is loaded first, before the main form
data.
Then the subform is re-queried, to match the master
field on the main form.
Then the main form is filtered or selected, and the
subform is re-queried, to match the new value of the
master field on the main form.

Oh wow! Thanks for this David, this is a little bit of under-the-hood I
hadn't seen before. It definitely explains what I'm seeing - and its
definitely something I'll keep in mind for the future.

One related question: does this process take place even if the subform is
hidden? In this particular case the main form has a tab control with subforms
on each tab (its a pretty complex little form!). The subform that's causing
the problem IS on the main form, but I could move it easily enough. If
visibility effects this chain of events, maybe the easiest solution is to
simply move it onto a new tab.

To speed this up, remove the subform from the subform
control, and set the form property of the subform
control only after the form has finished opening,
in the load event.

So do this in the "parent" form's load event?

This might solve another question I had: since the query in question could
be _dramatically_ sped up by re-writing it for each user, so if I can
explicitely write the SQL for the subform and then populate it, that would
likely solve all the problems.

The reason you can't do this with the master/link field is that the query
uses a derived table internally, so the filter WHERE gets applied "too late",
after the derived table has already run. If you re-write the SQL with the
WHERE inside the derived table its basically instant.

I'm going to start experimenting with this! Thanks, this is really helpful!

Maury

 

[Guest]

One other question comes up, how do I refer to the RecordSource on the
subform? I have another subform (in one of the tabs) that I refresh using:

Me!SplitsEditor.Form.Refresh

so I figured the answer would be:

Me!PositionDisplay.Form.RecordSource = subSql ' a string with LOTS of sql

but this complains that "error 2455 ... invalid reference to the property
Form/Report". I also tried pure dot notation, but that didn't make a
difference. Any idea? Is the form called something different in this case, or
perhaps the RecordSource is renamed?

Maury

 

[DAVID]

Oh wow! Thanks for this David, this is a little bit of under-the-hood I
hadn't seen before. It definitely explains what I'm seeing - and its
definitely something I'll keep in mind for the future.

One related question: does this process take place even if the subform is
hidden? In this particular case the main form has a tab control with subforms
on each tab (its a pretty complex little form!). The subform that's causing
the problem IS on the main form, but I could move it easily enough. If
visibility effects this chain of events, maybe the easiest solution is to
simply move it onto a new tab.

Yes, even if the subform is hidden.
The main form hasn't been painted yet, so there
is no way for the subform to know if it is visible
or not.

 

[DAVID]

One other question comes up, how do I refer to the RecordSource on the
subform? I have another subform (in one of the tabs) that I refresh using:

Me!SplitsEditor.Form.Refresh

so I figured the answer would be:

Me!PositionDisplay.Form.RecordSource = subSql ' a string with LOTS of sql

That looks correct to me. Check that the name is correct!
I normally use Me.ControlName (dot instead of bang) for
controls, because it automatically checks.

(david)