Thursday, July 24, 2014

8 Biggest Mistakes of Application Architecture

We spend a lot of time arguing of the minutiae of software development. Exceptions or return codes, dynamic or static typing, OO or functional. IMO these have much less to do with the quality of software produced than the architecture, yet we seem to spend less time discussing that. This is the big picture stuff that makes or breaks a product (and it's owners) and in my experience, terrible architecture seems to be the norm in the enterprise world.

The following is a rant about some of the worst anti-patterns I've seen in enterprise software. Each one I have experienced at several companies, though (usually) not all at once. Most of them are cargo cult practices, some can be traced back to official Microsoft guidance, some have no reason at all but just seem to exist anyway.


1. Logical structure != Physical structure


I ran into this one just today. We have two services, a and b, that are different but related yet the share most/all of the same internal libraries. Let's pretend there are good reasons why these services need to be deployed on different machines, because sometimes there are. This does not mean they need to be in separate .csproj files.

It's just as easy, perhaps even easier, to deploy a single application to multiple machines with different configurations.

The physical deployment of an application does not have to be a 1:1 mapping with the solution structure.



2. Development is not Production


On a related note, it is often claimed that a developers machine should resemble production closely. I can't think of anything worse. The purpose of a development environment is to DEVELOP, anything that aids this endeavor is welcome, anything that inhibits it is not.

I want to be developing. I don't care about how many configurations the application will be split into in production. I don't want to manage IIS application pools. I don't want to set up SSL certificates. I don't even want to have to go to the login screen every time I recompile. These are all production issues and you shouldn't have to deal with it unless your configuring a production environment.

A good rule of thumb here is how long it takes a brand new developer to be setup. The answer should be minutes, not hours or days. I actually (very briefly) worked at a company that considered it normal for new developers to spend a week setting up there environment. Companies like this tend to receive the double blow of a week of downtime and a high turnover rate.

It's been 15 years since this appeared at number 2 on the Joel Test. I'm honestly amazed how many companies still screw it up.



3. The .csproj Fetish


There are many good reasons to separate code into different projects. An MVC app and a WCF service might share common functionality in a library project (or maybe not, see 1). But there are just as many good reasons NOT to separate code into a million tiny assemblies.

Recently I came across a solution that had 8 projects just to handle pdf conversion. HtmlToPdfConverter, JpegToPdfConverter, etc. Each project had a single class in a single file. The classes combined were barely long to deserve splitting up, let alone into a separate project each.

All this achieves is to slow down build and debug times. MSBuild slows down dramatically as you increase the number of projects. Try feeding your source tree directly into csc and see just how c# code can be compiled.

Long before IDE's held all our code together things were organized into folders. Fortunately, IDE's are capable if using this technological wizardry, giving us a very simple mechanism of grouping related functionality. Folders exist, use them!



4. Repositories on Repositories


ORMs frequently get a bad wrap for generating n+1 queries, but in the world of enterprise architecture I all to often see a different cause: The Repository Pattern.

ORMs actually come with a repository pattern built in. In nHibernate (which I'll use as my example) the repository is the ISession interface. It's simple and flexible, it can handle nearly every type of query you need and the ones it can't should probably not by performed by an ORM anyway.

An architect or developer will come along and build there own repository on top of this one, but with less features and no flexibility. Just in case you want to change your database one day. How many of you have ever actually seen this happen?

The problem with having data access behind a repository is that it has no context of what data is required for the task at hand. Does it need all the children of that entity? Should it load a parent entity? What should it filter by? It ends with a million permutations of a Find method that the ORM repository is perfectly capable of expressing.

Why does this cause n+1 errors? Because it's abstracted the tool with the best way of resolving them.

Advance Idiots will return a model with lazy loading. Essentially creating an ORM on an ORM.



5. The Service Layer.


This one only applies to web apps.

I'm not talking about a logical service layer here, but a real physical one, usually with WCF as the glue. I think the root cause of this is the same as number 1, architects assuming the the physical structure and the logical structure need to completely match.

Numbers everyone should know are unfortunately numbers that architects don't seem to know. Getting data from RAM is a lot faster than getting it from a network. The service is also likely to make network connections of it's own, usually to a database. The end response time is cumulative, having to make multiple service calls quickly adds up. It won't even show up in a profiler.

The most frequent justification I hear for this is scalability. Having a service layer on a different machine than the web server will somehow, magically, help the application scale in a way that having multiple web servers (maybe even a farm) will not. If the applications weren't spending so much time serializing objects and waiting for synchronous remote procedure calls then maybe they would scale a bit more linearly.

The other justification I hear a lot is that service oriented architectures are considered good architecture. I'll probably save this for another rant, but a service layer and SOA are not synonyms, they are completely unrelated to one another.

Advanced Idiots will combine this with the repository pattern and have n+1 issues spanning multiple network hops.

Super advanced idiots will have services that call services that call services. Displaying a single piece of data to a user can cross a dozen network/process boundaries.

Super amazeball idiots will instantiate a service layer in it's own process every time a method needs to be called on it. Seriously, I've seen this in a production environment handling millions of dollars a month.



6. Replacing new with Resolve


This isn't an attack on IOC containers, I think they're great, but in the wrong hands they are disastrous. The concept is simple enough, that's why every man and his dog have created a toy IOC container, unfortunately a lot of people don't seem to get to the end of tutorial 1.

An IOC container is designed to wire up structural dependencies. If a class name ends with Service, Factory or Handler it is a good candidate to go in the container. If the methods of a class contain adjectives then it is a good candidate to go in the container. If a class name ends with Model, DTO, Request or Response then it probably should NOT go in the container. If a class frequently appears in a list it should probably NOT go in the container.

If an application is full of calls to Resolve<T>() then it was built by someone who doesn't understand the basic concepts of IOC.

Advanced Idiots will combine this with service layers (see number 5). I've actually seen IOC containers used to resolve factories that call web services that resolve POCO objects. I've actually an architect that defended this as being good architecture. I've actually left work at lunch time before the urge to kill became too great...



7. CRUD


So many applications start out life as a simple forms-over-data interface. But using CRUD as the basis for an application architecture is doomed from the start. CRUD is built around the data model and non-developers are horrible at thinking about the data model, they really shouldn't have to.

There is simple crud like active record, but this is about the more advanced CRUD operations. CRUD DAO's, CRUD Repositories, CRUD View Models, CRUD DTO's. So much work for such a limited pattern.

The root of this, of course, is the developers innate desire to generalize common patterns and come up with elaborate use of generics to reduce the amount of code we have to write to solve the problem wrong.

Software should be modeled around users and the actions they perform, not rows in a database.



8. Deployment


The seems to be a rule that the more a company talk about being agile the worse their deployment procedures are. I would argue that being able to deploy frequently (dare I say continuously) is one of the defining features that show your agility as an organization.

If you can not have a bug fix in a customers hands within minutes of bug fixed (excluding testing) then your organization is not agile. Telling users that they have to wait for next months deploy to have a critical issue fixed is not agile. Being unable to put up and tear down test environments on a whim is not agile.

In far to many companies doing a production release is a nerve wracking affair that can consume several days. Number 1, 2 and 5 are the major causes of deployment headaches.



Conclusion


The cause of every one of these issues has been the same, someone with architect in their title. We really should reconsider what the role of an architect is and if the should be involved with technical decisions, or involved at all.

Thursday, March 13, 2014

Android: Take back your builds


So I started getting back into android development, reviving a little app I published a few years ago. The app itself was fairly crappy by android 1.6 standards (get off my lawn!) and now it's just looking terrible.

So I fire up the shiny new android studio but it looks like I'm going to have to start the project from scratch and port everything over. After getting frustrated trying to do the simplest things, like adding a library I went back to Eclipse. Several arcane build errors and several even more arcane eclipse ones later I was getting frustrated. Clearly this isn't going to work.

Aside from the terrible UI's of both IDE's there was something much more important. I wasn't getting any functionality out of them either, they were just getting in my way. I wanted to try the new atom editor but as yet don't have an invite, so I went back to trusty old vim with ant. This is when I discovered androids dirty little secret: the build system.


Monolithic Madness


A build system, as with other aspects of development, work best when you start with discreet parts and assemble them into a whole. I expected to find some ant tasks that wrap android tools and to be able to plug them in and get going.

Alas, google instead gives us some monolithic tools that define your build system for you. Attempting to go deeper give you a "here be dragons" warning. The tools that are at the very center of android development and yet there documentation consists of:

The other platform tools, such as aidl, aapt, dexdump, and dx, are typically called by the Android build tools or Android Development Tools (ADT), so you rarely need to invoke these tools directly. As a general rule, you should rely on the build tools or the ADT plugin to call them as needed.

 I firmly believe that this is at the center of why android IDE's are so lackluster, they have to conform to this monolithic build system. Aside from the "one build to rule them all" approach, building an android app is a very complicated procedure. This isn't "compile and run" like you find in other types of projects, these are the steps to build a basic, runable android application (illustrated here):


  1. Compile layouts, resources, etc into a resource file.
  2. Generate an R.java source file, this is needed just to be able to compile your code.
  3. Compile your real code.
  4. Turn your .class files into .dex files.
  5. Combine your resource file with your .dex files into an .apk.
  6. Sign your .apk.
  7. Finally we get to run!

So to create a build system how you want it, you have to replicate all this with little to no documentation. And this is what I'm about to do.



Step 1 - House Keeping


The first thing we have to do when defining our build is to know where the android sdk, our libraries and everything else is, so in ant we have:

1:  <property name="android.sdk" location="D:\Program Files (x86)\Android\android-studio\sdk" />  
2:  <property name="android.aapt" location="${android.sdk}\build-tools\android-4.4.2\aapt.exe" />  
3:  <property name="android.jar" location="${android.sdk}\platforms\android-19\android.jar" />  
4:  <property name="android.dex" location="${android.sdk}\build-tools\android-4.4.2\dx.bat" />  
5:  <property name="android.adb" location="${android.sdk}\platform-tools\adb.exe" />  
6:  <property name="build.resource" location="build\aapt\resource.jar" />  
7:    
8:  <record name="buildlog.txt" action="start" append="false" />  
9:    
10:  <path id="libPackage">  
11:   <fileset dir="lib\">  
12:    <include name="android-binding-0.45-update.jar" />  
13:    <include name="guice-2.0-no_aop.jar" />  
14:    <include name="roboguice-1.1.2.jar" />  
15:   </fileset>  
16:  </path>  
17:  <path id="libApp">  
18:   <pathelement location="${android.jar}" />  
19:   <path refid="libPackage" />  
20:  </path>  
21:  <path id="libTest">  
22:   <fileset dir="lib\">  
23:   <include name="hamcrest-core-1.3.jar" />  
24:   <include name="junit-4.11.jar" />  
25:  </fileset>  
26:    
27:  <pathconvert property="info.libPackage" refid="libPackage" pathsep=";&#10;" />  
28:  <pathconvert property="info.libApp" refid="libApp" pathsep=";&#10;" />  
29:  <pathconvert property="info.libTest" refid="libTest" pathsep=";&#10;" />  
30:    
31:  <echo message="android.sdk: ${android.sdk}" />  
32:  <echo message="android.aapt: ${android.aapt}" />  
33:  <echo message="android.jar: ${android.jar}" />  
34:  <echo message="android.dex: ${android.dex}" />  
35:  <echo message="build.resource: ${build.resource}" />  
36:    
37:  <echo message="libPackage:" />  
38:  <echo message="${info.libPackage}" />  
39:  <echo message="libApp" />  
40:  <echo message="${info.libApp}" />  
41:  <echo message="libTest" />  
42:  <echo message="${info.libTest}" />  
43:    

It might seem a bit verbose but having everything defined here and having the actual values in the output will make debugging a lot simpler. libPackage are the bare minimum of libraries that we need to deploy with our app. libApp is the superset of these that we need to compile, basically libPackage + android.jar. libTest is a superset of that which includes libraries like junit.

Our first two real targets are the clean and init ones:

1:  <target name="clean">   
2:   <delete dir="build" />   
3:  </target>   
4:    
5:    
6:  <target name="init">   
7:   <tstamp/>   
8:   <mkdir dir="${build}"/>   
9:   <mkdir dir="${build}\aapt\"/>   
10:   <mkdir dir="${build}\javac\app"/>   
11:   <mkdir dir="${build}\javac\test"/>   
12:   <mkdir dir="${build}\dex"/>   
13:  </target>  

Step 2: Compiling


Next in line is compiling our project:

1:  <target name="build" depends="init" >   
2:   <exec executable="${android.aapt}" failonerror="true">   
3:    <arg value="package" />   
4:    <arg value="-f" />   
5:    <arg value="-v" />   
6:    <arg value="-M" />   
7:    <arg path="src\app\AndroidManifest.xml" />   
8:    <arg value="-A" />   
9:    <arg path="src\app\assets" />   
10:    <arg value="-I" />   
11:    <arg path="${android.jar}" />   
12:    <arg value="-m" />   
13:    <arg value="-J" />   
14:    <arg path="build\aapt\" />   
15:    <arg value="-F" />   
16:    <arg path="${build.resource}" />   
17:    <arg value="-S" />   
18:    <arg path="src\app\res" />   
19:    <arg value="--rename-manifest-package" />   
20:    <arg value="my.new.package.name" />   
21:   </exec>   
22:    
23:    
24:   <javac destdir="build\javac\app" includeantruntime="false" classpathref="libApp" >   
25:    <src path="src\app\" />   
26:    <src path="build\aapt\com" />   
27:   </javac>   
28:    
29:    
30:   <javac destdir="build\javac\test" includeantruntime="false" classpathref="libTest" >   
31:    <src path="src\test\" />   
32:    <classpath>   
33:     <pathelement location="build\javac\app"/>   
34:     <path refid="libTest"/>   
35:    </classpath>   
36:   </javac>   
37:    
38:  </target>  



The first and most unfamiliar one is where we invoke the aapt tool. I honestly don't know what half of these options are because it's so poorly documented but the important ones are:

  • -M The location of your android manifest file.
  • -I The location of the android.jar that your using.
  • -J The location of the R.java file (needed to compile your real code).
  • -F The location of the generated resource.jar.
  • -S The location of your resource files.
The first javac compiles our actual application, referencing the libApp libraries. The source files used are src\app (Application code) and build\aapt\com (R.java). The second compiles our unit tests, making sure to add our application .class files to the classpath.


Step 3: Testing


This is a very generic task that runs our unit tests. For java developers it should look fairly standard, just run junit with our compiled .class files and libTest libraries in the classpath:

1:  <target name="test" depends="init, build" >   
2:   <junit haltonerror="true" haltonfailure="false" enableTestListenerEvents="true">   
3:    <classpath >   
4:     <pathelement location="build\javac\app"/>   
5:     <pathelement location="build\javac\test"/>   
6:     <path refid="libTest"/>   
7:    </classpath>   
8:    <batchtest>   
9:     <fileset dir="build\javac\test" includes="**/*.class" />   
10:     <formatter type="plain" usefile="false"/>   
11:    </batchtest>   
12:   </junit>   
13:  </target>  


At this point we've create our standard "build". It is completely independent of the IDE. with VI for example, I simply type :mak<enter> and all my tests are run in approximately 4 seconds for fast feedback.

Unfortunately unit tests aren't enough and we will want to actually run our app. In the next article I'll cover packaging, signing and deploying. 









Saturday, December 28, 2013

Creating an MVC framework part 2 - First Decisions and Code

In writing the first bit of code a quickly came across a few key decisions. The very first one was what sort of routing I wanted to include. This lead to a deeper question of what sort of applications I wanted the framework to be suitable for.

My career has largely been spent creating line of business applications and that is the priority focus of the framework. Eventually I would like to expand on that but with limited time you have to simplify or get no where. I also didn't intend for it to be particularly opinionated but I'm naturally getting pushed that way. Again I hope to make it more flexible in future but for now it's simply not a priority.

The first casualty of this is routing, otherwise known as pretty URL's. This feature is simply not that important in a line of business app and it simplifies things to not have to worry about it at this point. For now, the only route is /{controller}/{action}/{querystring}.

The other big decision I made is project structure. I'm sick of having various parts of an application strewn about the project, or several projects, based on type. The grouping of this framework is going to be done by function. A standard project layout will be along the lines of:


  1. MvcProject
  2. -\_Common
  3. ---_Layout.cshtml
  4. -\Product
  5. ---_Controller.cs
  6. ---List.js
  7. ---List.cshtml


Let there be Light


So I created a new web application and in the global.asax.cs I put:


  1. protected void Application_Start(object sender, EventArgs e) {
  2.   RouteTable.Routes.Add(new Route("{*url}", new RequestHandler()));
  3. }
This tells ASP that we want to handle all incoming URL's with our request handler, which is all I want for now. RequestHandler looks like this:

  1. public class RequestHandler : IRouteHandler, IHttpHandler {
  2.   IHttpHandler IRouteHandler.GetHttpHandler(RequestContext requestContext) { return this; }
  3.   bool IHttpHandler.IsReusable { get { return true; } }

  4.   void IHttpHandler.ProcessRequest(HttpContext context) {
  5.     context.Response.Write("Hello World!");
  6.   }
  7. }
IRouteHandler and IHttpHandler are the interfaces we need to implement to recieve requests from ASP. I'm not sure if it's a good idea to combine them or not but it works for now. The real meat and potatoes starts with ProcessRequest though, it might not look like much but this is the entry point to our framework. This is where the adventure begins.

Wednesday, December 18, 2013

Creating an MVC framework part 1 - Why???

So I was working on an application framework, something that would take some of the drudgery out of creating a new application. I like things to be nicely structured, but setting up this architecture takes time. Usually by the time I've set it up that urge to solve the original problem had waned and my projects folder was filled with yet another orphan.

I just wanted to solve the most common things. Validation, logging, permissions are the obvious ones, I find the implementation of these in microsofts MVC framework to be pretty terrible, it's almost always one of the first things I replace. I like the command/query model, so I wanted that in place. I want to be able to offer desktop/mobile integration further down the line. I want easy integration with knockoutjs. Most importantly, I don't want to have to set all this up every single time.

So as I'm sporadically putting various parts together I noticed one thing. MS MVC is getting in my way and stopping me from creating this framework, It must go! So what was once going to be an application framework is now going to be MVC framework as well.

A touch of arrogance


I think if your going to replace something then you need to replace it with something better, which requires at least a modest level of arrogance. You have to know how what you want to do better and hopefully have a rough idea on how to get there.

As I got further through my exploratory and brainstorming phase I started to get more confident that yes, I can make a better MVC.

Controllers


The first thing I started looking at is what I wanted my controllers to look like. I came up with this:


  1. public abstract class Controller {

  2.   [Route("/product/view/*/{Id}")]
  3.   public abstract object View(ProductDetailsQuery query);

  4.   public abstract object List(ProductListQuery query);

  5.   [Roles("ProductManager")]
  6.   [HttpPost]
  7.   public object Create(ProductCreateCommand command) {
  8.     System.Execute(command);
  9.     User.Message(MessageType.Success, "Product has been created");
  10.     return User.Redirect("/Product/List");
  11.   }
  12. }

There are a number of interesting things here, First of all, the controller is responsible for defining a public API, so routes and permissions are defined by attributes. MS MVC has recently added attribute based routing as well.

A permissions system will be provided by the framework that will allow role based and finer grained permissions.

The controller is abstract. For many common actions like view and list in the above sample, all the controller does is delegate responsibility. Abstract actions will have the ability to be routed by default. Saving those couple of lines of code was one of the main drivers for my framework, yes I'm that anal.

The HttpPost may seem fairly standard, but by convention this will enlist a transaction.

All action return objects. There are no view results, json results or anything like that. It's one object in, one object out. The format of the result is determined by the caller.

We don't have to check if ModelState.IsValid on *every single function*. That is done for us.

It's just called Controller. I will mention the project layout at a later date.

There are a couple of big interfaces in there, User and System. These are basically wrappers for real objects. I really like this abstraction because it makes it explicit who where interacting with. Where not interacting with NotificationSubSystem, where interacting with the user. We aren't interacting with the command executor, we are interacting with the system.

Fingers Crossed


This is the current plan any way. Some ideas may turn out to be bad ones, some might be brilliant and there could be many more additions. Finding out which is which should be a fun journey.












Sunday, December 15, 2013

ORM vs SQL Part 3: Filtering

The final of the 3 basic scenarios that nearly every application will need is filtering, the where clause in an sql statement. An application will have to display a list of products in a category, filter a list of users by email address, get an order by it's id or something along those lines. It's practically impossible to write an application with out it.

The most interesting thing about filtering is that it's rather simple to write a query that can handle all your scenarios. The problem is that it renders all your carefully planned indexes irrelevant or worse.

Sql server can be quite fickle about using indexes, depending on the cardinality, execution plan and phase of the moon. I'll show just how stupid it can sometimes be.

Filtering with an ORM


Once again we can build our dynamic query with an ORM quite simply:


  1. var query = Session.QueryOver<User>();
  2. if (string.IsNullOrEmpty(filterId))
  3.   query = query.Where(x => x.Id == filterId);
  4. if (string.IsNullOrEmpty(filterLastName))
  5.   query = query.Where(Restrictions.On<User>(x => x.LastName).IsLike(filterLastName));
  6. if (string.IsNullOrEmpty(filterEmail))
  7.   query = query.Where(Restrictions.On<User>(x => x.Email).IsLike(filterEmail));
  8. var result = query.List();
Once again, our ORM builds a prepared statement for every unique situation which can make use of the appropriate indexes if we have any. It's not neccessarily fast, that will depend on your performance tuning, but it's as fast as it can be.

Filtering with Sql


Dynamically filtering with sql is also quite simple. To get the same functionality as above:

  1. select TOP(20) id, lastName, firstName, email
  2. from users 
  3. where --lastName = '21250A22-6B3F-452C-9FE3-7EBBF216B13C'
  4.   (@id is null OR @id = @id)
  5.   AND
  6.   (@lastName is null or lastName Like @lastName + '%')
  7.   AND
  8.   (@email is null or email LIKE @email + '%')
This is much more elegeant than our solutions in the past two posts. The first problem is, what if you want some other logic, if you want to search by last name or email then this won't work. You'll either need something far more elaborate or you'll need several identical queries. The other problem is that it may not use an index, or even worse, will use the wrong index.

What index will this use?


Well the answer is it depends on a lot of things. The first query, collected statistics and moon phases being the primary influences. I don't know about you, but I like my systems to perform consistently. Assuming the above query is in a stored procedure:

  1. execute the procedure with @id=null, @lastName='abc', @email=null
  2. Query finishes in ~280ms. The index on lastName was used
  3. execute the procedure with @id=null, @lastName=null, @email='abc'
  4. Query finishes in ~11000ms, still using the index on lastName, not the index on email
  5. execute the procedure with @id='C15BAFAA-B772-4863-8849-00413B531C29', @lastName=null, @email=null
  6. Query finishes in ~11000ms, still using the index on lastName, not the primary key index
  7. execute the procedure with @id=null, @lastName=null, @email=null
  8. Query finishes in ~12000ms, still using the index on lastName
Deleting our indexes and repeating the queries in the same order takes ~50ms, ~60ms, ~20ms, 20ms.

So a query like this will not only render many of your indexes useless but can cause the wrong index to be used, which is much worse. Again the tailor made query that an ORM will generate will have much better performance than a generic one that you can create. 












Tuesday, December 10, 2013

ORM vs SQL Part 2: Ordering

Most applications will need to to control the ordering of the data being presented to users, the exact requirements will differ depending on the application and situation, but virtually all will require it. Sometimes the same data will presented in several places, each requiring it's own order for natural reasons. Other times dynamic tables will be presented and users will control what order the view information in. Some even allow full control and let the users specify multiple orders.

In the later two cases ordering usually goes hand in hand with filtering, but I'm going to leave the filtering until part three. In part one I looked at paging.


Ordering with an ORM


Simple ordering is straight forward with an ORM (and with sql for that matter):

  1. var users = session.QueryOver<User>().OrderBy(x => x.LastName).Asc.List();
In fact, at this level, sql is even simpler. An ORM starts to shine when you have some other logic controlling the ordering because you get a composable query object to work with, for example:
  1. var query = session.QueryOver<User>();
  2. if (orderByLastName == true)
  3.   query = query.OrderBy(x=>x.LastName).Asc;
  4. if (orderByLastLogin == true)
  5.   query = query.OrderBy(x=>x.LastLogin).Asc;
By composing queries like this, any ordering that can be logically described can be performed, your only limit is your own code and how much power and flexibility you want to give the end users.

The ORM will generate the sql needed for that individual scenario and make full use of our indexes.


Ordering with Sql


Simple ordering in sql is extremely straight forward:

  1. select TOP 10 * from users order by lastName

Most importantly, this will use the index on lastName. On my computer this will execute in ~5ms with 1,000,000 rows. This is essentially what our ORM will generate. 

Now, let's say our application is showing a list of users in two places, the first we are trying to find users by last name and the second we want to see who were the latest to login. We can write two stored procedures, which will be identical apart from the order by clause. Or we can add some dynamic ordering to the query:

  1. SELECT TOP 10 id, lastName, firstName
  2. FROM users
  3. order by CASE
  4.   WHEN @OrderBy = 'LastNameAsc' THEN lastName
  5.   WHEN @OrderBy = 'FirstName' THEN firstName
  6. END
The drawbacks for this are that the data types have to be the same in the case statement, so we can't use the same query to order by lastLogin. Likewise, we can't mix ascending and descending. And even if we are ordering on lastName, which has an index in this scenario, the index will not get used. It also performs quite badly, 753ms  which is more than two orders of magnitude slower for the same functionality.

In part one I said the new 2012 offset/next syntax performed quite slowly with dynamic ordering, this is why.


Ordering with Sql - Again


So being unable to mix datatypes is a deal breaker in our scenario. we want to order by either lastName or lastLogin. The easiest way to do this is to invite our old friend from part one, rownumber: 

  1. SELECT TOP 10 id, lastName, firstName, CASE
  2.   WHEN @OrderBy = 'LastNameAsc' THEN ROW_NUMBER() OVER(ORDER BY lastName)
  3.   WHEN @OrderBy = 'LastLoginDesc' THEN ROW_NUMBER() OVER(ORDER BY lastLogin desc)
  4. END  as rownumber 
  5. FROM users
  6. order by rownumber
Finally, we can order by multiple data types and by ascending or descending order. But it comes at a hefty price, it's yet another order of magnitude slower, ~5800ms, way beyond what I would consider acceptable.


Ordering and Paging


So now let's look at the combination of paging and ordering:
  1. SELECT TOP(@take) id, lastName, firstName, email, lastLogin 
  2. FROM (
  3.   SELECT id, lastName, firstName, email, lastLogin, CASE
  4.     WHEN @OrderBy = 'LastNameAsc' THEN ROW_NUMBER() OVER(ORDER BY lastName)
  5.     WHEN @OrderBy = 'LastLoginDesc' THEN ROW_NUMBER() OVER(ORDER BY lastLogin desc)
  6.   END  as rownumber 
  7.   FROM users
  8. ) as query 
  9. WHERE query.rownumber > @skip
  10. ORDER BY rownumber

Our simply little select isn't so simple any more and the performance has suffered terribly (~8500ms), to the point where it is immediately noticeable to end users. All because you thought ORM's were too slow.

We are quickly approaching the limits of what we can do with sql. The ORM can easily handle ordering over multiple columns dynamically, to do the same with sql would require a fundamentally different, and probably much more complex, strategy.


Why are ORM's faster?


In this scenario our ORM is 3 orders of magnitude faster than a stored procedure with the same functionality. This is simply because it generates queries dynamically, it doesn't have to account for every possibility at programming time because it can adjust at run time instead.

Sql, despite frequently being championed as faster, can not even make use of indexes appropriately as soon as you throw a real world situation at it.

The final score, with paging and ordering is ORM: ~10ms, Sql: ~8500ms.

Next up, I'll take a look at dynamic filtering.




















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:

  1. I'm talking about general application development, web, desktop or mobile doesn't really matter here.
  2. I'm talking about general line of business applications. If your working on something more exotic you may have legitimate needs that an ORM doesn't cater for.
  3. The ORM I'm using will be nHibernate, but the same applies to all ORM's.
  4. I'll mostly be talking about Sql Server.
  5. Stored procedures have there place, if your ORM creating a legitimate bottleneck you should feel free to work around it entirely.


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:


  1. 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 wan'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:


  1. SELECT TOP (@take) id, name 
  2. FROM (
  3. SELECT 
  4. id, name, ROW_NUMBER() OVER(ORDER BY name) as rownumber 
  5. FROM products) as query 
  6. WHERE query.rownumber > @skip 
  7. ORDER BY query.name

This has good performance, it's relatively simple and it works well with dynamic ordering, 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:


  1. SELECT *
  2. FROM products
  3. 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.