Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 10 | Comments: 35

filter by tags archive

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!


Comments

Markus Zywitza

Just an idea: Wouldn't such a thing be a good idea to simulate load on the DB server?

Neil

I always thought that each db call should make the workstation beep, or make a systray thingee blink. I never got around to implementing anything like that, though.

Dmitry

I always output (and inspect) queries into the console/debug stream for database tests. It's fairly easy to do with any ORM that implements a unit-of-work pattern and with log4net.

Chris Missal

This is a really good idea, but why stop there, maybe we can speed it up by using:

"waitfor delay '-0:0:0.5'"

What do you think?? ;)

Sosh

@Dmitry,

Could you give a quick example of how you would do that with say NHibernate? Thanks

Dmitry

@Sosh,

NHibernate is by far the easiest ORM to implement logging. For unit testing you just add the configuration property true into the Web.Config/App.config file.

If you want to configure log4net to log into event log, DB, etc, here is the link: nhforge.org/.../...et-for-use-with-nhibernate.aspx

Entity Framework was a different story. It required a provider wrapper that sits between the object context and the database provider.

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. Production postmortem: The case of the memory eater and high load - about one day from now
  2. Production postmortem: The case of the lying configuration file - 3 days from now
  3. Production postmortem: The industry at large - 4 days from now
  4. The insidious cost of allocations - 5 days from now
  5. Find the bug: The concurrent memory buster - 6 days from now

And 4 more posts are pending...

There are posts all the way to Sep 10, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    14 Aug 2015 - The case of the man in the middle
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats