Solved

Crystal reports load times

Posted on 2012-04-13
10
2,694 Views
Last Modified: 2012-04-18
This is my last attempt before I ditch Crystal Reports altogether...

I've read lots of articles on speeding up load times of cr's which has become a major issue for me recently, so I redesigned my application into two parts because it's not an app that ever 'stays running' so I couldn't just preload a report at startup.

All my app does now is call a WCF windows service (on the same machine) which stays running and does all the processing, this way I can preload a report at startup so that in theory every load after that should be quick.

Unfortunatlely for me this isn't the case, the results I've seen is that I send a request to process a report and it takes just over a minute to process, if I send another request for the same report it takes a couple of seconds (great :) ) problem is I tell it to process a different report and it takes over a minute again!! There are close to 200 different reports it could be loading so it looks like the re-design is a failure after all.

Has anybody come across this before? Does anybody have any suggestions for me or should I just start looking into a different reporting solution other than CR?
0
Comment
Question by:gavsmith
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37843144
I haven't experienced that but then I haven't used applications that call Crystal in a while.

I think you will find that each reporting system has its on set of issues that can cause slow loading.

I have written some reports that take time to load/display the data (15-30 minutes) even from the Crystal designer.  In those reports it was just the time it took to retrieve the data.  the calculations though extensive actually didn't take that long.

I rewrote the report to use Excel as the display/reporting mechanism.  It took just as long to connect to the data sources.  Once data started displaying the calculations didn't take much time.  It was the database connection mechanism that took the time.  SInce the database was QuickBooks there was only one way to connect and the QODBC driver is notoriously slow.  

Speed in Crystal is dependent on many things
1.  Database you are using
    Local or over a network, competition for the database
2.  Network speed and traffic
3.  How much data you are pulling back to Crystal
      Lots of records for the report or is the filtering done in Crystal
4.  WHere filtering, sorting, and grouping is done
     Server tends to be much faster than client
5.  Complexity of the calculations, sorts, groups
6.  Any subreports.
     Each has its own data connection and is opened when needed
7.  Placement of the subreport
     Report header/footer - run once
     Page header/footer - run once per page
     Group header or footer - run once per group
     Details - Run for every record
8.  Speed of the machine
9.  Memory available on the machine
10.  Are the report fiels stored on a shared or network drive
      Consider the contention for the report files and the drive itself
11.  Free drive space on the network and the individual machines
      Fragmentation of the hard drives

There are probably other issues.   If reports take about a minute and it is all reports then I would look at things like the database and connections, the network and traffic levels, contention for the drive the reports are on.

DO you have any simple, small reports - limited data, limited calculations - Do they run just as slowly?

I normally would want to look at the report, the filters, etc but since you imply it is all the reports unless you are doing something that requires all the reports to do the filtering on the client, it probably is less related to the report itself than to the environment.

The enviroment will be the same for all reporting tools you choose, so if you have slow servers or overloaded servers, an overloaded or slow network you won't see much improvement from a new tool.  Plus consider the cost of moving to a new tool.  200 reports is a lot of conversion.

mlmcc
0
 
LVL 10

Author Comment

by:gavsmith
ID: 37843409
You have made me think of something there that may (or may not) be happening.

Do you know if when you load a report in code using CrystalDecisions.CrystalReports.Engine.ReportDocument.Load(pathtofile) does it verify the database connection at that point??

If so it will be failing because I set the connection later in code. I was under the impression that it doesn't get anything from the database unless you actually do something with the report (print it/export it/show it in a viewer)

I'll try changing the initial connection to a valid one to see if that makes a difference.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 37843531
I believe it does check the connection.

Another thing to check that I just though of is the printer.  I ask people to upload their reports so I can look at them.  They take additional time to load looking for the printer.  I then get a message about the printer being unavailable and the default printer will be used.

mlmcc
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 18

Expert Comment

by:vasto
ID: 37843609
It is possible to have this issue if the report is set to use non existing server with Integrated security. Also are these reports set to use a specific network printer ? In case a report is set to use a network printer which is not available you may see behavior like that.  

What is your crystal reports version?
Can you confirm that loading is slow for reports WITHOUT subreports ?
0
 
