crystal reports running slow ?

All,

I have been tapped to help with fixing a reporting tool. We have a Sql Server database/crystal reports(10) setup. I havent had the chance to look at the tables in the DB yet, but I was told that aggregate tables were used. In my past experience with crystal reports, we used database views to feed crystal reports (in Oracle). I was thinking that I could somehow use views instead of tables and then try to re-index the base tables and compile satitics (if theres such a thing with Sql Server). I was also going to look into bottlenecking and locking (table locking as opposed to row or page locking for the lookup tables...to reduce overhead on the main tables) but, I'm not sure if it'll make a difference since this is just a demo server with no major traffic hitting it yet.

The question is, does anyone have any experience with crystal reports running slow with Sql Server, what should I look out for??

'WaleCrystal reports sucks.

Now that we have gotten the obligatory disparaging remark out of the way, you should push as much processing as possible onto the server, and use Crystal only for displaying the data. This means having Crystal call either a Procedure, View, or Table.

If your tables are pre-aggregated, then that is about as fast as you are going to get, because all the processing work is done during nightly batch processing (usually). That said, if your so-called pre-aggregated tables are actually being created on the fly each time the report is requested, well, that's about the lease efficient method.

With the (possible) exception of data aggregated for specific reports, it is seldom a good idea to reference tables directly. Reference views or (preferably) procedures instead, so that if the database schema needs to be changed you will just need to update your procedures rather than redesigning all your reports.

Make sure the aggregated tables are indexes on any columns used for filtering, and your dataset will be generated very fast. If the reports are still slow, take a close look at your network and figure out how much data is actually being passed back to Crystal. Maybe additional filters are possible.|||I couldn't agree with you more blindman, unfortunately, I don't make the decisions. I like the idea of having Crystal calling stored procedures, so it'll be a simple call and we'll have the procedure take care of any processing. I'll also looking into indexing etc.....|||a stored procedure for every report...sounds like a maintenance nightmare...and also, defeats the purpose of the tool.

you will want to look at all the WHERE clauses in the reports and index all the columns included.

the best solution would be to look into restructuring the data into a reporting friendly format (i.e. star schema).|||We decided to use DTS to create aggregate tables for us. And we added some indexes. The reports have picked up speed.|||A stored procedure for every dataset, not every report. And the result is reduced maintenance. Business logic that would have to be duplicated in every report only has to be implemented in the procedure, and you can be sure that the results of all reports based upon the same procedure will yield compatible results.

The purpose of the Crystal Reports tool is to present data, and that is what it should be used for. The purpose of the Database Server is to store and manipulate the data, and that is what it should be used for.

And Star Schemas are vastly overrated. They are the poor-man's database design.|||I disagree...

business logic should be handled at the meta-data layer for the reporting tool not at the report level. (i.e. one place)

crystal is not just for presentation...it's meant to facilitate business questions to be answered while shielding the user from the complexities of SQL.

star schemas vastly overrated? well, no...it's the best possible reporting structure....and a little thing called OLAP!|||OLAP and Star Schemas are not synonymous. A Star Schema is just a glorified pivot table. For functionality, it is just one step beyond a flatfile.

You can let your business model drive your schema, or you can let your schema drive your business model.|||For functionality, it is just one step beyond a flatfile.

I suppose...other than the functionality of robust and high performance reporting.

You can let your business model drive your schema, or you can let your schema drive your business model.

haha...Copernicus...I love it!! ;-)|||Ok, I admit it. You got me on "Copernicus". What's the reference? His planetary model?