Converting tables to SQL Commands 1

[GMcNamara]

I have inherited several reports that were created using tables. I could improve the performance of the reports a hundred-fold (maybe) by replacing the tables with SQL Commands. My question is: How can I make the report point to the SQL Command for the fields that are already on the report? Going through each formula, group, and field and replacing the original table name with the new SQL Command name is SO incredibly time consuming and labor intensive. Any suggestions?

I am using Crystal XI with SQL Server.

 

[synapsevampire]

Assuming that you will improve performance at all is optimistic, if a report is well written and passes all of the SQL appropriately it should perform similarly.

If you intend to do advanced aggregates in the Command Object, it should prove faster, however the recordset will be so different that you should expect to have to basically redo everything anyway.

I've heard a few people claim that Command Objects were significantly faster but my testing proved otherwise, in each case they're just passing SQL and the bottleneck is on the database.

Add to this that you're now requiring that your Crystal developers be proficient in SQL.

I would suggest that you do whatever advanced SQL that would make it run faster in a View or SP on the database. A SP will have a precompiled execution plan and should prove the fastest, adn you may be able to create Materialized Views which will be veyr fast as well.

But if you insist upon doing this, I don't know of a clever way to update everything at once unless you can establish a one to one relationship between the Command Object fields and the table fields.

-k

 

[kenhamady]

Unfortunately you can't unless you have set up a repository. With XI this requires that you configure BO Enterprise. Even then you can only move one table to the command. Here are 2 Business Objects Kbase articles on this:

c2012566
c2013530

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/