Knowledge Base (Display) Knowledge Base (Display)

Reports datasources: Stored Procedure vs SQL query

Reports datasources: Stored Procedure vs SQL query

This is a very recurrent question in early stages of a project. Should we use direct SQL queries in Birt datasets, or base these datasets on stored procedures instead?

Overview 

Most of the time SQL queries are utilized: easier, agile, more accessible. However you should really consider to use stored procedures when your queries become complex, and/or address large tables.

Stored procedure advantages

A stored procedure is an independant data layer. For example you can safely modify the way your data are extracted, use different aggregate tables and SQL cursors: as long the final output remains unchanged, we can't encounter any side effects in birt reports! 

This modularization has many other advantages. For example, with stored procedures it will be much easier to get help from a very specialized Database Administrator to optimize your data extraction: complex queries are not buried within javascript functions & events. Thus it allows to maximize your team talents!  

Here are other advantages:

  • Execution plan retention and reuse
  • Encapsulation of business rules and policies
  • Sharing of application logic between applications
  • Access to database objects that is both secure and uniform
  • Network bandwidth conservation
  • Enhanced hardware and software capabilities
  • Reduced development cost and increased reliability

Conclusion

The best compromise may be to use direct SQL queries for simple lookup/small tables & prototypes, and stored procedures for large / complex queries.

Tags: architecture developers
Average (0 Votes)
Most Recent