Speed Up Report

[Guest]

Hi, I hope someone can give me some good suggestions. I have an Access 2003
(Access 2000 format) database. I would like to speed up my report(s) but I
have a somewhat complicated situation.

I have an unbound form that users enter criteria to generate a report. The
run button executes a make-table query that is based on a pass through query
to an Oracle database (located across town). Once that is finished the
report opens. The report's record source is a query that is based on a
grouped query linked to a local table.

This report is taking about 45 seconds to run. The make-table is copying
about 67K records. Is there anyway to improve the efficiency of this?

Thanks
LeAnn

 

[Duane Hookom]

You have a lot going on. Which step takes the most time?

 

[Duncan Bachen]

You have a lot going on. Which step takes the most time?

The biggest thing to look at is if you need all 67k records to be
brought down.

If your access query is doing a group by on the local table, you can
speed things up by having your pass through query do the grouping for
you. This way you're only returning the grouped by records, not the raw
records which were used to create it.

The more work you pass off to Oracle, the quicker your app. will run.

I have an application attached to Oracle in a similar way. I only use
the make-table method where I absolutely have to have the records local
for comparison or to use Access functions on that I've written (since I
can't create the functions on the Oracle side).

 

[Guest]

Thanks for your reply. Yes, I admit there's a lot going on - even more than
I've described. I would like to query directly rather than use the
make-table, unfortunately I'm in a regulated environment and this seem to be
the most acceptable way of getting at the data. Also I'm sure it would be
unacceptable to process the grouping on the production server. There are
other problems trying to do the grouping on Oracle.

One contributing factor might also be that I have a calculated field in my
top layer query (which is based on the grouped query). That seems to slow
things down but can't be avoided.

I suppose I can split up the processing time by placing the make-table
queries on the Open Event of the user's form. That seems to split it in 1/2
- 1/2 before running the report and 1/2 after selecting criteria and running
the report.

Such is the beast I work with.
Thanks