Mindscape Blog » Blog Archive » Put the pedal to the metal with LightSpeed compiled queries

Put the pedal to the metal with LightSpeed compiled queries

A few months ago we introduced compiled queries for LightSpeed. The idea of compiled queries is to do a lot of query preparation up front, so that when you actually execute the query, we can bypass most of the query prep and just let the query rip.

In this post, I’ll update you on compiled query performance improvements since the first drop, and discuss a way of squeezing the last iota of performance out of your compiled queries.

Turbocharging compiled queries

Our first drop of compiled queries pushed a significant amount of query preparation into the compilation phase, but we didn’t stop there. Current nightly builds now prepare even more of the query, to the point where compiled queries are as fast as calling FindBySql — that is, they’re as fast as if the SQL was already written — or even slightly faster! The SQL generation overhead is goneburger.

And what do you have to do to take advantage of this goneburger goodness? If you’re already using compiled queries, nothing. The compiled query system implements it behind the scenes (caveat: not available on some databases). If you’re not already using compiled queries, it’s time to start.

Taking the brakes off

The full skinny on compiled queries is here, but here’s the summary: call CompiledQuery.Compile to compile a query, then CompiledQuery.Execute to execute it. Obviously, you’ll often want to reuse compiled queries with different parameter values, so when you call Compile, you can specify placeholders for parameters, and when you call Execute, you can specify values for those placeholders.

// Preparing the compiled query
var query = from p in unitOfWork.Penguins
            where p.Name == CompiledQuery.Parameter<string>("name")
            select p;
var namedPenguinsQuery = query.Compile();
 
// Using the compiled query
var penguinsNamedGloria = namedPenguinsQuery.Execute(
                            UnitOfWorkScope.Current,
                            CompiledQuery.Parameters(new { name = "Gloria" }));

There’s one remaining optimisation we can make to this code, and it’s not where you might expect. Passing an anonymous type to CompiledQuery.Parameters is easy, convenient and readable, but in order to match anonymous type members to query parameter placeholders, LightSpeed has to use .NET Reflection. And reflection is great and all that, but heaven knows it’s not fast. So let’s take the reflection brake off!

When you execute a compiled query with parameters, you pass the parameter values in an ICompiledParameterValues. ICompiledParameterValues is a simple interface which takes a key (the parameter name) and returns a value (the parameter value). The CompiledQuery.Parameters() method returns an ICompiledParameterValues implemented using reflection, but for specific cases we can create our own ICompiledParameterValues which we can make much faster. Here’s an implementation suitable for the query above:

public class SingleParameter : ICompiledParameterValues
{
  private readonly string _value;
 
  public SingleParameter(string value)
  {
    _value = value;
  }
 
  public object Value(object key)
  {
    // We assume there is only a single parameter, so we can ignore the key
    return _value;
  }
}

Now we can execute the query as follows:

// Using the compiled query
var penguinsNamedGloria = namedPenguinsQuery.Execute(
                            UnitOfWorkScope.Current,
                            new SingleParameter("Gloria"));

Because the parameter value lookup no longer needs to do reflection, the compiled query now runs even faster than it did before.

Obviously, the SingleParameter resolver is limited to cases where the compiled query defines only one parameter, but you can create other fast implementations based on switch statements, lists of tuples or dictionaries (pro tip: the old ListDictionary class is optimised for small dictionaries).

Put the pedal to the metal

If you haven’t tried compiled queries, or if you tried the first drop but haven’t updated in the last few months, we’d encourage you to take them for a spin. You can really crank out a big boost in LightSpeed performance, so if you have pages or screens that are dominated by data access time then compiled queries could well be the answer. As always, you can get nightly builds of the free, fully-featured LightSpeed Express Edition from the downloads page, and of the Professional Edition from the store.