Best way to improve performance on a form?

[asp3232]

Hi all,

A little background: my database has a main table of projects with about 275 records (with about 40 fields per record), and each record is linked with 0 to 10 additional issue records in another table (depending on the project).

I've got a form for entering and modifying the data (including a subform in continuous mode to work with the issue records).

If the form has no sorting or filtering applied, performance is acceptable when going between records, but if a sort (such as sorting projects by name or by project leader) or filter (such as excluding inactive projects) is applied, performance SEVERELY suffers (i.e. about 5 seconds or more to go to the next or previous record). This makes it very tedious to find the desired record, as you might imagine...

Are there any simple tricks that might improve this slowdown?

One idea I had is to create another form that has only a few key fields on it such as project leader and project name (most likely in a list box) and a checkbox or two for the common filters, and filter on this subform to display a list of projects, from which the user can select the project desired, which will then jump the main form (unfiltered and unsorted) to the right record. (though I'm not sure exactly how to make this jump happen).

Any help would be much appreciated.

Thanks!

asp

 

[hwkranger]

index your fields, makes faster for searches, slows down updates... Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[KUZZ]

the first thing to do is go to options/general and disselect all checkboxes at NAMEAUTOCORRECT!!!!!
increases speed by 40% at least. Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[asp3232]

Ok, unfortunately, the indexing doesn't seem to make much of an appreciable difference on the speed of changing records when the filter and/or sort is active (still 3 or 4 seconds).

And I didn't have any NAMEAUTOCORRECT boxes on options/general (I'm on 97 -- are they somewhere else?)

The situation is not helped by having the back-end database on a server 2000 miles away, though I would think that impact should be pretty minimal -- but if I copy the back-end to my local drive and re-link the tables, performance is ton better!)

Any other ideas?

Thanks!

asp

 

[ssecca]

Change all your queries to pass through queries and make all your forms moduleless forms.

good luck
ssecca

 

[asp3232]

Hi ssecca,

Thanks for your reply. Please clarify what you mean by "pass through queries" and "moduleless forms"

Thanks!

asp

 

[asp3232]

Quick follow-up to my previous post -- I took a quick look at pass-through queries, and it appears that they will only talk to an ODBC server (such as SQL server), and our back-end database only lives on a network shared directory as a file, not as something that has a server running. Is there a way to use pass-through queries in this situation?

Thanks!

asp