Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view or a stored procedure to collect the data. Occasionally I've seen reports use stored proceedures which then use physical tables rather than temporary tables to store and manipulate data sets. In these cases the report output often exists as a table like rpt_ap_vendors or similar, and it may or may not be devoid of data when not in use.

These are always cases where the report is generated on-demand, so this is not a case where a report could be generated once and served multiple times, and there are not multiple reports/stored procedures accessing this data at the same time.

What reason would there be for using physical tables for reports like this? Is there a logical, technical or performance related reason to do so? In generating reports I personally have always used views and stored procedures with temporary tables or better yet derived tables to avoid extra disc reads involving clearing out/deleting a temporary table.

share|improve this question
    
Well, if you point CR to a view (or proc; is that even possible?), wouldn't the database have to redo its work every time the user closed CR by accident, tweaked some settings, or maybe even paginated through some data? –  Nick Chammas Nov 4 '11 at 15:15
    
With our ERA software's DB the report results aren't cached anyway, the tables are empty except right when the report is run. I'm not as familiar with CR but I'm not seeing a benefit in the ERA reports, our users have to re-run reports if they close them or want to change parameters anyway. As for our home-made reports I'll actually go and check that, I'm not sure if our apps let you reuse the data or if they generate it every time. Either way most reports are once-off so I'm not seeing the benefit unless pagination requires a requery as you suggest –  Ben Brocka Nov 4 '11 at 17:06
    
So for your ERA software: the moment the report is closed the corresponding table is truncated? And for your home-made reports: What do you mean by one-off? Generated only once, ever? Or viewed only once per generation? Are you sure your users view these reports exactly once per generation? What if they forget to look up something, or their browser crashes, or they want to show someone else the report, or or or... –  Nick Chammas Nov 4 '11 at 17:18
add comment

2 Answers 2

up vote 8 down vote accepted

(+) Reasons to create a physical table to store report data:

  • The report data is reusable. I point Crystal Reports or SharePoint to the table and then don't worry about how often or when those tools or my end users access the data. (Well, to an extent, since repeatedly reading a large report table will trash my buffer cache.) I can also maintain a sliding window of old reports for the inevitable requests along the lines of: "Can you generate last year's report again? I can't find the CSV I extracted at the time."

    This is probably the main reason it's set up that way at your site. Crystal Reports may not be smart enough to cache the report data as users paginate through it or change the report's settings. So in the worst case CR is regenerating your report with each of those actions--a costly and time-consuming operation. With the physical table it just re-queries the table as many times as it needs.

  • Setting permissions on the report is easy. You want to see this report? Well, all you need is permission to read the results, NOT generate them. So here, have some read rights on this table in a locked down schema and filegroup/tablespace.

By manually caching the report, you control and isolate the process of generating it. You give your report readers more freedom to act and yourself less to worry about as a DBA.

(-) What you lose with a physical report table:

  • Flexibility. Wanna change the report? Argh, need some DDL changes.
  • Storage space. You're persisting the data on disk, so duh.
share|improve this answer
add comment

At a previous employer, some of the reports would take hours to run. The month-end and quarter-end reports were the worst (8 and 20 hours respectively). By computing the results, and storing them into a permanent table, the user could look at the results of the report at whim without recalculating the numbers on the fly. The process that calculated the reports was able to restart if it was interrupted, so that also helped: in that part of South Florida, there were multiple second-long power outages each day. While the company had a generator on-site for the bad weather days, not all of the staff had UPSes.

Some of the "power users" wanted to be able to access the data and crunch the numbers in Excel, so they had read-only access to the reporting tables.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.