Devart Forums

Posted: **Mon 12 Sep 2011 14:54**

Using a recent version of Devart, we identified a performance issue, maybe because we aren't using the component as effective as possible.

We identified the issue using JetBrains dotTrace in the DevArt BuildQuery method, which takes almost 200 milliseconds to complete:

Code: Select all

22,26 % AuxGetHelper - 181,66 ms - 1 call - Jumbo.MVC.Models.ReadOnlyTableData.AuxGetHelper(IQueryable, Object)
  22,20 % SingleOrDefault - 181,18 ms - 1 call - System.Linq.Queryable.SingleOrDefault(IQueryable, Expression>)
    22,19 % Execute - 181,06 ms - 1 call - Devart.Data.Linq.DataQuery.Execute(Expression)   (from System.Linq.IQueryProvider)
      22,19 % Execute - 181,06 ms - 1 call - Devart.Data.Linq.Provider.DataProvider.Execute(Expression)   (from Devart.Data.Linq.Provider.IProvider)
        21,27 % BuildQuery - 173,56 ms - 1 call - Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression)
          17,50 % DataProvider.CompiledQuery..ctor - 142,80 ms - 1 call - Devart.Data.Linq.Provider.DataProvider.CompiledQuery..ctor(QueryInfo, IDataServices, Boolean, Object)
            17,50 % GetReaderFactory - 142,80 ms - 1 call - Devart.Data.Linq.Provider.DataProvider.CompiledQuery.GetReaderFactory(List, IDataServices, SqlNode)
              17,49 % a - 142,70 ms - 1 call - Devart.Data.Linq.Provider.n.a(Type, SqlExpression, IDataServices, Type, Type)
              0,01 % c - 0,09 ms - 1 call - Devart.Data.Linq.t.c(MetaType)
          3,13 % i - 25,54 ms - 1 call - Devart.Data.Linq.Provider.Query.u.i(Expression)
          0,27 % BuildQuery - 2,20 ms - 1 call - Devart.Data.Linq.Provider.DataProvider.BuildQuery(ResultShape, Type, SqlNode, IList)
          0,21 % GetCompiledQueryFromCacheWithLock - 1,73 ms - 1 call - Devart.Data.Linq.Provider.DataProvider.GetCompiledQueryFromCacheWithLock(Expression, CompiledQueryCache &, a &)
          0,16 % a - 1,27 ms - 1 call - Devart.Data.Linq.Provider.Query.ae.a(Expression)
        0,92 % ExecuteAllQueries - 7,49 ms - 1 call - Devart.Data.Linq.Provider.DataProvider.ExecuteAllQueries(CompiledQuery, Object [])
  0,01 % Cast - 0,11 ms - 1 call - System.Linq.Queryable.Cast(IQueryable)

This should be pasted in a text editor to be readably. When I remove the details it's a little bit more readable:

Code: Select all

22,26 % AuxGetHelper - 181,66 ms - 1 call
  22,20 % SingleOrDefault - 181,18 ms - 1 call
    22,19 % Execute - 181,06 ms - 1 call
      22,19 % Execute - 181,06 ms - 1 call
        21,27 % BuildQuery - 173,56 ms - 1 call
          17,50 % DataProvider.CompiledQuery..ctor - 142,80 ms - 1 call
            17,50 % GetReaderFactory - 142,80 ms - 1 call
              17,49 % a - 142,70 ms - 1 call
              0,01 % c - 0,09 ms - 1 call
          3,13 % i - 25,54 ms - 1 call
          0,27 % BuildQuery - 2,20 ms - 1 call
          0,21 % GetCompiledQueryFromCacheWithLock - 1,73 ms - 1 call
          0,16 % a - 1,27 ms - 1 call
        0,92 % ExecuteAllQueries - 7,49 ms - 1 call
  0,01 % Cast - 0,11 ms - 1 call

Maybe this is because we aren't using the caching correctly (the above GetCompiledQueryFromCacheWithLock suggests the BuildQuery's actions are cached).

Our code is as follows:

Code: Select all

/// 
/// Auxillary method for Get / GetDirectFromTable
/// 
/// 
/// 
/// 
private TModelClass AuxGetHelper(IQueryable query, object id)
{
    if (id == null)
        return null;            

    return (TModelClass)query.Cast().SingleOrDefault(record => record.Id == (long)id);
}

id can be different everytime and we see in the performance trace that a different id leads to again about 175 milliseconds in the BuildQuery method, maybe because we didn't parameterize the query correctly and a new query has to be build for the new id.

Is it possible to speed this up?

Kind regards,

Edwin.

Posted: **Wed 14 Sep 2011 14:17**

The compiled query cache stores the whole query, including the passed parameters. To save the compiled query as a function getting an ID parameter, you can compile the query manually. Please see the corresponding topic for details:
http://www.devart.com/linqconnect/docs/ ... eries.html
After that, you can execute this delegate with other ID values omitting the time-consuming part of query preparation.

Please tell us if this helps.

Posted: **Mon 19 Sep 2011 09:24**

Ok. I'm implementing some functionality for getting a record by Id using CompiledQuery...

But why can't you optimize this for us? If I have to sprinkle CompiledQueries everywhere, my code will become very ugly and I think its coded at the wrong level. It would be more correct to implement this at the Linq-to-Oracle layer and would make things a lot easier and more performant.

Just accidentaly I came across the fact that all local variables, parameters and such are compiled into an object, which is fed into the LINQ IQueryable, instead of the run-time values itself. Constants and such are expressed as constants in a LINQ Expression, on the other hand.

So you could differentiate between constants and parameters. This way, you could "prepare" a query for usage with multiple different parameters, (like in the old days:-)), but completely transparant to the user (optionally including a setting to enable/disable this new behaviour).

Some examples:

Code: Select all

const long c1 = 2;
public enum blaat
  { 
    e1 = 3
  }
var query1 = datacontext.Laadroutes.Where(x => x.Id == 1);
var query2 = datacontext.Laadroutes.Where(x => x.Id == c1);
var query3 = datacontext.Laadroutes.Where(x => x.Id == (long) blaat.e1);

query1.Expression.ToString():

Code: Select all

Table(Laadroute).Where(x => (x.Id = 1))

(same for query2 and query3)

On the other hand:

Code: Select all

long id = 1;
var query4 = datacontext.Laadroutes.Where(x => x.Id == id);

query4.Expression.ToString() gives

Code: Select all

Table(Laadroute).Where(x => (x.Id = value(Jumbo.Juist.Controllers.HomeController+c__DisplayClass0).id))

Thus, the variable is captured in a compiler generated class.

So a distinction can be made between variables and constants, allowing automatic caching of queries, keeping the variables variable and increasing performance without the hassle of manually creating CompiledQueries.

I would like to hear your views on this.

Posted: **Thu 22 Sep 2011 08:57**

We do plan to change the CompiledQueryCache behaviour in the described way (i.e., to compile query with 'parameters' and so use the same 'prepared' query with different constants). However, we cannot provide any timeframe for this at the moment. We will post here as soon as any new information on this is available.

Posted: **Mon 03 Oct 2011 15:49**

I tried to use CompiledQuery like in the following example, but it fails:

Code: Select all

/// 
/// Get direct from table (no DefaultFilter).
/// 
/// Throws an exception when the record isn't found
/// 
/// 
/// 
public virtual TModelClass GetById(long? id)
{
    if (id == null) return null;
            
    try
    {
        if (DataContextHelper.IsOra(ContextValues.DataContext))
        {
            return CompiledGetById((Devart.Data.Linq.DataContext)ContextValues.DataContext, id.Value);
        }
        else
        {
            IQueryable all = GetTable();
            return (TModelClass)all.Cast().Where(record => record.Id == id.Value).ExtSingle();
        }
    }
    catch (Exception ex)
    {
        throw new IllegalNullValueException(ex, "Record {0} not found for Id =  {1}", typeof(TModelClass).Name, id);
    }
}

Dictionary>> _CompiledGetByIdDict = new Dictionary>>(50);
private TModelClass CompiledGetById(Devart.Data.Linq.DataContext dc, long id)
{
    Func> cqry;
    bool create = false;

    lock (_CompiledGetByIdDict)
    {
        if (!_CompiledGetByIdDict.TryGetValue(typeof(TModelClass), out cqry)) create = true;
    }
    if (create)
    {
        cqry = Devart.Data.Linq.CompiledQuery.Compile(
           (Devart.Data.Linq.DataContext datacontext, long _id) => datacontext.GetTable(typeof(TModelClass)).Cast().Where(record => record.Id == _id)
               );
        lock (_CompiledGetByIdDict)
        {
            _CompiledGetByIdDict.Add(typeof(TModelClass), cqry);
        }
    }

    var list = cqry((Devart.Data.Linq.DataContext)ContextValues.DataContext, id);
    return (TModelClass)(list.ExtSingle());
}

It fails with the following exception on the line were the cqry delegate is executed (I tried to translate the error message to English a bit):

Code: Select all

