Crystal reports load times

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?
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

gavsmithAuthor Commented:
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.
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.

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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 ?
gavsmithAuthor Commented:

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.
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.

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.
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.

I just realized that there was a very similar question a while ago:

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 :
3.  - I work for this company.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gavsmithAuthor Commented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.