An issue I run into quite a bit is unnecessary sorting in the database. I’m not talking about the sort of ‘Get last 5’ type of sorting where you need to sort to get a valid result set. I’m talking about the ‘Hey Database! I want some data…and I’ll probably throw some business logic in you… and while I’m here, how about we throw the presentation layer in as well and you sort the results for our UI!’
In the same way that AppDevs outnumber DBAs, infrastructure wise there are typically way more web/caching servers than there are database servers. This is mostly due to the fact that like a decent DBA, a decent database server is expensive. AppDevsWebservers in general are cheaper, have less memory and don’t need to be as awesome as a DBAdatabase server.
Lets take a look at some execution plans/cost so you can view ‘ORDER BY’ the same way I do.
Below is a simple example of selecting some badges by userid, and then display them alphabetically for the user to view :
And this is the execution plan it creates:
WHERE userid = 91254;
WHERE userid = 91254
ORDER BY name DESC;
On the bottom, do you see the glyph that is “Sort Cost: 15%”? As well as a difference of almost 10% in general between the two queries? Removing those “sort”(har har har) of thing adds up…like a lot.
Just Fix It
In C#, you have these things you can use called Ordered Enumerables and they are really easy to use… take a look:
var table = new Badges(); var badges = table.query("SELECT Name FROM dbo.Badges WHERE userid = @0", args: badgeid); return badges.OrderBy(x=>x.Name); //return badges.OrderByDescending(x => x.Name);
} That wasn’t too hard was it? In the above example, it is making a call to the DB (using Massive) and sucking the results into ‘badges’. That is where it breaks off its relationship with the database, and sorts the results in ‘badges’ and returns them to whatever was calling it.
Does this make us happy?
I’m happy because you’re not putting extra load on the DB. You’re happy because you have some sorted data and can close a help ticket… everyone wins right?
But But But, our servers are busy too!
There are a ton of ways to do this:
Doing it on the front end also works great if you are stuck using some particular DALs or ORMs that take a bulimic approach to data retrieval IE – Eating everything in site and then barfing it out to the app.