Compiled queries for LightSpeed

I’m pleased to announce a beta release of compiled queries for LightSpeed. This feature allows you to pre-process LINQ queries or LightSpeed query objects into a ‘compiled’ form, then repeatedly execute that compiled form in different units of work and with different parameters. Compiled queries improve performance by removing much of the query processing overhead each time you run the query.

Compiled queries are included in current LightSpeed nightly builds. You can get the Express edition nightly build from the Downloads page, and the Professional edition nightly build from the store.

How do I compile a query?

To compile a LINQ query, use the Compile() extension method which is defined for LINQ queries in the new CompiledLinqQuery class. Compile has two overloads, one for queries that return collections and one for queries that return single values, such as Count(), Sum() or First() queries. Both overloads return CompiledQuery objects, which I’ll discuss in a moment. Here’s how it looks:

using Mindscape.LightSpeed.Linq;  // bring extension methods into scope
 
var query = from p in UnitOfWork.Penguins
            where p.Age < 9
            select p;
CompiledQuery<IList<Penguin>> youngPenguins = query.Compile();
CompiledQuery<int> youngPenguinCount = query.Compile(q => q.Count());

Note that you need a unit of work in order to compile a query, even though compilation doesn’t actually load any entities. This is because we need a LightSpeedContext in order to build the right SQL for the database you’re using, and using a unit of work provides us with a LightSpeedContext and you with handy query properties to write the query against. You can throw away the ‘compilation’ unit of work once you’ve called Compile(); you’ll provide a ‘real’ unit of work when you execute the compiled query.

We expect most users of compiled queries to be working with LINQ, but for those of you who are bustin’ LightSpeed old skool, you can also compile query objects. To do this, use the new CompiledQuery class. This has static methods for compiling Find, Count, Calculate and Project queries, corresponding to the equivalent methods on IUnitOfWork. Here’s an example:

Query query = new Query(typeof(Penguin), Entity.Attribute("Age") < 9);
CompiledQuery<IList<Penguin>> cquery = CompiledQuery.CompileFind<Penguin>(query, _unitOfWork);

Running a compiled query

A compiled query is represented by a CompiledQuery<T> object, where T is the type of data to be returned — typically a list of entities, but it could also be a numeric type for count and aggregate queries, or a list of non-entity objects for projections.

To run a compiled query, call its Execute() method, passing the unit of work in which you want to execute it:

IList<Penguin> ps = youngPenguins.Execute(unitOfWork);
int pcount = youngPenguinCount.Execute(unitOfWork);

The unit of work must be associated with the same LightSpeedContext instance as you used to compile the query. We’ve advised you before to use a singleton LightSpeedContext — if you want to use compiled queries, it’s doubly important! You should also remember to treat the LightSpeedContext as immutable — if you do sneaky things like changing the DataProvider after compiling the query, on your own head be it!

Parameterising compiled queries

In practice you probably won’t re-run the exact same query again and again. One or more of the parameters will vary from run to run. For example, you might be performing a search by name, with the name coming from user input. To represent a parameter in a compiled query, using the CompiledQuery.Parameter method:

var query = from p in UnitOfWork.Penguins
            where p.Name == CompiledQuery.Parameter<string>("name")
            select p;
CompiledQuery<IList<Penguin>> namedPenguins = query.Compile();

Now when you call CompiledQuery.Execute, you must supply an ICompiledParameterValues which provides the values for each parameter. The simplest way to do this is to use an anonymous type and the CompiledQuery.Parameters method:

var results = namedPenguins.Execute(unitOfWork, CompiledQuery.Parameters(new { name = "Gloria" }));

(Note: anonymous types, though awfully convenient, are relatively slow. Depending on the performance characteristics of your query, it may be worth using an optimised implementation of ICompiledParameterValues based on a switch statement or a dictionary. At the moment you’ll have to hand-roll such implementations but we’ll try to add built-in ones over time.)

