Is there a way to speed up reporting?

[Bullsandbears123]

I'm using Access 2002 and I make many complicated reports and subreports. Is there a way to speed Access up when querying and calculating the reports? I usually run them at night but since I make so many it would help a lot if I can speed it up. I have a Xeon processor and a Gig of RAM but its still SO SLOW. Thanks!

 

[bhoran]

You could test the queries if thay are also slow you may need to index some of your fields or include indexed fields in the selection criteria.

You may have to speak to your database admin if you are accessing tables stored outside of access.

 

[dhookom]

You haven't given us much to go on. bhoran raises a good point. Something a small as removing [Page] of [Pages] from a report will increase the speed greatly.

We don't know if your data is Access or SQL Server or linked text files or...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bullsandbears123]

Is there a simple way like replacing Access with SQL server or something. I have a lot of reports. Many that sum() others that use page of pages. I also use query's with many sub queries and UNIONS. In addition to Select Top X queries. Top X seems extremely slow.

 

[dhookom]

I would first try to remove the "Of [Pages]" if possible. Check your indexes to make sure you have all criteria and join fields indexed. UNION queries will work much faster if you can use UNION ALL rather than UNION.

There have been times when I have used temporary tables for reporting. This seems to speed reports tremendously.

Moving to SQL Server is a big step so I would try a number of methods prior to upsizing before doing this.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bullsandbears123]

Whats the difference between Union and UNION ALL, thanks

 

[dhookom]

UNION will attempt to determine if there are duplicate records in the separate SELECT statements. The duplicates would be filtered out. This might take lots of additional, unnecessary processing. UNION ALL doesn't care about duplicates.

For instance, if you have a table of Customers and a table of Suppliers and wanted to use a UNION query to return all names and addresses, the UNION ALL might return a person twice if they were both a Customer and a Supplier. UNION would not return duplicates but might run much, much slower.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bullsandbears123]

Great, THanks,

Do you know if there is a more efficient "Select Top X...", b/c this one is extremely slow when I run it.

 

[dhookom]

It might depend on where you are using this. I sometimes send all records to the report and then cancel the printing of some records within the report. I doubt this would be any faster.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.