System.InvalidOperationException: 
No method Select in type System.Linq.Enumerable is compatibel with the given arguments.
   bij System.Linq.Expressions.Expression.FindMethod(Type type, String methodName, Type[] typeArgs, Expression[] args, BindingFlags flags)
   bij System.Linq.Expressions.Expression.Call(Type type, String methodName, Type[] typeArguments, Expression[] arguments)
   bij Devart.Data.Linq.Provider.Query.u.a(Expression A_0, Type A_1)
   bij Devart.Data.Linq.Provider.Query.u.b(MethodCallExpression A_0)
   bij Devart.Data.Linq.Provider.Query.u.j(Expression A_0)
   bij Devart.Data.Linq.Provider.Query.u.a(Expression A_0)
   bij Devart.Data.Linq.Provider.Query.u.a(Expression A_0, LambdaExpression A_1)
   bij Devart.Data.Linq.Provider.Query.u.b(MethodCallExpression A_0)
   bij Devart.Data.Linq.Provider.Query.u.j(Expression A_0)
   bij Devart.Data.Linq.Provider.Query.u.b(LambdaExpression A_0)
   bij Devart.Data.Linq.Provider.Query.u.j(Expression A_0)
   bij Devart.Data.Linq.Provider.Query.u.i(Expression A_0)
   bij Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)
   bij Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Compile(Expression query)
   bij Devart.Data.Linq.CompiledQuery.a(IProvider A_0)
   bij Devart.Data.Linq.CompiledQuery.a(DataContext A_0, Object[] A_1)
   bij Devart.Data.Linq.CompiledQuery.Invoke[a,b,c](a A_0, b A_1)
   bij Jumbo.MVC.Models.TableData`1.CompiledGetById(DataContext dc, Int64 id) in D:\Projects\CodeLibrary\Jumbo.MVC\Models\TableData.cs:regel 139

I have no idea what to do next. I think the error occurs because of I'm using a cast to cast TModelClass to the IHasId interface (which is simple a requirement to have a long Id-property).

I hope you can help me out!

Kind regards,

Edwin

Posted: **Tue 04 Oct 2011 08:45**

Some more info:

* dataContext.GetTable(typeof(Whatever)) does not seem te work: it is required to write it as dataContext.Whatevers.

* Probably the specific DataContext is required in the call to CompiledQuery.Compile, instead of the standard Devart.Data.Linq.DataContext

So I don't see this is ever going to work. Whatever I do, I get all kinds of exception at the invocation of the compiled query. That puzzles me, because the above points seems to be compilation time issues: if at compilation time (CompiledQuery.Compile), a table can be accessed with .GetTable(typeof(Whatever)), it sure must be accessable at execution time of the compiled query, because then the real DataContext containing all the tables is used.

Maybe I'm just missing something...[/list]

Posted: **Tue 04 Oct 2011 09:35**

I couldn't get the above to work, so I thought lets try it the old fashioned way:

Code: Select all

string tablename = typeof(TModelClass).GetCustomAttributes()[0].Name;
string qry = String.Format("select * from {0} where id = {1}", tablename, "{0}");
var list = ((Devart.Data.Linq.DataContext)ContextValues.DataContext).ExecuteQuery(qry, id.Value);
return list.Single();

Unfortunately the performance of this is really horrible... I assumed (wishfull thinking) that the query (qry) would be cached, but this is not the case at all. So I cannot get a performance benefit for only compiling the query once, using ExecuteQuery. (Of course with 'once' I mean once for each table).

Any ideas to get a high performance, cached query, for all tables, like "select * from table where Id = {0}", without specifically implementing it for each table class?[/code]

Posted: **Tue 04 Oct 2011 13:43**

Thank you for the report, we have reproduced the issue. We will analyze the situation and inform you about the results.

As a workaround, you can try using the generic GetTable overload instead of the one taking a Type parameter in the CompiledGetById method:

Code: Select all

cqry = Devart.Data.Linq.CompiledQuery.Compile( 
  (Devart.Data.Linq.DataContext datacontext, long _id) => 
    datacontext.GetTable().Cast().Where(record => record.Id == _id)
);

Please tell us if this helps.

Posted: **Wed 05 Oct 2011 11:24**

Yep, it works!

Apart from the one time BuildQuery of approx. 238 ms, a single "get by id" costs 10 ms now, instead of a reoccuring 175 ms, so a big improvement!

Kind regards,

Edwin.

Posted: **Thu 06 Oct 2011 15:06**

Glad to see that the problem was resolved.

Devart Forums

Performance issue BuildQuery -- wrong usage?

Performance issue BuildQuery -- wrong usage?

Tried to use CompiledQuery.Compile, but it fails...