I have a problem which I do not understand........................
I have a split database. Tables in the backend on a networkdrive, all
other in frontend placed in %temp%
I have this form [form1] that is based on a query [main query]. This
query consists of one table [table1] and two queries [subquery1 &
subquery2], both of them joined to the table. It takes about three
seconds to load [main query].
To improve performance I put the results of [main query] in [table2].
I did that by copying [main query 1] to a create table query so that
fieldnames etc remained the same.
I put some indexes in the table on the numeric fields that are used for
filtering and searching.
I made a query [main query 2] (Select * from table2) that runs in less
then a second. Perfect!
Now I based [form1] on [main query 2].
Shocked I saw how the performance of the form actually dropped instead
of speeding up....
The form loads a bit faster then it did when based on [main query 2]
but the conditional formatting on the form is really slow, one can
actually see line after line being formatted (4 fields get a different
backcolor based on the value within that field). When scrolling, lines
get formatted as they are visible on the screen, this really reduces
speed when scrolling through the page (2623 records)
When loading THE SAME form based on [main query 1] loading the forms
takes about half a second longer but conditional formatting is done
within a second for the whole! form. But removing all conditional
formatting has no effect and none of the fields in the form is a
calculated one (except 2 Dlookups on top of the form but removing them
has no effect either)
so, to be short, [main query 2] is about three times as fast as [main
query 2].
Still a form based on [main query 2] is slower then the same form based
on
[main query 1] even though bith queries contain exactly the same fields
and
fieldnames, I even tried them both with the same names...
Is there anyone who knows how this can happen or who can tell me what
to look for?
Greetings, Henro
