Using Stored Procedures 1

[sylvias]

What are some of the advantages and disadvantages of writing Crystal Reports using stored procedures?

 

[ceesql]

One advantage is speed. Complex formulas can be processed on the server instead of the report.

 

[synapsevampire]

Many complex functions can be performed on the database server by assuring that you have SQL pass through from Crystal and when using SQL Expressions in Crystal as well.

Generally the advantage to an SP is that it has a precompiled execution plan, the gain that this affords is dependent upon many things, there isn't a generic statement that applies.

One of the disadvantages to Stored Procedures is that they don't lend themselves well to Joins, and you lose control over parameters (they come from the SP itself, and are predefined.

If you have a SQL statement, try creating a View from it, and then an SP, if it's a large database, and the SQL takes quite some time to execute, you'll probably find that the View is very close to the same speed as the SP.

Writing good SQL, making sure that you're taking advantage of indexing, assuring that you're not doing full table scans, these are much more important issues than simply using an SP over a View.

-k

 

[Jaime13]

Another disadvantage is that you cannot call another stored procedure within the stored procedure (Oracle). Advantage, query may change but the ref cursor if built on a record can still hold same data