Solved

Crystal reports load times

Posted on 2012-04-13
10
2,651 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now