Archive

Posts Tagged ‘compiled queries’

Magiq-to-Sql: Compiled queries support

November 25, 2010 Leave a comment

Back on the road with Magiq, at least for a while. I’m working on the development of a framework that could make full use of the Named Scopes feature of Magiq. The problem is that linq-to-sql needs compiled queries in order to speed up the performance and the current API is awful. So I tried to give Magiq the ability of handling compiled queries.

Since Linq is all about expression trees, I knew it will be possible to get from

var value = 3000000;
var query = country.Cities.Query( x => x.Population > value)
                          .OrderBy( x => x.Population);

to something like

var value = 3000000;
var expresson = (dc, id, value) => dc.GetTable<City>()
                                     .Where( x=>x.Id == id )
                                     .SelectMany( x=> x.Cities)
                                     .Where( x => x.Population > 100000 )
                                     .OrderBy( x => x.Population);

var query = CompiledQuery.Compile( expression );

Well, two hours of playing with the Expressions and all worked. The only/main problem is that Expressions instances are created every time so each expression object has a different HashCode and it’s impossible to cache the compiled queries by expression. Sadly, I had to add a parameter named “key” for identifying the compiled query.

This si how the final API looks like:

var value = 3000000;
var query = country.Cities.CompiledQuery( "large-cities-by-country", 
                                          (q,p) => q.Where( x => x.Population > p)
                                                    .OrderBy( x => x.Population),
                                          value );
  • The query will be identified as “large-cities-by-country”.
  • We need to pass the expression to be applied to the collection and the parameters, because we don’t want to hardcode the values in the query.

A small benchmark I’ve done with a simple model executing 5000 queries on a new DataContext each gives, this result:

  • magiq – not compiled: 36457 millisecond
  • magiq – compiled: 22295 millisecond
  • linq2sql – compiled: 22194 millisecond
Advertisements