Large Database Query

[cbetts75]

Hi,

I am currently using a large Access 2002 database in order to generate various reports.

My two main tables are despatches and returns from which they hold around 1,200,000 records and 100,000 records respectively.

The problem I have is that the reports use various expressions within various queries to generate a single result (percentages per channel etc.)
This is obviously very time consuming and it may take up to around 10 to 15 minutes to get a result from a chain of around 5 queries.

Can anyone suggest alternative methods to generate similar results in quicker time?
(Please note that the tables can not be downsized and records can not be archived)

Many thanks

 

[FoFa]

If the data is being summerized, sometimes it is faster to put the results in a "work table" and work off of that (because you can use different indexes). But it is usually a hit amd miss kind of thing. Try it, see if it makes a diff or not. But you would only put the summerized data in the work table typically And it would be denormalized typically.

 

[cbetts75]

No luck.

Any other ideas guys?

 

[KeithG]

Do you have your indexes setup correctly?

 

[cbetts75]

I do not have indexes setup on a few of the main fields i use to generate queries (I did not build the database!)

What are the possible implications of changing these fields to 'Yes (Duplicates OK)' where there are so many records within the table and the database is on a server?

 

[FoFa]

It could take awhile to build the indexes. Just remember to NOT index limited value fields as it is more a hinderance than a help. And indexed field should have no more than (crap what is it 5% like values in a given table) or something like that, to be of any real value.

 

[The_Doc_Man]

When doing multi-layer queries, if they have "WHERE" clauses because you are doing multiple filtration, do the MOST RESTRICTIVE filter first. (Innermost.)

If any sorting is involved, do that last (outermost).