Wanting to separate your database access logic into stored procedures in an attempt to wring every ounce of muscle from your DBMS doesn’t sit very well if you think that keeping all of your entity manipulation in one spot is important (ie think OO concepts are important). It seems to me that there are a lot of folks that like stored procedures for the speed advantage. I don’t like them because now if something breaks, I have two places to look, two things to test, maybe two development environments, two steps to saving my changes in source control (oops- my sproc and code got out of synch again), etc. So in order to keep my data access and code together, it would seem logical to use the prepared statement functionality if something was going to get called repeatedly. I think this technique retains most of the speed advantage, yet keeps everything related to the data in one place.
The concept of keeping everything having to do with your data access in one spot (or under a unified dev-time umbrella) is one that sometimes gets lost in technology discussions. With respect to CMP, it’s heart is in the right place, and some day it will get there. Even today, with the right understanding, and the right wizards, it’s sort of okay (not exactly a ringing endorsement, eh?). But there seems to be agreement that it’s hard (impossible?) to be a really good DBA and a really good coder. Why not let the DBA do that they do best, and have the coders stay the heck out of the DBMS-specific layer? If that means we put some better smarts in how a generic SQL is optimized for a specific platform, then we need to push in that direction instead of trying to keep a foot in a world where the door is closing.
Id try to separate it into a number of arguments.
- Keeping all of your entity manipulation in one spot is important (ie think OO concepts are important).
- If something breaks, I have two places to look, two things to test, maybe two development environments
- Two steps to saving my changes in source control (oops- my sproc and code got out of synch again)
- Why not let the DBA do that they do best, and have the coders stay the heck out of the DBMS-specific layer?
Ok, number 1.
You develop your data access layer, which in turn somehow accesses database. This somehow can be direct insert/update/select/delete SQL calls over JDBC, SQLJ, JDO, CMP/BMP or (if you dont use Open SQL) calls to stored procedures. Ok, if youd like to work with DDL, DML statements and database access logic in one IDE, then solution, provided by NetWeaver Developer Studio is your choice with its data dictionary and database abstraction. If you want to use stored procedures, you end up with another development environment for dealing with them. I dont see this as a bad thing. In my point of view, it fits very nice in the picture of a team, where a DBA person works in his/her environment with database schema and stored procedures, and where Java programmers work with their toolset.
Ok, lets clarify, when and how it breaks. If it breaks during development, your salvation is unit tests, layers of unit tests, different kinds of sets of unit tests like smoke, regression, integration, functional. Umph! I think I named nearly all of them 😉 Providing a set of unit tests for testing your data access layer is not that bad practice. In fact, if you regularly perform all possible sets of operations on a clean schema on a separate machine, that would only help you during development lifecycle.With this number of tests, run every day in a daily build, you can easily identify problems and their sources.
I would say, its an issue of configuration management. Imagine the following picture: You have a couple of teams, which develop their different applications, using some database schema, which is the responsibility of the third team. So, you have different release schedules for all these 3, but still, when you release on of the applications, you specify, that it uses and depends on the following release/version of the database schema. And, usually, its a good practice for a DBA person to send a list of changes to the involved team(s), when she/he updates schema of the database in a version control repository and deploys these changes to the database server group is working against.
I also say that! Let DBA control SQL queries people write in their software. Let DBA deal with stored procedures. Let DBA design database schema (not alone of course, because database schema seriously reflects problem domain). All Java programmers would have to do then is to map their domain classes to the database layer using DAOs and data mappers.
You might think of me as a fan of stored procedures. I am not. I am a fan of customer satisfaction, a fan of meeting Service Level Agreement/Requirements.
Stored procedures is the choice that you can make, if you can make it, of course. Unfortunately, requirements often restrict that.
In one of my next posts I’m going to talk about performance/availability/scalability/fault tolerance mechanisms. In general, and, in WebAS context.