Compiled Queries

Compiled queries allow you to significantly speed up your query times by pre-processing LINQ queries or LightSpeed query objects into a compiled form. These can then be executed repeatedly in different units of work and with different parameters. The performance increase is gained by reducing query processing overhead as it only needs to be conducted once, instead of each time you run the query.

Compiling a LINQ based query

To use compiled queries when writing a LINQ query first bring the Mindscape.LightSpeed.Linq namespace into scope via a using statement and then you can call the Compile() extension method on a LINQ query. This will return you a CompiledLinqQuery instance which can be later executed.

Two overloads are available; one for queries that return collections and another for values, such as Count(), Sum() or First(). The code below illustrates the use:

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 at this point you need a unit of work to compile the query. No entities are loaded, but a LightSpeedContext is required in order to build the right SQL statement for your database. This also provides handy query properties to write the query against. You can throw away the ‘compilation’ unit of work once this is done, and provide a ‘real’ unit of work when you execute the query.

Compiling a query object based query

To use compiled queries when writing a query object based query you need to use the CompiledQuery class which can be found in the Mindscape.LightSpeed.Querying namespace. This class has static methods for compiling Find, Count, Calculate and Project queries. The code below illustrates the use:

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

Executing 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. It is highly recommended to use a singleton LightSpeedContext as this guarantees the preceding requirement. This LightSpeedContext should be treated as immutable.

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 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.