Profiling

You can use the logging infrastructure to perform simple profiling.

Query Patterns

You can review the SQL logs to determine what queries LightSpeed is issuing and to look for inefficient patterns such as N+1 lazy loads.  When looking for performance bottlenecks, it can be wise to identify a piece of code you want to profile, and turn logging off after executing that code – otherwise you may end up with a very large SQL trace, making it to track down whether you have traced a single inefficient run of code or a large number of efficient runs!

Timing Queries

When LightSpeed logs a SQL statement or batch, it also prints out the time taken to execute that statement.  You can also access the time taken directly using the CommandLog.TimeTaken property.

Using a Custom Logger for Profiling

In some cases you may be able to use a custom logger to search for patterns or problems by drilling into the CommandLog object.  For example, to detect poorly-performing queries which might be candidates for tuning at the database level or refactoring into stored procedures, you could create a custom logger which logs only queries that take a long time to run.  Or to detect N+1 problems you could create a logger which logs SQL statements on a per unit of work basis, and highlights where a unit of work has executed more statements than a threshold.

public class AlertingLogger : ILogger
{
  public void LogSql(object sql)
  {
    CommandLog commandLog = sql as CommandLog;
    if (commandLog != null)
    {
      if (commandLog.TimeTaken > LongQueryThreshold)
      {
        SendLongQueryAlert(commandLog);
      }
    }
  }
  public void LogDebug(object text) { /* do nothing * }
}

Using Profilers with the Interception API

LightSpeed 5 introduces a dedicated API for profiling internal performance. This exposes LightSpeed events to external profiling tools, such as the ASP.NET MVC Mini-Profiler, allowing them to intercept and modify the events. The API is available in the Mindscape.LightSpeed.Profiling namespace, which contains the Interceptor class. To connect a profiler to LightSpeed, you need to provide an implementation of this, such as in the figure below.

public class MiniProfilerInterceptor : Interceptor
{
  public override IDbConnection CreateConnection(Func<IDbConnection> baseCreator)
  {
    var realConnection = baseCreator();
    return ProfiledDbConnection.Get((DbConnection)realConnection, MiniProfiler.Current);
    // or return new ProfiledDbConnection(...) depending on which version you are using
  }
 
  public override IDbCommand CreateCommand(Func<IDbCommand> baseCreator)
  {
    var realCommand = baseCreator();
    return new ProfiledDbCommand((DbCommand)realCommand, null, MiniProfiler.Current);
  }
}

You can then attach the profiler through the web.config using the new interceptorClass attribute. Alternatively, this can be done by setting the LightSpeedContext.Interceptor property.

<lightSpeedContexts>
  <add name="Development"
         interceptorClass="LightSpeedMiniProfilerInterceptor.MiniProfilerInterceptor,LightSpeedMiniProfilerInterceptor" />
</lightSpeedContexts>