|
Return to Newsletter Contents...
Report Optimization Techniques
by: Aaron Zechman, BI Consultant, MCSE
Have you ever had a report(s) that
runs longer than you think it should and only have read only access to the data
you are pulling back? There are two
categories of techniques that should be able to help you out in regards to
helping speed up your reports.
The first technique is to try to push
all the work to the database server.
There are several things available to assist you in doing this.
The first is to check your options.
You can do this by going to your file menu and selecting options.
Next you will need to click on the database tab and under advanced
options make sure the check boxes are clicked beside “Use indexes on the Server
for Speed” and “Perform Grouping on Server”.
The second thing you can do is to use parameter fields.
This will decrease the number of records brought back from your server
and provide user interaction as well.
A parameter can be added by using the Select Expert or the Record
Selection Formula Editor. Lastly you
can take advantage of SQL Expression Fields.
You will find this option in your field explorer window and this is
another way to limit the data coming back to the report.
This will push the limitations of your report onto the database server.
The
second consideration in order to speed up reports is design.
The first option here is to use Summary Reports.
This will allow the user to quickly find what they need and help avoid
overwhelming users with data by hiding the detail section so they are still able
to drill down. This will help out
because only the records that the end user is interested in are brought back.
Secondly, if you are using subreports to return data to your main report,
make it an on demand subreport. For
a similar reason as a Summary report this allows the end user to find the data
they want and then click on the subreport to return the data.
This avoids from having to run the subreport for each and every summary
record thus saving time. Lastly,
link tables if possible instead of linking subreports.
This alleviates from having to run another report since each subreport
runs as a separate report and you can often have a performance advantage by
linking tables through the visual linking expert.
Now while
there are other ways to make reports run faster these are techniques and tools
that will apply to most reports.
Go to Top |
Return to Newsletter Contents
|
