I was just reading a discussion on a popular networking site about stored procedures. In particular, one software architect thought they should not be used.  His reasoning was that the programming language was designed to handle the database access and it was easier to port his application to different database systems without them.

I was stunned.   But then I thought about it and realized that I shouldn’t be.  More and more of the developers I work with have very little understanding of how a database works.  They use coding tools to bind their application to the database schema and then just call a programing function to do what needs to be done.  Want to update a record in the database?  Call the ADD parameter of the bound schema object.  Don’t worry about what is actually being done behind the scenes.  It does not matter what the DBMS is.  It’s all good.

No, it’s not.  I have looked at some the SQL produced by these interfaces and it can be amazingly bad.  Also, since it is ad hoc SQL, it is not optimized in like stored procedures are.  Plus, if you need to optimize the SQL code, it is next to impossible because the SQL is generated and you have very little control over that.

Also, if your app is not optimized to take advantage of the DBMS it is using, then it already has a strike against it.  I have worked with a major application that advertised that it would work with any of the leading database providers (MS SQL, MySQL, Oracle), just select the correct driver when you install the application.  It did work with MS SQL and Oracle, but it had horrible performance issues and the nightly data loads from our sales system took hours to load less than 100,000 records.  This was directly due to the fact that it created all of the SQL through the application, with no stored procedures and no optimized code.  It was a nightmare.

I am not saying that you should never let the application handle the data access, it can work depending on the size and complexity of the project.  What I am saying is that you have to understand that it can be a major issue and that you have to make an educated decision about how and when you use these tools, and don’t just blindly allow the application to handle all the “low level” database calls.  If you do, don’t be surprised when you are stuck with a slow and frustrating  “one size fits all” product.