I'm having a problem trying to optimize the performance of one of my
A97 databases. I have very slow record navigation after a change I made
to the table structure, and I'm not sure how best to correct it.
For purposes of explanation, lets say I have two tables: tblIssues and
tblLocationHistory. The tblIssues table contains 'incidents' along with
the incident header information. The tblLocationHistory table contains
a list of all locations that incident has been to along with time
stamps and owner information.
tblIssues
PK: IssueID (autonumber)
~Many misc fields
tblLocationHistory
PK: HistID (autonumber)
FK: IssueID
OwnerID
Location
DateBegin
DateEnd
I have a primary form, which contains information from tblIssues that
contains a button to pull up another form, which then shows that issues
location history. This works just fine.
The problem is that I need to show the original location (first record
for that issue in tblLocationHistory) as well as the last location
(last record for that issue in tblLocationHistory) on the Primary form
for a 'dashboard' view.
Originally I tackled this problem by adding de-normalized fields in
tblIssues that contained the original and current location. At the time
I figured the performance gain and simplified query structure would
outweigh the problem of trying to keep the current and original
location information in tblIssues correctly synchronized with the data
in tblLocationHistory. Turned out I was wrong. Despite my best efforts,
there were still occurrences where the two were not synchronized, and
it created major workflow and reporting problems when it did occur. So,
I decided I would try and do it properly and represent those fields
with queries rather than duplicate data fields.
First, I built a query (qryOrigin1) based on tblLocations that
performed a Totals function to GroupBy IssueID and display the Min of
HistID for original location. (I did same thing but Max of HistID for
Current Location) I then created another query (qryOrigin2) which
utilized the results of qryOrigin1 to display the rest of the fields in
tblLocationHistory. The queries qryOrigin2 and qryCurrent2 were then
linked to tblIssues in another table and used as the datasource for the
primary form. It works great (really fast) except for one big problem:
the fields on the form were no longer updateable because of the sum
functions performed in qryOrigin1 and qryCurrent1.
I started over and tried something else. I returned the datasource of
the primary form to tblIssues and used dLookup functions to access the
information in qryOrigin2 and qryCurrent2. The fields on the primary
form were now updateable, but now those fields with the dLookup
functions were taking north of four seconds to display after a user
navigated to a new record. This was an annoyance for users, so I tried
a new method.
This time I created two separate sub forms on the Primary form that
contained the Current and Original location moves. This solved the
problem of the information taking 4 seconds to display, but also
introduced a new problem. Record scrolling now takes over 1 sec going
from one record to the next.
I had assumed A97 set up the sub forms to automatically requery the
entire dataset displayed in the subforms with each record move, so I
tried removing the linkchild fields and applying a filter instead on
the OnCurrent event. While this worked, there were no observed
performance gains.
At this point the only thing I could see doing to increase the
performance would be to transfer everything over to a manually coded
recordset. That sounds like a PITA, and I'd like to avoid it if
possible.
Right now, this slow performance is not much more than an annoyance for
users. However, the database is set to grow in size significantly and
I'm worried that these annoyances will turn into more serious
performance issues. It seems to me that if it's possible to make a
single query that can display everything I want it to and has great
performance (but is read only), I should be able to recreate something
that is editable and has similar performance. Since I'm almost entirely
self taught, I'm wondering if there isn't a more practical way to go
about doing this that I don't know about.
Thanks in advance for any advice any of you might add
