By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

Strange problem

P: n/a
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

Jan 11 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.