Performance troubleshooting thoughts

[franklin1232]

I am having trouble with a large report and it's run time. It takes almost 2 hours to format 45 pages.

First I verified the SQL server 2000 database configuration. The main report and sub report both use views for data. So I ran both at the same time and used that trace data to fine tune my indexes. Combined with out any filter parameters both views take less than 10 minutes to return data to Query analyzer when run simultaneously.

So I believe the issue is that the report has many formulas and running totals. I believe these are the problem since the first report page will load in about 10 minutes but to move to the next page I have to wait 10 more minutes and the next page is the same thing. Once the first page has completed is the database a factor anymore hasn't crystal already retrieved all the data? Any thoughts on troubleshooting the report field and the perfomace would be appreciated.

Thanks

 

[rosemaryl]

Dear Franklin1232,

Can you provide more information? Crystal reports is a page on demand system, it retrieves the page as demanded. If you go into help and do a search for multi-pass you should find a topic with a chart of what is processed when. I have never found that the displaying of a page after data is retrieved is what takes all the time.

You indicate you have a subreport. Where is the subreport? If the subreport is in a detail section, or group section, the Query is run for the subreport for each "link". If it takes 10 minutes for the first link and 10 minutes for the next link, this is the problem.

When you do a database/show sql query for both reports, does the entire selection criteria appear in the where clause? If not, that is where the work must be done ... to get the data retrieved more efficiently. If the where clause doesn't get just the records that match your criteria, Crystal must select a larger amount of data and then re-examine the records to apply criteria that didn't pass.

Can you post your record selection criteria?

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

The following thread might also be generally helpful: thread149-1276307.

-LB

 

[mocgp]

The subreport IS the problem. There are ways to optimize a main/subreport structure but in the end, a subreport will almost always extend report run times. My questions would be, what is the subreport's placement within the main report?; what links are being used to the subreport?; Do the main and subreports qualify for "Perform Grouping on Server"?

Notice especially rosemaryl's second paragraph about the subreport. You have to multiply the time it takes for the subreport to run by the number of times the main report encounters the subreport link.

You say that both the main and subreports use a view; would it be possible to rewrite the subreport's view in such a way that you could link the main report view directly to the subreport view and eliminate the need for the subreport? That would be the ideal fix; without knowing any more particulars, I can't offer any suggestions for optimizing the subreport.

 

[franklin1232]

Thanks to all who replied. I went back to the database and began working on the queries. What I found was somewhat odd. I typical use the graphically builders when the queries are simple. This time they hosed me. The view was based on a query joined to a sub query that had a TOP 100 function. Somehow the TOP 100 was running in both queries but was not reprsented in the graphical designer. When I tried to remove it using the graphical designer it just kept coming back. So I created a new view after editing the SQL in Notepad. After retuning my indexes the report takes only 3 to 4 minutes to format. Much improved.

Thanks again for the replies