Architecture for crystal report

Question

All,

Architecture for crystal report,

I have two option,

1. Stored procedure with business logic and display data on crystal report.( tight coupled) as SP are specially designed for the reports - less reusable. but recompiled.

2. Views to pull data and add business logic on report itself to filter data.(loose coupled) -reusable views but what about performance compared to SP?

Any suggestions are more welcome...

Answer 1

If I understand your question correctly, I would recommend implementing option 1.

• By calling a stored procedure, you will be reducing network traffic because you'd be passing only parameter definitions and the procedure name, instead of the entire query string you'd be sending to the DB in option 2.

• Using stored procedures also keeps the plan cache tidy by compiling the set of SQL statements within the stored procedure, instead of storing separate plans for each statement within the string you'd be passing to the DB in option 2.