Really very slow reports (1 Viewer)

AndyHumpherson

Registered User.
Local time
Today, 11:49
Joined
Jun 25, 2012
Messages
22
Hi All

I may be able to put a copy of my database up for review but thought i'd ask the question first.

I have a database that reports on management information regarding deliveries and collections for health trusts and local authorities. It is big current over half a million lines of data.

I have 7-10 sub reports and charts grouped under one main report that are produced monthly. I have some code in a module that exports the reports based on various criteria to break the information down.

Now these reports are taking ages. The main report takes easily an hour to run, while the other reports take 15 minutes to half an hour to run and export.

I've tried a few things:
- Indexing all my fields that I use for criteria.
- Merging some queries to avoid duplicate running.
- Only importing the data that I actually need.

Any suggestions on how to improve this?
 

llkhoutx

Registered User.
Local time
Today, 06:49
Joined
Feb 26, 2001
Messages
4,018
Indexing all fields might be the culprit. Read up on why fields should be indexed.

Several simple queries will run faster that a large complex or elegant query.
 

mahenkj2

Registered User.
Local time
Today, 17:19
Joined
Apr 20, 2012
Messages
459
Once you know which fields are to be indexed, you would be able to make correct indexing.

In case, you have trouble in identifying, remove indexes and then run Analyze Performance in the database tools. It will prompt you the things needed to optimize objects.

best regards.
 

llkhoutx

Registered User.
Local time
Today, 06:49
Joined
Feb 26, 2001
Messages
4,018
There is not "user indexing" in Access, other than identifying which fields are indexed.
 

AndyHumpherson

Registered User.
Local time
Today, 11:49
Joined
Jun 25, 2012
Messages
22
Hi

Thanks, I have read up a little on indexing and i've only indexed fields that are queried regularly and have lots of different values. Will go through again though and double check.

Will make use of the performance analyzer though thanks Mahenkj2
 

llkhoutx

Registered User.
Local time
Today, 06:49
Joined
Feb 26, 2001
Messages
4,018
Only index fields which are related to other tables or which are sorted in your query. You don't need to index every field in a query.
 

AndyHumpherson

Registered User.
Local time
Today, 11:49
Joined
Jun 25, 2012
Messages
22
Thanks guys, i'll amend the indexes and run the reports again - see how it runs. They ran much better anyway on my home pc so obviously my work pc isn't up to the task...
 

Users who are viewing this thread

Top Bottom