ORM vs SQL part 1 - Paging

In this series of posts I want to talk about the real world ramifications of not using an ORM for application development and why falling back to SQL and stored procedures is a poor choice.

Quite often the argument seems to be that an SQL select and a few joins is simple and effective (which it is), and that ORM's are a useless abstraction. The problem is that simple selects don't get you very far, even when creating the CRUDiest of applications.

In real applications, we have to do paging, sorting (dynamically), joining (dynamically) and filtering (dynamically). That's just on the read side, not even considering what an ORM can do for you when your writing data. I'm going to go through the abstractions an ORM provide and show how they make life easier for us.

Some caveats before I go on:

This part goes over paging, Part Two goes over ordering and Part Three goes over filtering

Paging with an ORM

Here is an example of paging a list with nHibernate, it's simple and it works, I don't even have to bother explaining it

Session.QueryOver<Product>().Skip(20).Take(10)

Paging with SQL

I'm going to assume Sql Server here but it varies from database to database. Before we go down that path I will also say that database independence is not a good reason to use an ORM.

A quick Google search for sql paging will give you quite a number of different possibilities. MySQL, for all it's faults, at least includes the ability to say "LIMIT 20, 10", which means skip 20, take 10. Those of use working with Sql Server aren't so lucky, it blows my mind that such a common scenario wasn't at the forefront of developers minds when Sql Server was first created and they've had to add various hacks over the last decade. Now that we can (hopefully) pretend that Sql Server 2000 no longer exists the best option seems to be:

SELECT TOP (@take) id, name
FROM (
  SELECT id, name, ROW_NUMBER() OVER(ORDER BY name) as rownumber
  FROM products
) as query
WHERE query.rownumber > @skip
ORDER BY query.name

This has good performance, it's relatively simple and it works well with dynamic ordering (check), which I'll get to in part two. This is exactly the sql that nHibernate will generate for you. Another possibility, as of Sql Server 2012 is:

SELECT *
FROM products
ORDER BY name OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY

While still lacking the elegance of MySQL, it works and is faster than the previous example. Throw in dynamic ordering though (part 2) and the performance goes out the window. There are other possibilities I haven't covered, the pros and cons of each is worth it's own blog series, though I won't be the one writing it (if you know of one, let me know and I will link it here).

The dumbest one I've seen, unfortunately the most common also, is to page on the client side (client being the application, not end user). The idea seems to be that you cache entire result sets and filter/order it in application code. People actually think this performs better, but I'll save this for a longer rant later.

That doesn't seem too bad

Well it isn't too bad but it's not such a simple query anymore. This extra complexity goes into nearly every query in the application, so it's a non-trivial amount of extra code to maintain. In later posts we'll see how this complexity is multiplied by other, equally necessary features.

Finally, compare it with the simplicity of doing the same in an ORM, which will execute the exact same query and perform the same, all that extra code starts to look pretty meaningless.

In part two I'll take a look at dynamic ordering.