Move SQL Server Execution Plans to the middle tier

Most Enterprise developers are familiar with n-tier development. Over the years there has been some argument as to where you put certain logic.

In n-tier development, there is usually a UI layer for layout, a UI-oriented Business layer for UI-oriented validation logic, a Data-oriented Business layer for business rules, and a Data layer. However, it’s not that simple.

You see, conventional Enterprise development says that business logic should be in the business layer(s), but then it turns around and says that if you need a particular piece of business logic to be more performant, then it should be re-written as a stored procedure.

Rewriting business logic in the database stored proc would be undoubtedly faster. However, the very idea that business logic is now turning up in the database is conceptually wrong.

Lets take it a step further. Say you want every piece of data-oriented business logic to be performant. Suddenly, every routine that previously existed in a the middle-tier business layer is now in the database. So that middle-tier now provides just a pass-through to the routines in the database.

This strategy actually works, and works well. I have seen a number of medium sized systems that are implemented this way. There are actually many benefits in coding this way. You can now write a patch script to change business logic that is transactional. To get an application upgraded generally requires more bureaucracy than writing a patch script for a few stored procedures. Again, this is not a catch-all, and I wouldn’t write every system this way. As always, there are many ways to skin a cat.

A major benefit of stored procedures, and why when they are written well they are so fast, is that when they are compiled, they produce an execution plan. The stored procedure knows which indexes to use and exactly where to get the data from. The downside is that not everyone has experience writing stored procedures, and maybe they shouldn’t have to.

What this all comes down to for me is this. Every developer that works in the data-oriented business layer should need to know at least SQL. Stored procedures themselves are just routines, and there should be a way to replicate this sort of functionality in the middle-tier.

At this point, the smart people at Microsoft should be able to work out a system to ensure that the execution plans are compiled in the middle tier based on the routines written there. No, I don’t know exactly how they’ll do it – Perhaps they’ll have to originally write some sort of polling/replication to the database to achieve it.

The best place to put this is probably in a hybrid of the new LINQ framework. If LINQ is as good as I think it should be, then it should be relatively easy for companies like Microsoft to plug in an optimised block for compiling execution plans in the middle tier.

If the outcome is better performing applications, then I’m all for it. And if every Enterprise application world-wide suddenly becomes faster and more efficient, with all the business logic in one location, and without developers having to learn anything new, then that is a great thing.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: