Have Geek Brain

Crystal Reports - very large database, very long processing time

I really don't understand how to do this. I have a very large database and the table I am accessing has approx. 600,000 entries. This database is accessed by an accounting application that provides a report to an SQL query through which the report accesses the database.

My report has an associated subheading that contains constraints that are placed in the title of the report. When this report is run, the average refresh time using a very basic query is 36 minutes. Adding two more items to the query takes 2.5 hours to report.

Here's what I've tried:

  • clear the report only leaving items absolutely necessary - no difference
  • removed most of the formulas (deleting the rest of the formulas does not have a time difference)
  • tried to edit a SQL query - was not allowed due to accounting application
  • tried debug report and main report - didn't work
  • added other groupings - no difference
  • deleted groups - no difference
  • checked all servers due to lack of disk space - not a problem
  • tried "on request" subreport - no change
  • checked (discrete or range) and that's how it should be
  • tried breaking indexes, grouping on the server, etc. - no difference
  • 2 passes are required to report. I tried translating it one pass with no success.

There must be something I am missing.

There seems to be no other change in the report using the usual crystal functions. Is there a way to speed up data access without having to go through all 600,000 records? The SQL query that accesses this data is long and has many queries. This is not something I can change.

Is it possible to add something (formula?) That invalidates these requests? I'm getting there now ...

+1


a source to share


4 answers


A couple of things we have had success with adding indexes to databases, and instead of importing tables into a report, we instead keep a stored procedure to get the results we want.



+1


a source


If indexes and stored procedures don't get you where you need to be, you've reached denormalization until it's part of your database life. Perhaps you should look at creating an MI database with tables optimized for your reporting needs; and some data conversion scripts that can pull data from production into your MI database. Depending on what it is oracle / ms, there are tools to help you do this.



+1


a source


We are using Crystal Reports with a billing system and we had database queries taking over 1.5 hours. It doesn't even take into account the rendering / formatting of the reports.

We created Materialized Views and made the client update them every day. A materialized view is basically a database view that stores the returned dataset. The dataset is not updated unless you explicitly inform it about the update.

+1


a source


Do you know what a SQL query is? If so, you can move the report outside of the accounting application and insert the query directly into the team in the database expert. I have had to do this on several occasions with another application that I am working with.

0


a source






More articles:


All Articles