This project has moved and is read-only. For the latest updates, please go here.

Prepared statements and indexing

Topics: Developer Forum
Aug 12, 2008 at 9:01 AM

Hi!

 

This is my very first post; therefore I must congratulate you on the remarkable product! I could spend few pages describing how fascinated I’m :) NQuery is simply impressive!

 

I have two questions/suggestions. I would like to describe shortly motivation for them.

 

I work currently on a huge and serious predevelopment project that has few particular demands. One of demands is ability to query object space in a fast and convenient way. After several workshops it is decided to use SQL as a language (convenient) in combination with an in-memory database (speed). Keep in mind that this is not ‘database application’. We are copying currently parts of our object space to in-memory database in order to query it efficiently. (BDW, we use QuiLogic www.quilogic.cc as in-memory DB).

 

Concept worked great (fast + convenient) until we noticed that in real life scenarios database memory overhead is simply too big! We tried several workarounds but without real success.

 

And then we found NQuery :)

 

After few tests I can say NQuery is exactly what we need! But... it’s too slow :(

 

Here is the speed comparison (time is in milliseconds) between NQuery upon List<> and QuiLogic in-memory database on three simple queries:

 

SELECT Id, ProgramName FROM CdlComponent

  - Result: 95.000 rows

  - QLDB:      200 ms

  - NQuery:    410 ms

 

 

SELECT Id, ProgramName FROM CdlComponent

WHERE ProgramName = 'Input'

  - Result: 2.300 rows

  - QLDB:       3 ms

  - NQuery:   230 ms

 

 

SELECT Id, ProgramName FROM CdlComponent

WHERE ProgramName LIKE 'I%'

  - Result: 2.300 rows

  - QLDB:      15 ms

  - NQuery:   610 ms

 

Of course, generic query engine can’t be compared with fully optimized in-memory relational DB! But this comparison produced two simple ideas.

 

 

First, DB uses prepared statements. There is a significant difference in query speed with and without prepared statements.

 

Second, DB uses indexes.

 

So, my question is - is it possible to create something like this:

NQuery with prepared statements + indexing mechanism (something like i4o http://www.codeplex.com/i4o)

 

If it is, is it possible to give rough estimation, how faster above queries could be.

 

Thanks!

Igor

Aug 15, 2008 at 5:54 PM
Hi Igor,

first of all I would like to say that I really appreciate your feedback.

Basically, NQuery uses some kind of prepared statements. If you specify a query and execute it NQuery will first check if your query has already been compiled. If not, it will compile it. Here compilation means it will find an execution plan for your query, perform some optimization strategies and finally NQuery will compile all expression in the plan down to MSIL.

In most queries the compilation time will be much larger than the execution time. You can check this by executing the queries twice. The second execution will typically be much faster.

However, as you already found out using typed objects is pretty slow. The reason is that although NQuery uses compilation in the execution plan it does not use compilation to obtain the initial values out of a table's row -- that it the responsibility of a TableProvider/ColumnBinding. If you add an IEnumerable<T> to your data context NQuery will use a registered property provider to determine the set of properties T is supposed to have. If you haven't registered your own property provider NQuery will use the ReflectionProvider. To make a long story shot: in this release ReflectionProvider uses simple reflection to pass values to NQuery's query executor. And that it is pretty slow.

The easiest workaround for this is to replace the default ReflectionProvider by one that also generates MSIL to read the values. Below you will find sample code that does that. Please note that it uses Linq so you will need .NET Framework 3.5. If this is not an option for you I will produce a version that uses plain old Reflection.Emit -- but that is a bit more work, so I haven't done it yet :-)
internal sealed class FastPropertyProvider : ReflectionProvider
{
    private delegate object PropertyInvoker(object instance);

    private sealed class LambdaPropertyBinding : PropertyBinding
    {
        private PropertyInfo _propertyInfo;
        private PropertyInvoker _propertyInvoker;

        public LambdaPropertyBinding(PropertyInfo propertyInfo, PropertyInvoker propertyInvoker)
        {
            _propertyInfo = propertyInfo;
            _propertyInvoker = propertyInvoker;
        }

        public override string Name
        {
            get { return _propertyInfo.Name; }
        }

        public override object GetValue(object instance)
        {
            return _propertyInvoker(instance);
        }

        public override Type DataType
        {
            get { return _propertyInfo.PropertyType; }
        }
    }

    protected override PropertyBinding CreateProperty(PropertyInfo propertyInfo)
    {
        ParameterExpression parameter = Expression.Parameter(typeof(object), "inst");
        UnaryExpression castParameterToInstanceType = Expression.Convert(parameter, propertyInfo.DeclaringType);
        MemberExpression propertyAccess = Expression.Property(castParameterToInstanceType, propertyInfo);
        UnaryExpression castResultToObject = Expression.Convert(propertyAccess, typeof(object));
        LambdaExpression lambda = Expression.Lambda(typeof(PropertyInvoker), castResultToObject, parameter);
        PropertyInvoker compiledDelegate = (PropertyInvoker)lambda.Compile();

        return new LambdaPropertyBinding(propertyInfo, compiledDelegate);
    }
}
To replace the default ReflectionProvider by the faster one you simply have to set the DefaultProvider on your data context:
query.DataContext.MetadataContext.PropertyProviders.DefaultValue = new FastPropertyProvider();
Please note that you must do this before you add any table to the data context -- otherwise theses tables will still use the slower ReflectionProvider.

I suggest you try this before we discuss more sophisticated optimization strategies. However, if you are still not satisfied with the performance please let me know!

Kind Regards,
Immo Landwerth