Conditional Format and performance

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a continuous form and want to make the foreground red in one combo box
based on the value of another field to which a checkbox is bound. The
expression is just this: Expression Is [chkBox1]=True

However, this form is very dynamic and is often requiried. The conditional
format slows it down to a crawl during the requery process. This is a split
FE/BE, with a copy of the FE residing on each computer.

Is my approach inherently inefficient?
 
Is a requery really required? Recalc or Refresh are faster than Requery.
Refresh allows you to see modifications made by other, to records you
initially "know". What a Requery does more is that it also make visible
records added by others.

I don't think that a Boolean check and eventually changing the brush (color)
can really impact on the speed, but Requery can, since it bring back all the
records (which pass the WHERE clause) from the BackEnd to the FrontEnd.

If a requery is required (pun intended), try to have an efficient WHERE
clause which will strongly limit the number of records to be sent.



Vanderghast, Access MVP
 
Unfortunately, Requery is required on this form. There are two main reasons:

1. The users change filters very often, and
2. At any given moment, it is possible that another user has modified,
added, or deleted a record.

When I remove just the conditional format (no other changes), the form is
much faster. I finally decided to remove the conditional format and instead
add a calculated control beside the one in question. This control is always
red, but its ControlSource is this: =IIf([cboBox1],"X",Null), so it appears
only on records matching the IIf True condition.

According to the users, this is much faster than using Expression Is
[cboBox1] Is Null in a Conditional Format. I still like the Conditional
Format idea, but it still seems to make the system take a performance hit.

Now, one more piece of info. This seems to be much more of a problem on
one-year-old Duo Core 1.4 GHz Dell Dimension systems with 1 GB RAM than it is
on this week's new Quad Core 2.8 GHz Dell Optiplex systems with 2 GB RAM. I
guess some of that is to be expected, but I just found out that the
difference can be quite marked.

Michel Walsh said:
Is a requery really required? Recalc or Refresh are faster than Requery.
Refresh allows you to see modifications made by other, to records you
initially "know". What a Requery does more is that it also make visible
records added by others.

I don't think that a Boolean check and eventually changing the brush (color)
can really impact on the speed, but Requery can, since it bring back all the
records (which pass the WHERE clause) from the BackEnd to the FrontEnd.

If a requery is required (pun intended), try to have an efficient WHERE
clause which will strongly limit the number of records to be sent.



Vanderghast, Access MVP

Brian said:
I have a continuous form and want to make the foreground red in one combo
box
based on the value of another field to which a checkbox is bound. The
expression is just this: Expression Is [chkBox1]=True

However, this form is very dynamic and is often requiried. The conditional
format slows it down to a crawl during the requery process. This is a
split
FE/BE, with a copy of the FE residing on each computer.

Is my approach inherently inefficient?
Click to expand...
Click to expand...
 
If you have calculations in the control sources of any fields (regardless if
the control is used in a FormatCondition) it will impact the formating and
the perceived speed of the form. Been there done that, hated it and sued.

Brian said:
Unfortunately, Requery is required on this form. There are two main reasons:

1. The users change filters very often, and
2. At any given moment, it is possible that another user has modified,
added, or deleted a record.

When I remove just the conditional format (no other changes), the form is
much faster. I finally decided to remove the conditional format and instead
add a calculated control beside the one in question. This control is always
red, but its ControlSource is this: =IIf([cboBox1],"X",Null), so it appears
only on records matching the IIf True condition.

According to the users, this is much faster than using Expression Is
[cboBox1] Is Null in a Conditional Format. I still like the Conditional
Format idea, but it still seems to make the system take a performance hit.

Now, one more piece of info. This seems to be much more of a problem on
one-year-old Duo Core 1.4 GHz Dell Dimension systems with 1 GB RAM than it is
on this week's new Quad Core 2.8 GHz Dell Optiplex systems with 2 GB RAM. I
guess some of that is to be expected, but I just found out that the
difference can be quite marked.

Michel Walsh said:
Is a requery really required? Recalc or Refresh are faster than Requery.
Refresh allows you to see modifications made by other, to records you
initially "know". What a Requery does more is that it also make visible
records added by others.

I don't think that a Boolean check and eventually changing the brush (color)
can really impact on the speed, but Requery can, since it bring back all the
records (which pass the WHERE clause) from the BackEnd to the FrontEnd.

If a requery is required (pun intended), try to have an efficient WHERE
clause which will strongly limit the number of records to be sent.



Vanderghast, Access MVP

Brian said:
I have a continuous form and want to make the foreground red in one combo
box
based on the value of another field to which a checkbox is bound. The
expression is just this: Expression Is [chkBox1]=True

However, this form is very dynamic and is often requiried. The conditional
format slows it down to a crawl during the requery process. This is a
split
FE/BE, with a copy of the FE residing on each computer.

Is my approach inherently inefficient?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top