Is joining thru "Command" faster than "in-CR joins"

[mirogak]

Hi,

Crystal Reports XI

I am building a report that is pulling from many large tables and it is taking forever to pull the data. I was thinking I go with other alternatives such as:

1. As-Is : Join the many tables within Crystal Reports's Database Expert function.

2. View : Create one view in the database with only the data that I need. And then have that as a single data source of my report.

3. Add Command : Or basically take the SQL of that (above) view and stick it in the "Add Command" feature of Crystal Reports.

From your past experiences, is there a considerable difference between the above 3 alternatives?

Any hints?

Thanks,

Mirogak

 

[Turkbear]

Hi,
As a general rule, having most processing of a query done at the database is a better idea, since the database engine is usually optimized for this..

The Command object, in effect, creates a temporary view in the database so, in practice, either
#2 or #3 of your alternatives should perform similarly.

To see if there is a 'real' difference, look at the SQL generated by Crystal using each method and run it directly against the database .




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[satinsilhouette]

Write it on the database server making sure you have the filter (where clause) filter out as much data to be sent back to Crystal as possible.

Thanks so much!
satinsilhouette

 

[synapsevampire]

The key to all of this is minimizing local processing (Crystal) and optimizing the data sources on the database side for querying. There are different uses for databases, the more common relational models are OLTP based systems, and are designed for writing to, not querying the database.

Best would probably be to create a table on the database which has only the rows required, for instance if your current table has 10 years worth of data and you are only pulling 1 year of data, a different table with only the 1 year will increase performance.

As for a Command Object vs. a View, that is dependent on many factors, not the least of which is your database type and connectivitiy. Adding indexes to the tables can radically increase performance, but you may find that your database when used with a View doesn't take full advantage of the indexes, or that you should place indexes on the Views (again, database dependent).

You should really get a competent database tuner involved with this process, and don't assume that your dba is, most are not, and certainly if you're going to look for help from report developers you should include the database being used and it's version.

-k

 

[monik22]

Hi, we use to have that problem also. What we do now is create stored procedures and have crystal use them as the datasource.