What Exactly Is Crystal Doing?

[TomSalvato]

This is more out of curiosity than anything else. I just ran a report that took about an hour to fully process. It uses a sub-report and is going through a heavily populated table ... so the hour seemed about right to me. Here's what's annoying though ...

When it finally stopped processing, and I'm looking at page 1, it's NOT really done, is it? As soon as you go to turn a page it acts like it's processing again ... like it's really THINKING about the paging process. You can see the "Accessing Database" message on the lower left corner flashing madly, flickering between that and "Generating something (that I can't quite make out)".

Do you folks know what I'm talking about? And if so, is there anything I can DO about it, or is it just another Crystal idiosyncrasy that we just live with?

Thanx in advance! -TS

 

[lbass]

If you add Page N of M into the page footer, the entire report will process up front. It forces the subs to process in order to capture the total page count. If you are working in CR X, though, I have seen this cause problems.

-LB

 

[TomSalvato]

Hi LB. I have the 'Page N of M' in the page footer. Are you suggesting that that's what's causing this delay? I'm running 2008, by the way.

 

[TomSalvato]

I should also probably mention that the subreport in question isn't even being displayed. I'm just using it for calculating some shared variables and such. -TS

 

[hilfy]

Yes, page N of M will cause this kind of delay - especially when you have subreports. How often is the subreport processing? Is it for every record or for a group of records? Just using subreports will also cause a delay because the subreport will run its query for every time its displayed as you're changing pages.

So, in essence your subreports are running twice - once to determine how many pages are in the report and again for every time it occurs on a page when you change pages.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

I don't really think the subs are running twice. I was actually proposing Page N of M as a solution as it forces the report to process in full. If you use a regular page number, the report will finish once the subreport on the current page has processed, but then if you try to go to the next page, the report will go into accessing the database mode. With Page N of M, the report will finish the entire process--you will still see the report complete and then repeatedly access the database, but this will occur up front, and when you page through, you don't have to wait for each page.

A separate issue is whether you have optimized the report and determined that the sub is in the correct location for your purpose.

-LB

 

[chelseatech]

Yes they are processing the subreports twice (once on the first build to get the page count, and again when each page is viewed). I've noticed this with CR 2008 (and XI) when processing some of the things I'm doing inside my UFL's.

There are all sorts of ways to speed up a report.

Could you build a summary table so the subreport values are already available (and maybe even join that to the tables in the main report and remove te subreport).

You can also use a SQL expression to extract a value from other tables. We covered that technique to eliminate subreports in Crystal Clear a few years back.

Large Image fields also slow report processing down.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[TomSalvato]

Yeah, my subreport is exactly where it has to be, but the processing makes the report useless. We're going to try and create a stored procedure and see if we can eliminate the need for the sub.

Thanx again for all the input, folks. It is greatly appreciated, as always.

-TS

 

[lbass]

ChelseaTech,

I disagree. The set of subreports will only execute once (or at least once per group/record instance depending upon where it is located) as a result of Page N of M. When the report is first refreshed, if you are using Page N of M, you will see the subreports executing with "Accessing Database" repeatedly flashing. When you page through the report, you will not see the "Accessing Database" again. There may be a slight lag as the page loads (and even that should be minimal), but the subreports are not returning to the database for data--it's already there.

If you do not use Page N of M, then the report will access the database repeatedly as you page through the report--as each subreport is called.

-LB