NQuery

New Post: Decimal and Date query error on dataset

langc334 — Tue, 19 Aug 2008 12:26:00 GMT

terrajobst,

Thanks for the response. I will insert those 2 adjustments to my query and give it a run.

New Post: Decimal and Date query error on dataset

terrajobst — Mon, 18 Aug 2008 08:18:25 GMT

Hi langc334,

sorry for the late answer but this weekend I had to ship Clone Detective for Visual Studio.

NQuery shares many characteristics with C# when it comes to implicit conversions and overload resolution. So if you want to compare a decimal with a double you have to convert one of the operands, e.g.

Pay_Offset = TO_DECIMAL(500.50)

If you want to use date literals you have to use pound signs as a delimiter, e.g.

Pay_Date = #5/13/2008 12:00:00 PM#

Here is the full repro source:

public sealed class Customer
{
    public int C_ID;

    public Customer(int c_ID)
    {
        C_ID = c_ID;
    }
}

public sealed class Payment
{
    public int P_C_ID;
    public decimal Pay_Offset;
    public DateTime Pay_Date;

    public Payment(int p_C_ID, decimal pay_Offset, DateTime pay_Date)
    {
        P_C_ID = p_C_ID;
        Pay_Offset = pay_Offset;
        Pay_Date = pay_Date;
    }
}

private static void Main()
{
    List<Customer> customers = new List<Customer>();
    customers.Add(new Customer(1));
    customers.Add(new Customer(2));

    List<Payment> payments = new List<Payment>();
    payments.Add(new Payment(1, 500.50m, new DateTime(2008, 5, 13, 12, 0, 0)));
    payments.Add(new Payment(2, 600.50m, DateTime.Now));

    DataContext dataContext = new DataContext();
    dataContext.Tables.Add(customers, "Customer");
    dataContext.Tables.Add(payments, "Payments");

    Query query = new Query();
    query.DataContext = dataContext;
    query.Text = @"
                    SELECT    *
                    FROM    Customer,
                            Payments
                    WHERE    C_ID = P_C_ID
                    AND        Pay_Offset = TO_DECIMAL(500.50)
                    AND        Pay_Date = #5/13/2008 12:00:00 PM#
                ";

    DataTable table = query.ExecuteDataTable();
    table.TableName = "Result";
    table.WriteXml(Console.Out);

    // Output:
    //<DocumentElement>
    // <Result>
    //    <C_ID>1</C_ID>
    //    <P_C_ID>1</P_C_ID>
    //    <Pay_Offset>500.50</Pay_Offset>
    //    <Pay_Date>2008-05-13T12:00:00+02:00</Pay_Date>
    // </Result>
    //</DocumentElement>
}

Kind Regards,
Immo Landwerth

New Post: Prepared statements and indexing

terrajobst — Fri, 15 Aug 2008 16:54:01 GMT

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

New Post: Decimal and Date query error on dataset

langc334 — Wed, 13 Aug 2008 12:56:08 GMT

I am running a query against a VB .NET dataset with multiple tables. I get two errors when I run the query. One is that I cannot compare a double to a decimal and the other a string to a date. Here are the two samples of how the query looks.

SELECT * FROM Customer, Payments
WHERE C_ID = P_C_ID and Pay_Offset = 500.50 and Pay_Date = '5/13/2008 12:00:00 PM'

Pay_Offset is a decimal(18, 2) type column in the table and Pay_date is a datetime type column. The dataset is filled with data from a database that declares these types.

I have tried using quotes and no quotes around the dollar amount and it will change from can't compare decimal to double to can't compare decimal to string. The date value I have tried with and without the PM and I still receive the error. Am I putting the values in wrong in the query? Is this something with how NQuery handles values that it auto converts the dollar amount to a double even though the field is decimal?

New Post: Prepared statements and indexing

ironcev — Tue, 12 Aug 2008 08:01:35 GMT

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

Source code checked in, #34246

terrajobst — Sun, 06 Jul 2008 20:06:36 GMT

Updated "How to build.txt".

Source code checked in, #34245

terrajobst — Sun, 06 Jul 2008 20:00:50 GMT

Added source control bindings for setup project.

Source code checked in, #34244

terrajobst — Sun, 06 Jul 2008 19:59:21 GMT

Refactored build process. Changed the way documentation is generated. Now both a CHM and HxS are produced. Added setup.

Project License Changed

Sun, 06 Jul 2008 13:13:51 GMT

