Poor man’s guide to database optimization - by the Marquis de Sade

time to read 2 min | 321 words

One of the more common problems that I see over and over again in many applications is that test databases are too fast. As I have shown, this can easily lead to really sever cases of chattiness with the database, and all while the developer is totally oblivious.

This is especially true when you develop against a local database with almost no data, and deploy to a network database with lots of data. SELECT N+1 is bad enough, but when N is in the hundreds or more, it gets bad. The main problem is that developers aren’t really aware of that. This don’t see the problem, or feel it. And while they could fix it if they caught it in time, trying to come back to an existing application and fix all the many places where they assumed database access is free is a daunting task.

Therefore, I have set out to solve the problem. Obviously it is a problem with the developers not paying attention, but how can we deal with that?

Well, you could buy the NHibernate Profiler, which is my official recommendation. Or, if you don’t feel like spending money on this, you can utilize the following interceptor. That will make the developers sit up and notice when they start talking to the database.

public class SlowDownDudeInterceptor : EmptyInterceptor
{
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        return sql.Insert(0, "waitfor delay '0:0:0.5'" + Environment.NewLine);
    }
}

Don’t go to production with this!