Using a stored procedures (SP) data access layer is a totally viable approach (see ). While I do not devalue SP’s as the “assembly language of databases”, I personally dislike the language break and generally use stored procedure in special cases only:
- Optimize performance by saving roundtrips.
- Needing specific RDBMS features not exposed by the ADO.NET provider.
The ODP ADO.NET provider even supports Oracle Change Nofitfications and Advanced Queuing.
- Coding very complex SQL.
- Creating data centric fat-server applications.
I generally prefer developers working on complete use cases, implementing them vertically through all layers and have layer specialists for UI and database define standards and perform reviews for quality. For end user centric apps this enforces focusing on user experience and delivering applications with a benefit to users. For background services developers get a feeling for the complete service from the start. It is easier to follow this approach when using one language (family) throughout all layers.
I have no experience with
Using them might be an alternative to SP’s written in T-SQL or PL/SQL.
Some aspects about using stored procedures:
- SP’s are not generally substantially faster than using dynamic SQL from ADO.NET.(*) The execution plan of parametrized(!) SQLCommands is cached.
- Some apps may benefit using Oracle’s result caching in SP’s..
Oracle offers a “Client Result Cache” feature for caching in the application layer too, supported by ODP ADO.NET (see [ProODP]).
I am generally not a fan of caching though.
- SP’s are faster if saving a considerable number of roundtrips.
- You need to save about four roundtrips to compensate for the XML (de)serializing overhead when using SQL Server OpenXML.
- Oracle offers PL/SQL associative arrays and bind arrays to pass parameters in bulk.
- SP’s can reduce network traffic.
- Minimizing roundtrips and reducing network traffic could become more important again with the limited bandwidth and latency of cloud databases.
- SP’s are easier to change on-the-fly.
Don’t forget to source control the changes though.
- SP’s make it easy to share data access code across (heterogeneous) applications.
- SP languages like T-SQL or PL/SQL are better suited for coding complex SQL operations.
- Depending on the development and application requirements, having a DBA completely create, tune, maintain and control a SP layer may or may not be a good approach.
*) BTW: One can write dynamic SQL in SP’s too.
Books & Articles
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions,
Microsoft SQL Server 2008 Internals (Pro – Developer),
Pro ODP.NET for Oracle Database 11g