LVL 10

Author Comment

by:gavsmith
ID: 37855428
mlmcc

It was your comment that made me think about the database connection, the report has at design time, being the problem and it appears I'm right if the report has a valid connection on load it's much quicker. it must be verifying it on load.

Is there some other way I can get around this, other than changing all my reports datasources?

I think I will have to change all of the reports to use a system DSN and make sure it's correct on each production server.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37856359
There is an option in the report that you can turn off.  I think it is in the REPORT OPTIONS to verify database on load

Another consideration is the filtering you are doing.  SOme filtering can be passed to the database while other remains as report based thus forcing you to retrieve all the records.
Even some otherwise simple looking filters get done on the client.  If you look at the SQL the report uses when it runs you can see if the filter is passed to the report.

Another idea is if you are filter on joined tables sometimes changing the joins or putting the filter in the join clause can speed up a report.

mlmcc
0
 
LVL 18

Expert Comment

by:vasto
ID: 37856833
When you say "loading" do you mean the time when you select the report file and it is loaded in crystal engine ( for example in the designer) or you mean that the report is already opened, you are trying to start it and it is preparing the parameters ? Are you loading the report programmatically or in Crystal Reports Designer?

It seems strange that the report will make any attempt to connect to the database on loading. Even if the connection is set correctly report will be able to connect successfully just in case connection is using integrated security, which is not always the case.  I have seen things like that with version 8 and 8.5 and the bug was fixed with a service pack. If the problem is a connection issue you most likely will have a record in the event log on the database server machine. Check if this is the case. What is your crystal reports version ?

I will be very surprised if this is a connection issue.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37859168
FWIW, I'm using CR 10 and it's not actually connecting to the db when I load a report in CR 10 (I don't run reports from code, so I can't comment on that).  CR needs a password to connect to the MS SQL db, and when I load a report that uses a stored procedure or table, CR doesn't ask for the password until I actually refresh the report.  OTOH, I suppose it could be checking the connection in some way that doesn't require a password, or other db's or types of connection might work differently, or it might be different when a report is run from another app.

 The "verify" options that I see in CR 10 are for "first refresh", so they presumably only affect the first time you run the report, not when it's loaded, but give them a try.  You never know.  Or maybe your version of CR has different options.

 You could also try "Perform Query Asynchronously", assuming that it's not already checked.  I wouldn't really expect it to make a difference, but you could give it a try.  Just be advised that it can cause problems, depending on the report and how complex your query is.  Using stored procedures, I've found that it keeps CR from "locking up" while it's waiting for the SP to produce data.  But it also caused problems for some reports, I think because some things in the SP (like building and reading temp tables) ended up being done in the wrong order.  But if your problem is some kind of delay waiting for a db connection, maybe that setting will help.

 James
0
 
LVL 18

Accepted Solution

by:
vasto earned 250 total points
ID: 37859253
I just realized that there was a very similar question a while ago:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27492491.html

And  the problem disappear when the connection was fixed, so mlmcc is probably right. However I still don't have reasonable explanation why the report will try to connect to the database on load.

gavsmith, if you are sure that the problem is in the connection, it is not a big deal to fix the reports datasources - there are few tools that can do this for you :
1. http://www.redeemedsoftware.com/Products.asp?pageid=156
2. http://www.softwareforces.com/Products/rpt-inspector-professional-suite-for-crystal-reports
3. http://r-tag.com/ReportDataSource.aspx  - I work for this company.
0
 
LVL 10

Author Comment

by:gavsmith
ID: 37860089
vasto

Yes my problem is exactly the same as the link you posted... odd because I did search for an answer before I asked this question. But then when you search for 'crystal reports slow loading' you get a lot of results to go through!!

Like the other user says, the solution seems to be a 'work around' I believe the Crystal API should not be trying to do anything with the database when you programatically 'Load' a report, it shouldn't touch the database until you want to do something with the report be it export/print/show/refresh etc.

Nevermind I'll stop ranting and start working again, thanks for the help
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wav problem 4 27
send messages to whatsapp programatically 2 49
Cant save 3D 4 21
Latest thoughts on C# utilizing Mono on Linux vs .NET on Windows? 5 29
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question