Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Formatting a page is taking more time. Post Reply Post New Topic
Author Message
GowthamSen
Groupie
Groupie


Joined: 03 Jun 2009
Location: India
Online Status: Offline
Posts: 65
Quote GowthamSen Replybullet Topic: Formatting a page is taking more time.
     Posted: 12 Jun 2009 at 1:31pm
Hi,
 
I created one crystal report with 6 sub reports.
 
The performance is a bit slow. When I check the performance Information, I got the following statistics.
 
Report Definition
-----------------
 Number of Database Fields:   35   
 Number of Summary Fields:   2   
 Number of Print Time Formulas:   4   
 Number of On-Demand Formulas:   3   
 Number of Shared Variables:   4   
 UFLs in use:   None   
 Number of Subreport Objects:   6   
 Page N of M Used:   Yes   
 File Format Schema:   11.0.0   
 
Saved Data
----------
 Number of Data Sources:   1   
 Unicode Saved Data:   Yes   
 Total Number of Records:   30   
 Selected Number of Records:   30   
 Recurring Database Record Length:   15 KB  
 Recurring Formula Record Length:   9 bytes  
 Constant Formula Record Length:   0 bytes  
 Size of Saved Records:   451 KB  
 Size of Saved Memo Fields:   0 bytes  
 
Processing
----------
 Grouping Done on Database Server:   No   
 Sorting Done on Database Server:   No   
 Record Selection Done on Database Server:   Yes   
 Require Two Passes:   Yes   
 Require the Total Page Count:   Yes   
 Number of Nodes in Group Tree:   91   
 Number of Summary Values:   0   
 Size of Saved Group Tree:   2 KB  
 Built the Group Tree in Progress:   No   
 
 
Performance Timing
------------------
 Open Document:   0 ms  
 Run the Database Query:   32 ms  
 Read Database Records:   18 ms  
 Format First Page:   303 ms  
 Number of pages formatted:   2   
 Average time to format a page:   151 ms  
 Number of page starts generated:   10   
 Average time to generate a page start:   32 ms  
 
Here when I see the performance timing section, the format first page is taking more time almost 10 times more the time it takes to retrieve the database query.
 
Is there any way, we can improve this performance?
how do we find out bottlenecks by seeing the above status?
 
 
Thank you
Regards,

Gowtham Sen.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 15 Jun 2009 at 7:35am
subreports are very expensive as far as data reads.  If you have a total of 100 records, your report is going to make 601 hits to the database.  1 for the main report and 100 times for each subreport.  The way to improve performance is to get rid of the subreports.  I create a 'super' table in my stored procs.  This table has all the information for that line, including any subreport information.  I try to get all the information that I want to display in the report in one line of data.  Let SQL do the work, it is what it is good at.  Then when Crystal gets the data, all that it needs to do is format it.
 
If you aren't using stored procs...you might try to reformat the report to have fewer hits to the database (fewer subreports).
 
Personally, I recommend that every report run from a stored proc, except for a table dump...but those aren't very useful reports.
 
HTH
IP IP Logged
GowthamSen
Groupie
Groupie


Joined: 03 Jun 2009
Location: India
Online Status: Offline
Posts: 65
Quote GowthamSen Replybullet Posted: 15 Jun 2009 at 11:07am

Thank you Lockwelle.

Here in this scenario, one subreport is in report header and 4 sub reports are in report footer and one subreport in group header where it is suppressed such a way to print only on first page.

So here each sub report is called only once.
Thank you
Regards,

Gowtham Sen.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 15 Jun 2009 at 11:24am
Hmmm....  I am going out on a limb, since I really don't know the internal workings of Crystal, but I frequently put formulas in sections that are suppressed.  The formula still evaluates, even though it is suppressed, so I am guessing that a subreport will still process even if it is suppressed.
 
Just a thought, since again, I don't know how Crystal works internally, just what I see happening.
 
Still, you have 5 subreports running for each group.  Since they are in group headers and footers, they should execute less frequently, but for your first page, 2 are being called and if the footer is on that page, the other 4 as well.
 
Again, personally, I think it is way simpler to get all the information needed in a stored proc and then let Crystal just format the result, but that is me.
 
HTH
IP IP Logged
GowthamSen
Groupie
Groupie


Joined: 03 Jun 2009
Location: India
Online Status: Offline
Posts: 65
Quote GowthamSen Replybullet Posted: 15 Jun 2009 at 12:02pm
Thanks a lot lockwelle !!Thumbs%20Up
Thank you
Regards,

Gowtham Sen.
IP IP Logged
GowthamSen
Groupie
Groupie


Joined: 03 Jun 2009
Location: India
Online Status: Offline
Posts: 65
Quote GowthamSen Replybullet Posted: 17 Jun 2009 at 3:57pm
Hi,
 
I have one more question. In Report performance verfiication option the time is shown in milli seconds.
 
When I view the report from crstal report editor, it is showing the data without any delay in 1 or 2 seconds.
 
But when I view the report from Infoview or crystal report viewer, the report is taking around 8 to 12 seconds.
 
Am I missing any thing here?


Edited by GowthamSen - 17 Jun 2009 at 3:58pm
Thank you
Regards,

Gowtham Sen.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 18 Jun 2009 at 6:18am
Is that with a refresh of data?  The editor seems to cache the data, which means that all it has to do is format the report, but when you run the report, it has to connect to the database, fetch the data, then format the data, which might be the difference.
 
I don't really know as I have never run performance checks on my reports.  Also, running the verification probably takes some time as well.
IP IP Logged
GowthamSen
Groupie
Groupie


Joined: 03 Jun 2009
Location: India
Online Status: Offline
Posts: 65
Quote GowthamSen Replybullet Posted: 18 Jun 2009 at 7:35am
Hi,
 
Yes, Its while refreshing the data in crystal report editor while viewing print preview.  Its not taking any delay at all here.
 
Even performance verification report also is not taking much time.
 
Only in report viewer it is taking more time.
Thank you
Regards,

Gowtham Sen.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.