Friday, September 14, 2007

Is ORM and caching ending the reign of the DBA Nazi?

Years ago, when I worked at Sybase, stored procedures were king. Sybase had introduced them, and had shown very convincingly that stored procedures gave you huge advantages, including centralized control of business rules and SQL logic, keeping the data close to the processing, the ability to compile and store the query plan for faster execution, and so on. So we all built apps that invoked stored procedures, rather than calling SQL directly.

It was also my experience that the DBAs ruled the database. They had Absolute Control, and this allowed them to fine-tune the performance of the database. In particular, they didn't let Just Anybody write SQL. That was for the masters. "Tell us what you want, and we'll write a stored procedure for you. And we'll tell you how to interact with the database. (And a box of chocolates would probably help you get it sooner)."

Just around the time I left Sybase, web applications were taking off. I didn't pay attention as much, but I noticed that more and more applications embedding SQL directly. And I wondered what happened to the mantra of stored procedures.

I have also been watching the growth of ORM technologies like JDO and JPA. And I always wondered -- how do all those DBA-zis I knew were out there in the field feel about this? I found it odd that you can't map objects to stored procedures or views, just tables. What happened to centralized control?

Well, Diego Parrilla explains where things are going , including a hilarious imagined dialog between the DBAs, an IT manager and the developer lead.

An excerpt:

IT Manager: Why this [ORM] tool writes crappy SQL?

Development Lead: It does not write poor SQL. It creates simple queries, that's all. We can configure it to create more complex SQL, but sometimes the number of objects explodes and the application run out of memory.

DBAs: Why don't you use the already created Views?

Dev Lead: We cannot map Views to objects.

DBAs: No views!? How am I going to optimize your complex queries?

Dev Lead: Well, we are not going to write complex SQL anymore. The ORM will do.

DBAs: And what about triggers and stored procedures?

Dev Lead: Out. Triggers and ORM does not match very well because it's hard to keep under control the changes performed in the DBMS. And Stored Procedures sucks, we have Java.

IT Manager: So, if there is no views, triggers and stored procedures, the DBAs can set your focus on keeping the system healthy and optimized, but not coding processes. Right?

DBAs: Errr... that's not exactly right...

IT Manager: And we can also transfer all these development tasks to the development team. Right?

I do see these kinds of debates going on the forums from time to time. An old-timer says "you have to use stored procedures to get any performance." But their claims are lost in the noise of all the people building ORM tools and working with data caches. The value of ORM and caching is too great, and stored procedures end up taking a back burner.

One fascinating conclusion Diego makes: as the business logic and data processing (through caching) goes more and more to the application server tier, then the demands on the database are less and less. Maybe it's fine to use basic, good-enough databases.

IT Manager: Should we try an open source database?
DBA: Damn Gavin King...

1 comment:

Diego Parrilla said...

you get the moral of the post: something is going on in the enterprise architectures due to Object Oriented design, ORM and caching strategies in the application layer. And the DBMS could become pure legacy infrastructure soon.