Microsoft Public License (Ms-PL) This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software. 1. Definitions The terms "reproduce," "reproduction," "derivative works," and "distribution" have the same meaning here as under U.S. copyright law. A "contribution" is the original software, or any additions or changes to the software. A "contributor" is any person that distributes its contribution under this license. "Licensed patents" are a contributor's patent claims that read directly on its contribution. 2. Grant of Rights (A) Copyright Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free copyright license to reproduce its contribution, prepare derivative works of its contribution, and distribute its contribution or any derivative works that you create. (B) Patent Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free license under its licensed patents to make, have made, use, sell, offer for sale, import, and/or otherwise dispose of its contribution in the software or derivative works of the contribution in the software. 3. Conditions and Limitations (A) No Trademark License- This license does not grant you rights to use any contributors' name, logo, or trademarks. (B) If you bring a patent claim against any contributor over patents that you claim are infringed by the software, your patent license from such contributor to the software ends automatically. (C) If you distribute any portion of the software, you must retain all copyright, patent, trademark, and attribution notices that are present in the software. (D) If you distribute any portion of the software in source code form, you may do so only under this license by including a complete copy of this license with your distribution. If you distribute any portion of the software in compiled or object code form, you may only do so under a license that complies with this license. (E) The software is licensed "as-is." You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. You may have additional consumer rights under your local laws which this license cannot change. To the extent permitted under your local laws, the contributors exclude the implied warranties of merchantability, fitness for a particular purpose and non-infringement.

Source code checked in

terrajobst — Sun, 01 Jun 2008 13:44:23 GMT

Renamed NQuery.Gui.* to NQuery.Demo.*

Source code checked in

terrajobst — Sun, 01 Jun 2008 13:13:31 GMT

Updated welcome page in demo application.

Source code checked in

terrajobst — Sun, 01 Jun 2008 13:02:09 GMT

Updated "How to build.txt".

Source code checked in

terrajobst — Sun, 01 Jun 2008 12:57:11 GMT

Added smart member lists tests. Added many other tests after analyzing the code coverage results.

Source code checked in

terrajobst — Sun, 01 Jun 2008 12:50:50 GMT

Removed obsolete batch files.

Source code checked in

terrajobst — Sat, 31 May 2008 10:27:27 GMT

Removed obsolete FxCop folder.

Source code checked in

terrajobst — Sun, 25 May 2008 20:16:21 GMT

Updated sandcastle documentation project files so that it looks better with the VS2005 style.

Source code checked in

terrajobst — Thu, 22 May 2008 12:56:46 GMT

Migrated samples project to VS2008. Fixed compiler errors in samples.

CLOSED ISSUE: Query error when deleted rows in DataTable

terrajobst — Sun, 18 May 2008 18:52:53 GMT

Running a query against a data table results in an error:

DataTable dataTable = new DataTable();
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Rows.Add(1, "Name1");
dataTable.Rows.Add(2, "Name2");
dataTable.Rows.Add(3, "Name3");
dataTable.AcceptChanges();
dataTable.Rows[1].Delete();
dataTable.TableName = "Test";

Query query = new Query();
query.Text = "SELECT * FROM Test";
query.DataContext.Tables.Add(dataTable);

DataTable resultDataTable = query.ExecuteDataTable();
resultDataTable.TableName = "Result";
resultDataTable.WriteXml(Console.Out);

The error is:

Unhandled Exception: NQuery.RuntimeException: ColumnBinding.GetValue() threw an exception. ---> System.Data.DeletedRowInaccessibleException: Deleted row informa
tion cannot be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(DataColumn column, DataRowVersion version)
at NQuery.Runtime.DataColumnBinding.GetValue(Object row)
at NQuery.Runtime.ExecutionPlan.TableIterator.Read()
--- End of inner exception stack trace ---
at NQuery.Runtime.ExecutionPlan.TableIterator.Read()
at NQuery.Runtime.ExecutionPlan.StatisticsIterator.Read()
at NQuery.Runtime.ExecutionPlan.ResultIterator.Read()
at NQuery.Query.ExecuteDataTable()
at ConsoleApplication2.Program.Main() in K:\ConsoleApplication2\ConsoleApplication2\Program.cs:line 46

I guess this error can simply resolved by skipping all rows that have a RowState of DataRowState.Deleted.

Source code checked in

terrajobst — Sun, 18 May 2008 18:52:51 GMT

Fixed WI-14823. Added test to verify behavior.

Source code checked in

terrajobst — Sun, 18 May 2008 18:34:36 GMT

Replaced FxCop by static code analysis feature of Visual Studio Team System.