Load Queries Based Report Faster

[yus2aces]

I have a report based on query with some calculation (sum, avg, division) which is also based it from another calculated query which is also from another query. When I want to preview/load the report, it takes more than 10 minutes. :banghead:
Is there a way to load or format the report faster? I am thinking to make the temporary table from the each query. Is this method will work or any better approach?

 

[pr2-eugin]

Creating TempTables will help, but as your current flow is laid out, this will not work out well either. It will result in Bloat, complete redesign of all your queries. A bit more complicated.

Not sure why you have based the report on multiple underlying queries. SLOW queries normally are a result of either not Normalized data or use of Domain functions in Queries.

Which one is your case?

 

[stopher]

SLOW queries normally are a result of either not Normalized data

Actually de-normalising can improve performance particularly where there is a lot of pre-calculation like the OP suggest. Datawarehouses are typically de-normalised for efficiency.

See this link which explains how to go about designing a non-normalised database structure that is optimised for reporting.

Note that good choice of indexing will also help you speed up your queries.

Chris

 

[yus2aces]

Thank you so much for the reply.

I am not sure , but I try to practice the best of normalization that I know. To make it clear, this database is about fertilizer for each block of plantation. So I had to use several queries for each purposes, which is needed for another.

For example:
a. 1st query: calculate nutrient status from leaf analysis based on nutrient threshold table. The leaf analysis table right now consist of almost 10,000 records.
b. 2nd query: create xtab to display dosage rule for each oilpalm condition (deficient, low, optimum, high, excess) based on soiltype and tree age.
c. 3rd query: join the 1st and 2nd query to define the right dosage for each nutrient status based on the given rule in 2nd query
d. 4th query: this one is full of calculated expression such as iif, sum, avg., based on the 3rd query above. The purpose is to divide the 3rd query result in two based on fertilizer.
e. 5th query: to display the final result in 4th query with the total fertillizer used in the area of leaf analysis sample, by multiply the dosage in 4th query with the tree population within.

The last query is the source of my report.

Yes, it is full of calculated query but it isn't a domain aggregated function (if my understanding is right) .

My plan is to make a template table, which I can copy using different name and delete from each time the query runs (using vba). Maybe I will apply this to the 4th query.

I hope my description of the program is clear enough.
I will visit the link (from stopher) to know more.