Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal Reports and SQL Server - Speed issue 1

Status
Not open for further replies.

SQLBill

MIS
May 29, 2001
7,777
US
Using: MS SQL Server 2000
MS Windows Advanced Server 2000
2-node cluster
300+ GB harddrive space
4 processers
4 GB RAM
Crystal Reports 8.5

My users are letting me know that Crystal Reports is taking a long time to return results from the database. What can I do to shorten the amount of time it takes for CR to get a report completed? Is there a better front end reporting tool?

(Cross posted to MS SQL Server forum)

-SQLBill
 
The most important step for Crystal to run quickly is to make certain that selection criteria get sent to the database. To be sure that it is, go to database -> show SQL query and inspect the SQL that is being sent. If all your criteria isn't there, then that is the first problem to solve. If it isn't there then the DB is sending a LARGE dataset to crystal, and Crystal is discarding the unwanted rows. Crystal isn't very good at that, let along the unecessary large I/O on the DB. There are a number of tricks to getting Crystal to pass the criteria, if you do a search I am sure you can find several good posts about this (synapsevampire has a good list).

Once you get beyond that, the only other thing that you can do is optimize the SQL. On the rare occasions that I couldn't optimize withing Crystal (usually because I needed to send hints) a stored procedure removed that problem.

Crystal is very good at formating and displaying data. As long as you can get the DB to do the DB work and let crystal just handle the display part, it is a pretty good tool.

Lisa
 
Thanks. Will check it out, but I do believe all the selection criteria is being done by the database.

-SQLBill
 
If I find that Crystal Reports isn't passing the conditional statement(s) (Where clause) to the database, how do I 'force' it to do so?

-SQLBill
 
this thread has several suggestions.

thread149-304644
 
Oh.. of course if that doesn't help.. you can always post your select statement here.. someone usually can figure it out.

Lisa
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top