SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Separation of interface from implementation

One of the supposed advantages of the EF is that it abstracts you from your database implementation. This is considered to be a good thing. It lets me move between different database products (SQL Server, Oracle, MySQL, etc) without having to rewrite anything the business tier of my application. I just need to hook up a different provider to the entity framework and I'm done. All of my business logic and query construction stays at the application layer.

Ironically, this paradigm, which comes from the object oriented world, fails to recognize one of the most basic tenents of object oriented design. The separation of interface from implementation!

First of all, I'd better point out the reason for this post. Why is it a bad thing to put the query construction into automated tools like the entity framework or nHibernate, or into the hands of the application layer more generally?

The short answer is performance. Databases aren't given much respect in many organizations. It's just a place to persist information, right? No, of course not, that's completely wrong. A database is an extremely complex system of physical constructs like indexes, partitions, columns composed of data types, keys, etc. SQL databases also have a standardized (though vendor extended) language for data retrieval and modification. This, of course, is SQL.

SQL is not a procedural language. SQL is a declarative language. Procedural programming fu do not translate to SQL-fu, and vice versa.

A person focused on database development generally knows at least a little something about database design, indexes, partitions, keys, and of course SQL. There's a lot to know. I've been doing it for 7 years and still learn new and complex technical details about SQL Server every week, if not every day. A person focused on database development also, as a rule, knows a little something about SQL.

A procedural programmer, as a rule does not. Or at least, obviously someone who has spent 10 years mostly focused on, say, .NET won't know as much about it as someone who has spent the same amount of time focused on, say, SQL Server. The people who wrote an object-relational mapping tool might know a little, but they don't necessarily. And more importantly, the tool certainly doesn't know anything about your specific indexes, partitions, performance requirements, storage system, etc.

Long story short, this means that the ability to write sensible and efficient SQL for your specific database will only ever be possible by someone with knowledge of the logical and physical structure of that database, and never by an automated tool, or a tool which translates one language (like LINQ) to another (like SQL).

Now here's the key to this post. An ORM layer which dictates certain database requirements and builds your queries for you is COMPLETELY UNNECESSARY in order to achieve the goal of database vendor independence. The belief that it accomplishes this disregards the separation of interface from implementation.

Your database developers can, and should, do this for you by providing the application code with an API. From an OOAD point of view, this is simply the separation of public methods from private data, and has been around FOREVER. How do we accomplish this? With stored procedures, or routines. If we switch vendors the only thing we have to do is ensure that the database provides the same set of routines to the application layer. The code inside those methods is irrelevant to the API itself. I can build a GetCustomer routine (stored proc) in SQL. If a decision is made to move to Oracle, all that matters is that the GetCustomer routine is provided, taking the same parameters and returning the same results. The fact that internally it is built using PL/SQL instead of T-SQL is completely hidden from the application layer.

This does mean that the code INSIDE the routines may have to be rewritten. I say may because if standards compliant SQL is used, it doesn't. You can literally do a copy and paste. But the thing is, you probably want to rewrite that code anyway. Why? For the reasons I discussed above. The performance of your queries depends extremely heavily on your logical data model, your physical database structures, and requires a deep knowledge of same. That's precisely the problem with automated query generation tools... they don't. Your performance can be orders of magnitude worse if you don't tune your queries based on your structures, and vice versa.

Need a concrete example? What about nHibernate. GUID's are usually needed for object creation, so each object gets it's own unique GUID which is then used for lookup. Using GUIDs in a database has the potential for MASSIVE performance loss. I'm talking orders of magnitude on query times, as well as vastly (VASTLY) increasing the physical size of the database on disk. Why? The reasons are complex, which is exactly the point. Care in how they are used is required to avoid this. But do the application programmers know the pitfalls? No, they're application programmers! They don't have to know anything about databases, that's what nHibernate is for, right?

Don Halloran

SQL Server, SSIS, SSAS and maybe even SSRS rants and raves (and, on occasion, useful code or designs).


Posted by Anonymous on 11 July 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Separation of interface from implementation - Don Halloran         [sqlservercentral.com]        on Topsy.com

Leave a Comment

Please register or log in to leave a comment.