Weird problem in forms?

[Henrootje]

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.

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 a form based on
[main query 1] even though they contain exactly the same fields en
fieldnames.

Is there anyone who knows how this can happen or who can tell me what
to look for?

Greetings, Henro

[Henrootje]

Found it! It was just a matter of indexing: that had not been done on
the new table at all!
On 11 jan, 12:04, "Henrootje" <[e-mail]:

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.

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 a form based on
[main query 1] even though they contain exactly the same fields en
fieldnames.

Is there anyone who knows how this can happen or who can tell me what
to look for?

Greetings, Henro