I would like to collect your ideas on improving report speed. I have to chuckle when I see people complaining about reports that take 20 seconds. We have a statewide database with millions of records, hundreds of tables, and an old inefficient server, and if we can get our reports to run in under 15 minutes, we think we have a fast report. We also are unable to upgrade our clients, meaning we can't use the optimal native driver.
The following are some strategies for improving speed that I've picked up, but I would like to hear about other people's methods to expand (or correct) upon this list:
1-Pass all elements of the record selection criteria to the SQL statement.
a- Do not use (most) formulas for record selection
b- Follow guidelines in SynapseVampires FAQ
2-Perform grouping on server (in our case, it's faster NOT to, though)
3-Link tables on indexed fields whenever possible.
4-Select on indexed fields when possible.
5-Remove unused tables, unused formulas, unused running totals from the report.
6-Use conditional formulas instead of running totals when possible.
7-Use conditional formulas to return a desired field result or not, instead of using suppression to eliminate unwanted records.
8-If using running totals, evaluate the minimum number of times, i.e., on change of group instead of for every record, if this is an option.
9-Group on indexed fields when possible (not sure, just surmising)
10-Group on database fields instead of formulas when possible (not sure, just surmising)
11-Avoid subreports (in most cases), although they may result in a faster report if they eliminate significant row inflation.
12-Using a command as the datasource will be faster than using CR to link tables, select records, etc.
13-If using a command as datasource, develop parameters within the command.
14-Link tables by selecting a lead table that has one indexed record to link to tables containing multiple corresponding records.
15-Suppress unnecessary sections; suppress or hide details to facilitate grouping on the server.
16-Avoid nested formulas.
17-Whenever possible, limit records through selection, not suppression.
18-Use SQL expressions to convert fields to be used in record selection instead of using formula functions.
I'm sure I've missed many obvious concepts. Please add any you can think of. Links to other sources are welcome as well.
Thanks for contributing!
-LB
The following are some strategies for improving speed that I've picked up, but I would like to hear about other people's methods to expand (or correct) upon this list:
1-Pass all elements of the record selection criteria to the SQL statement.
a- Do not use (most) formulas for record selection
b- Follow guidelines in SynapseVampires FAQ
2-Perform grouping on server (in our case, it's faster NOT to, though)
3-Link tables on indexed fields whenever possible.
4-Select on indexed fields when possible.
5-Remove unused tables, unused formulas, unused running totals from the report.
6-Use conditional formulas instead of running totals when possible.
7-Use conditional formulas to return a desired field result or not, instead of using suppression to eliminate unwanted records.
8-If using running totals, evaluate the minimum number of times, i.e., on change of group instead of for every record, if this is an option.
9-Group on indexed fields when possible (not sure, just surmising)
10-Group on database fields instead of formulas when possible (not sure, just surmising)
11-Avoid subreports (in most cases), although they may result in a faster report if they eliminate significant row inflation.
12-Using a command as the datasource will be faster than using CR to link tables, select records, etc.
13-If using a command as datasource, develop parameters within the command.
14-Link tables by selecting a lead table that has one indexed record to link to tables containing multiple corresponding records.
15-Suppress unnecessary sections; suppress or hide details to facilitate grouping on the server.
16-Avoid nested formulas.
17-Whenever possible, limit records through selection, not suppression.
18-Use SQL expressions to convert fields to be used in record selection instead of using formula functions.
I'm sure I've missed many obvious concepts. Please add any you can think of. Links to other sources are welcome as well.
Thanks for contributing!
-LB