efficiency of queries for reports in Access 2002

[SlowLearner]

Is it more efficient to concatenate strings in the underlying data source of
a report, or in the text box of the report?

 

[Klatuu]

Keep your queries as simple and generic as possible. Do all the
calculations, concatenations, etc in the report. First, you can possible
reuse the query and more important, execution is much faster when done at the
report level. Doing the work at the query level increases your network
traffic.

 

[AnandaSim]

Keep your queries as simple and generic as possible. Do all the
calculations, concatenations, etc in the report. First, you can possible

I've tended to do more work in the queries exactly for the first
reason. If there are lots of custom calcs, then the query can and
should be re-used for additional queries and additional reports. If I
do a lot of calcs in one report, then formula maintenance if
problematical across reports.

reuse the query and more important, execution is much faster when done at the
report level. Doing the work at the query level increases your network
traffic.

I've noticed that queries will take time in a batch to generate the
results. Whilst reports tend to calculate on a page by page basis,
prolonging the "agony" of completing the job.

At the end of the day, all the data has to be calculated, the queries
do it in a batch, the report does it in a dribbling way.

Do you have any doco and stats on the effects?

Thanks

Ananda

 

[Klatuu]

I have no documentation that would support my position; however, I did have a
situation where a very complex report with over a hundred calculation that
were done in the query. The report took just over an hour. I moved the
calculations to the report and it ran in about 3 minutes.

Also, think about the network traffic. Since Jet is not a client server
app, but a file server app and all work is done on the client, fetching the
data to do the calculations takes much longer that just retrieving the
records and performing the calculations.

Now, in a true client server application like SQL Server where data
retrieval and calculations are performed at the server level, then letting
the server do the work and returning only the pre calculated values, it would
be a different story.