LKa Viyuda,

I'm confused. The article wasn't written by a MySQL DBA, it was written by a Microsoft SQL Server DBA. As well, I don't believe that all DBAs that use one type of database are ignorant.

"The author’s arguments against constraints for referential integrity and uniqueness are just plain ignorant."

Hey, it's an MySQL user ... what do you expect? :).

There are very good reasons for moving SQL code to stored procedures. The whole conceptual view is one of those... but only in extreme cases (and everyone is right, you'd still end up re-writing all the stored procedures, etc.)

1) Security - because SQL Server has the concept of ownership chaining, you can carefully control how the data is accessed via stored procedures. This means not having to give access to base tables. If the application does pass-through authentication, that means the end user has to have access to the base tables. And that means they can connect via a source other than the application. There are ways around this, such as the use of app roles, however, if you're on SQL Server 2000, this really kills connection pooling. With stored procedures, you can force John Doe to only delete one row at a time. Without, John Doe can connect with Access and delete the whole table with one bad query. You can go the "service account" road fro the app, but that opens the non-repudiation/auditing box up, so there are certainly trade-offs here.

2) Execution Plan Reuse - SQL Server is more lenient when it comes to stored procedures as far as keeping execution plans in cache. In a very busy server, ad hoc queries are going to have their plans flushed a whole lot faster than a stored procedure execution plan. In addition. SQL Server does a whole lot better with parameterization in stored procedures than in ad hoc queries. Just a small change can cause SQL Server to require a new execution plan. In addition, when stored procedures are used, SQL Server is case insensitive with respect to storing the object in the procedure cache. This is NOT the case with ad hoc queries. So if you have SELECT Column1 FROM Table1 and SELECT Column1 FROM TABLE1 being passed by the application, that's two separate execution plans in the procedure cache.

The assertions in that article are too absolute to have any hope of being true.

It is true that many developers lack (and need) more education in RDBMS modeling and SQL language. But it's wrong to believe that if we just keep the OO riffraff out of our precious schema then all problems will be solved.

The argument that using stored procedures protects you from schema changes is a weak argument. It's often true that schema changes are accompanied by changes to application logic, and changes to the interface of the SP. So you end up needing to change application code anyway.

Putting db access code into your application can be a good thing:

- IDE tools are better at application coding than they are at SP coding. Sure there are SP coding tools, but they're less prevalent and I would argue less capable than application coding tools.

- In most projects, application code is more likely than SP code to be managed under source control.

- You can use add query logging and profiling to an application. Some DBMS software does this, but it's typically configured server-wide, not per-application, and the log is written in some central place regardless of what client is executing the query.

Regarding modularity, we can achieve this with proper OO design. If you design your app with better separation of responsibilities, you can reduce the spaghetti code syndrome when you alter your schema. Create a class that models a specific domain in your business logic, and the implementation of this class maps operations to the physical storage model. It's called encapsulation.

The author's arguments against constraints for referential integrity and uniqueness are just plain ignorant. They were not added to protect against mistakes of procedural developers; constraints are a normal part of a physical data model. Simply programming in SP's will not magically protect against bogus data changes, if we do away with the constraints.

I suppose Phil Factor's next article will argue against equipping autos with brakes, because if we accelerate perfectly so that we coast to a stop at the end of our journey, we don't need them. And the real problem is bad drivers who don't know how to manage their vehicle's acceleration in this manner.

I read TFA, and I disagree with most of it.

- It is written with the assumption that the one that manages the database is also the one writing all those SP's. I have never seen that - both in Oracle as well as in the MS SQL projects I have been involved with, DBA's were all about backups, security, allocation of servers for the db etc. and the developers were doing all the coding.

- When using SP's you are essentially moving your code from the application to the DB. Supposedly this would yiuld mode modularity and insulation from schema changes. Well, that's just balony. If you have SP's and the schema changes, you *still* have to rewrite the SP's. And as for more modularity, well, "the application" sounds monolithic but in reality application programming languages offer far more possibilities for modularity then the DB SP dialects. So if your SQL is sprinkled throughout the application, then that's the application programmer's fault. Nothing to do with location of the code, just organization.

- ORM's: one of the main reasons for using those is DB independency. Another one is productivity. There is no need at all to shudder at the code generated by those things. Maybe if you want to get to the utter edge of DB performance you are better of writing your own stuff, but I certainly don't see how SP programming would enter that picture.

- "Checks on foreign keys and uniqueness are excellent in development but are scarcely necessary if creating, updating and deleting are done through well-tested stored procedures. And, they will certainly slow data throughput in a hard-working production system."

So what is he proposing? That checking referential integrity with SP's would somehow be faster than letting the DB handle it? C'mon, I don't believe it for one second - regardless of the DB platform.

There is actually one thing where I agree, and that is that cursors usually aren't necessary (although some DB's can actually handle very large resultsets better through a cursor than through a direct SELECT - unfortunately - MySQL cursors don't work like that)

kind regards,

Roland

I wouldn't necessarily agree that database administrators need to do a lot of heavy coding... It just means that you need some SQL Developers in your shop to do the database development stuff. Production DBAs, Development DBAs, SQL Developers... there are often fine lines or no lines between them.

But applications can often benefit when they have someone who is more focused on the nuances of the database and it's language. Don't get me wrong, I know there are lot of great application developers out there who are excellent with SQL and know a lot about data modeling and architecture. But chances are those folks are even better at something else (otherwise they'd be DBAs first and application developers later). Let them do what they are hired to do and hire someone else do to the heavy lifting with your SQL code.

But I think the real problem is that true SQL Developers might be pretty hard to find.

This approach (everything in stored procs) appears to be typical for many MSSQL apps. The extreme example is building the entire app in T-SQL and just calling the "main" procedure from the "application".