Query objects fans can also parameterise queries, but must use the non-generic version of the CompiledQuery.Parameter method which returns a QueryExpression. (You can’t use this in LINQ because LINQ queries are subject to C# compiler type checking.)

When is it worth compiling?

The universal guideline for optimisation is “don’t guess, measure.” We can’t give you any firm rules on when to compile or what improvements you’ll see. However, we can offer some general guidance.

Query execution time has a number of factors: how long it takes LightSpeed to generate the SQL, how long it takes the database to process the query, how long it takes the network to stream the results back to the client, and how long it takes LightSpeed to materialise the results. Query compilation affects only the first of these.

If a query returns a very large result set, the cost of the query will be dominated by data transfer and materialisation (or, if the database is badly designed or the query is complicated, by database processing). Query compilation will probably have a negligible benefit in such cases.

If a query is very complex but returns relatively few results, then the cost may be dominated by SQL generation or database processing. In such a case compilation may or may not produce significant improvements depending on how long the database processing takes.

The SQL generation time is most significant when a query is relatively simple, well optimised on the database (e.g. the columns used in the query are indexed) and returns relatively few results. It is cases like this where you’ll see the most benefit from compilation. Of course, these are already the queries that run quickest!

LINQ queries require more pre-processing than query objects, so get more benefit from compilation. And obviously, queries that you run many times benefit more than queries that you run only infrequently.

Finally, beware of measuring in isolation. If compilation saves 20% off your LightSpeed queries, that may sound sweet, but if the LightSpeed queries are only 20% of the overall page time, then the improvement in the total page time would be marginal. Remember, don’t guess, measure — and measure what is meaningful!

What’s in and what’s out

At this stage we haven’t implemented compilation for all queries. We’ve focused on entity loading, aggregates and simple projections. If your query involves join statements, grouping, the ElementAt() operator or complex or client-side projections, it won’t currently compile. (Joins resulting from using associations in a query should be fine.) Let us know what your priorities are here, and we’ll try to push them to the top of the list as we round out the feature.

Get it while it’s hot!

Want to take it for a spin? Download the FREE Express edition nightly build from the Downloads page, or if you’re already a customer grab the latest Professional edition nightly build from the store. And please post bugs, feedback and feature requests in the forums — we love to hear from you! Happy coding!

Tagged as LightSpeed

4 Responses to “Compiled queries for LightSpeed”

  • […] Compiled queries! Yay! […]

  • I don’t understand how this all fits together.

    In a web application I am creating new units of work per request and to generate the compiled query I need the unit of work instance. So should I have a static field in my class that use to store the compiled query instance, and only generate it the first time that class is constructed?

    Is this a valid way to do this?

    public class ResetPasswordQuery : IResetPasswordQuery
    {
    public ResetPasswordQuery(IUnitOfWork unitOfWork)
    {
    this.unitOfWork = unitOfWork;

    if (compiledQuery == null)
    {
    var query = from u in unitOfWork.Query()
    where u.ResetPasswordIdentifier == CompiledQuery.Parameter(“resetPasswordIdentifier”)
    select u;

    compiledQuery = query.Compile();
    }
    }

    public User Execute(Guid resetPasswordIdentifier)
    {
    var parameters = CompiledQuery.Parameters(new { resetPasswordIdentifier });
    return compiledQuery.Execute(unitOfWork, parameters).SingleOrDefault();
    }

    private readonly IUnitOfWork unitOfWork;
    private static CompiledQuery<IList> compiledQuery;
    }

  • Hi Jake,

    Sorry this was a bit confusing. Yes, a static field in your class is probably the right way to do, and initialising it on first use is certainly a reasonable way to ensure that you have a UOW available to perform the compilation. An alternative approach would be to initialise it in a static constructor. In this case your static constructor would need to create and dispose a unit of work (associated with the correct LightSpeedContext), but this UOW would be used only for compilation, not for connecting to the database. Something like:

    static ResetPasswordQuery() {
    using (IUnitOfWork uow = Globals.Context.CreateUnitOfWork()) {
    compiledQuery = /* compile the query */;
    }
    }

    This avoids the per-instance construction check, but at the expense of having a ‘dummy’ unit of work mysteriously appearing in your code. And to be clear, I’m not saying you should change what you have, because what you have is fine, I’m just mentioning an alternative in case because some people will prefer it.

    Hope this makes a bit more sense now!

  • […] with LightSpeed compiled queries Tagged as LightSpeed Tweet 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 […]

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top