September 19, 2002 at 8:42 am
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?
-SQLBill
September 19, 2002 at 8:48 am
Hmmm I use Crystal against SQL 2000 with no issues in speed and my server is nowhere near as powerfull as yours..!
Couple of idea's...
1) If you execute the queries from CP in QA do they run ok?
2) Set a profiler trace and look out for anything odd.
3) Is it all users or just some?
Clive Strong
September 19, 2002 at 10:56 am
Run some performance monitoring as well.
Steve Jones
September 19, 2002 at 11:38 am
The same query run in QA runs just fine. It doesn't appear to be user related, more query related. Over on another site (with a Crystal Reports forum) I got the possibility that Crystal Reports isn't passing the WHERE information to the database. In other words, the database is providing everything and CR is then performing the WHERE.
So, I have my work cut out trying to figure out all these queries to see how they are operating behind the scenes. But, I figure if I solve the issue with one query - it'll fix all of them.
Any pointers on what exactly I should be tracing and monitoring? How do I tell if SQL is handling the 'conditional' statements (WHERE) or if Crystal Reports is handling them?
-SQLBill
September 19, 2002 at 12:21 pm
profiler shoudl show you what is passed to the server. That will tell you if it is sending the where.
For Perf mon, there is a great e-book for sale here that can walk you through the basics of monitoring.
Steve Jones
September 19, 2002 at 1:26 pm
Thanks Steve. I'll work with Profiler for now. I know about the ebook, I've seen it recommended on this site and the SQL Server Worldwide User's Group (www.sswug.org) site. I keep meaning to buy it and never get around to it. Gotta make the time and get it next time I'm on the computer at home.
-SQLBill
September 26, 2002 at 10:46 am
Why don't you put the query into a SQL Stored Procedure (with parameters if necessary) and call the stored procedure from Crystal Reports. That way you don;t have to worry about the Where clause being performed in Crystal and you get the obviuos advantage of having a compiled procedure instead of ad hoc SQL.
quote:
I got the possibility that CrystalReports isn't passing the WHERE information to the database. In other words, the database is providing everything and CR is then performing the WHERE.
Nigel Moore
======================
September 26, 2002 at 12:57 pm
I am looking into making the queries run as stored procedures. But I still have to answer, to my boss, why Crystal Reports is being so slow. And I don't have full control over how the queries are run...I work at one site and management is at another, they want to be able to run ad hoc queries against the database and get information now, not later. For queries which are run daily and don't change much, yes a stored procedure might work. But it's the rest of the queries and the fact I can't tell the user how to run their queries that one of the problems.
-Bill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply