Improve Performance

[Guest]

I'm not sure if anyone can help me but I would like to speed up the output of
my report. I have a stand alone Access 2003 database located on our file
server (no front-end/back-end). The report is based on a union query but
there are a series of other queries that must run before the report is
generated. I am querying an Oracle system (located across town) and a
FoxPro database (NT4 server located here).

I have:
4 pass through queries (2 for each system above)
4 make-table queries 1 for each of the pass throughs
2 select queries that each use 2 of the above tables
and finally, there is a union query that returns all of the records from
the 2 queries above.

The report has VBA to hide or display records and was taking 60 - 90 seconds
to export (when each of the make table queries returned about 1500 records).
Unfortunately I had to remove some of the selection criteria and now 2 of the
queries are returning 20K+ records. The selection criteria is now stored in
a local table and that was added to my "lookup" queries to select records
using a left join. Now the report takes 3 - 4 minutes to export.

Some of the problem could be the server and network speed but is there
anything I could do to speed the query processing? I can't index any fields.
Hope this makes sense to someone.

Thanks
LeAnn

 

[Guest]

Make sure that none of your queries have sorting on any fields. Access
reports do the final sort so anything before is just wasted cpu and temp
space.

See if UNION ALL will return the records that you want. It's much faster
than UNION; however, it might let in some duplicate records.

Make sure that the tables in Access are indexed based upon any Where clauses
in the SQL / criteria in the queries. Oh - you said that you can't index any
fields. Maybe talk to the Oracle and Foxpro DBAs. Some can be bribed with
beer. I can.

 

[John Spencer]

You might be able to increase performance by avoiding the Make Table queries and
turning them into Append queries on pre-defined tables. You can have indexes on
the pre-defined tables.

One technique I have used is to predefine my tables and never populate them. I
then just copy the table structure to a temporary database and link to the
tables in the temporary database.

On the other hand, how often is this report generated? Is it worth a lot of
time, effort, and testing to speed up the report if it is only taking 3-4
minutes? I have one relatively slow report that is run 4 times a year - the
cost of making it run faster is not worth an hour of my billing time (I gave the
managers a choice).