speed issue

  • Thread starter Thread starter yaohong.chng
  • Start date Start date
Y

yaohong.chng

Hi all, I have been pondering about speed issue about reports.

Which would be faster? A delete & append query for new data entered by
the user into a main table on which a report is based on or reports
based on queries from different tables?

The proposed main table would consist of all data otherwise found in
the smaller tables. I've been trying to convince my boss that using
separate tables would be faster but he seems to think otherwise.

He believes that having a large table with calculated values would be
faster than calculating on the fly(which I have been doing so far).
However, since I have different tables, I require either a UNION ALL
query or many subreports...and then using a runningsum txtbox in the
report itself.

Any other suggestions to increase the speed of generating reports?
 
Hi all, I have been pondering about speed issue about reports.

Which would be faster? A delete & append query for new data entered by
the user into a main table on which a report is based on or reports
based on queries from different tables?

The proposed main table would consist of all data otherwise found in
the smaller tables. I've been trying to convince my boss that using
separate tables would be faster but he seems to think otherwise.

He believes that having a large table with calculated values would be
faster than calculating on the fly(which I have been doing so far).
However, since I have different tables, I require either a UNION ALL
query or many subreports...and then using a runningsum txtbox in the
report itself.

Any other suggestions to increase the speed of generating reports?
Click to expand...

There is no universal answer. From a "best practices" standpoint you want to
work with queries against the live tables whenever possible and avoid temp
tables. There are situations with reports though where the use of a temp table
can speed things up.

It mostly has to do with whether the report's design causes the queries that are
doing the calculations on-the-fly to run multiple times during the processing of
the report. In those cases using a temp table can eliminate that multiple query
processing and make the report faster.

Bottom line is you have to try both and see which is faster.
 
Back
Top