best way to get data in crystal reports8.5

[luminous1975]

I have to decide on the architechture to display reports in CR8.5 which have data with lot of calculations in it...i have to get the data from sql 2000 and deploy it on crystal enterprise server.
the above requirement is for windows application...
which wud be fast using visual basic 6.0 or stored procs...

 

[synapsevampire]

An SP on a database should always outrun external code which extracts from the database and then processes, unless the database isn't configured well to suit the load, or the SP is malformed.

SPs have precompiled execution plans, which is why they run faster than conventional Selects and Views (again, in most cases).

There are some nuisances with SPs, such as parameter collection and passing, but for performance considerations alone, go with an SP.

Another benefit of an SP (or a View) is that if the underlying tables change, or the business rules change, you go into the SP and resolve the change and all of the processes will function again.

Also you gain reusability, so that if another tool wants to extract the same data, they just point to the SP, whereas in VB you'd have to set up a COM server or some such as a gateway to your embedded selects...pretty messy and frought with overhead.

-k

 

[Siggy19]

Personally, I generally prefer tightly written Views to Stored Procs...

1. Views can be linked together by Crystal, if necessary.

2. Stored Procs can encourage lazy SQL coding... I have seen many examples where the SP creates and then populates a temporary table which could all be done in a single SQL statement and would be much faster. (Note that there are times when an SP needs to populate a temp table and can do this very efficiently... I am griping about the times when this capability is missused).

3. Stored Procs are more reliant on Parameters, while Views can usually make much better use of WHERE clauses passed from Crystal.

4. The pre-compilation thing is over-rated... for a report the compilation should rarely take more than a minute anyway while the report could reasonably take 30 minutes if it is complex. Meanwhile, the pre-compilation can cause Stored Procs to use inefficient Query Plans because the criteria used in day-to-day running does not always match the criteria used when developing the SP.