Complex Query Slowdown...

[DamianC-UK]

I have a problem, which is desperately in need of a solution, before my db
users lynch me!! So any help would be much appreciated...

I have an extremely complex query, which takes around 30 seconds to run.
The problem is that this query feeds around 10 other queries, which
ultimately produce a report.
10 * 30 seconds = you see the problem!!
When more than one user is in the database, the main complex query takes
more than 30 seconds to run, 10 * more than 30 seconds = Very slow!!

The database is networked, and has a BE/FE structure, but the FE is also on
the network, due to IT restrictions on local PC's).

This database is in use by around 15 users at any time.

As an overview of what the db does:

It contains team members names, and then stores performance information for
a host of (10+) KPI's, with raw data stored in individual tables, as the data
comes from external sources. This needs to be calculated into the performance
results and then referenced to each individual, before being returned by
team, individual, department or for the whole company.
Due to the nature of the business, there are frequent changes to where each
team member is working, and this data needs to follow where they have been at
any point, as KPI's are different for each area, but the core data is the
same.

The main complex query looks up each team members whereabouts by date, from
a table that tracks team movements, this is then used in all the other
queries to calculate the KPI's against the correct measures, depending which
team you were in on each date.

I've looked at changing the main query into a make table to fix this
problem, and while this fixes the speed issue to a degree, it creates the
problem of balooning the size of the database, by hundreds of Mb a day.

So my question is this:

Is it possible to (either in VBA, or in queries) make this complex query run
once, store the results for reference in subsequent queries, without having
to rerun the main query each time?

I'd thought about doing this in VBA, but am not sure where to start with this.

I will quite happily share SQL etc, but want to keep this as brief as
possible, as it's already quite complicated to explain.

Any support would be greatly appreciated.

Thanks

 

[Dale_Fye via AccessMonster.com]

One way that I have done this is rather than using a Make-Table query to
create this table, create it once, and then update it daily (or better yet,
only when something changes).

If you need to update it daily, use the tables "Description" property to
store the date it was last updated. Then, when your splash form loads, check
to see whether it has been updated on that day and whether it is in the
process of being updated (you will need to set a flag somewhere in your
database that you can turn on before you start the update and turn off after
the update is complete).

If it has not been updated yet, and is not in the process of being updated,
then:
1. set the Updating flag to true
2. delete all of the existing records in the table
3. use an append query to copy all the records from your long running query
into this table
4. set the Updating flag to false
5. Set the tables description property to the current date

currentdb.tabledefs("TableName").Properties("Description") = Date

As an alternative to Steps 2/3, you could write a query that uses the results
of your "long running query" to update the table. This would restrict the
"bloat" that comes with deleting and appending lots of records. It may take
a bit longer, but this would only affect the first person to open the
application each day.

One thing you have to be careful of is that using the tables "Description"
property will generate an runtime error (3270 - Property not found) if it has
not been previously set (this will happen when you try to read or write to
the property), so you will need to have a little error handler to handle that
possible situation.

Err_Proc:
if err.Number = 3270 then
'This will create the Description property for a table and set its
value to "Test"
set prp = currentdb.tabledefs("TableName").CreateProperty
("Description",dbText, "test")
currentdb.TableDefs("TableName").Properties.Append prp
Resume
else
msgbox err.number & vbcrlf & err.description
end if

The other key thing you need to take into account is that you need to prevent
your users from running any of those other queries you mentioned while the
Updating flag is set to True.

HTH
Dale