Simple Queries Causing Multiple Round-Trips to Database and Poor Performance

4 posts, 0 answers
  1. Matthew
    Matthew avatar
    1 posts
    Member since:
    Jul 2013

    Posted 24 Feb 2014 Link to this post

    We have been having problems with poor Query Generation time in Entity Framework, so decided to look for alternative frameworks. I spent half a day rewriting our data access layer to support DataAccess instead of Entity Framework. At first glance there was a noticeable performance boost in our application. Queries that had taken 4 seconds to generate with EF took only a split second with DataAccess.

    After doing a bit more testing, we discovered a few queries in our application that now take upwards of 30 seconds to run. Using SQL Profiler we noticed that the query was causing DataAccess to perform a round trip for every record in the database table, even though the end result of the query was supposed to be a paged list of 25 items.

    I put together a simple example that demonstrates the problem. Given the model:

        public class Item
        {
            public virtual Guid Id { get; set; }
            public string Name { get; set; }
            public IList<Item> Items { get; set; }
        }

    The following code results in a round trip for every single item in the data table:

                var context = new FluentModel();
                var q = context.GetAll<Item>().Select(i => new
                {
                    i.Id,
                    i.Name,
                    i.Items
                }).OrderByDescending(i => i.Items.Count());
                var result = q.Take(10).ToList();

    DataAccess first grabs every single item from the table, in my test case 10,000, and loads them in to memory. It then runs a query on each item to obtain Items.Count() for the order by. The OrderByDescending and Take calls are then run on the in memory table to get the final result.

    Interestingly though, this only seems to be a problem when using the Fluent API. The same query using the LINQ Query below works as expected.

                var context = new FluentModel();
                var q = from i in context.GetAll<Item>()
                        orderby i.Items.Count() descending
                        select new
                        {
                            i.Id,
                            i.Name,
                            i.Items
                        };
                var result = q.Take(10).ToList();

    The problem for us is that a large part of our queries are built by chaining fluent API expressions so writing LINQ Queries is not an option. Ideally we need to be able to chain any Fluent LINQ expression on our query and get the result in a single round trip. Am I missing something or is this something that DataAccess is not capable of handling?
       






  2. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 25 Feb 2014 in reply to Matthew Link to this post

    This is a limitation of DataAccess. All subsequent operations after a .Select() projection are executed in-memory except for Take() and Skip(). What you see is DA pulling the entire table from the database, sorts it in memory then performs the Skip/Take windowing. The LINQ syntax you provided is not equivalent to the fluent syntax because you have the OrderBy and Select swapped.
    And this is just the tip of the iceberg. You might have problems with query generation performance in EF (and rightfully so), but you will have problems in Telerik DataAccess with query generation, period. No matter how blazing fast it is compared to EF. Just a few examples:
    http://www.telerik.com/forums/wrong-sql-generated-with-joined-subqueries
    http://www.telerik.com/forums/invalid-sql-generated-for-bool-expressions
    http://www.telerik.com/forums/exception-when-using-local-variables-in-linq
    http://www.telerik.com/forums/invalid-sql-generated-with-navigation-properties-and-defaultifempty
    http://www.telerik.com/forums/invalid-sql-generated-with-or-and-subqueries
    If you're utilizing fluent query composition all over the place I suggest you keep looking for alternatives.
  3. DevCraft banner
  4. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 03 Mar 2014 Link to this post

    Hi Matthew,

    Please excuse us for the delayed response.

    Let me confirm that your observation on the behaviour of the query composed with the fluent LINQ syntax is correct. Indeed, in this scenario, Telerik Data Access would perform in-memory the ordering operation. Here, a good approach that will allow the whole statement to be executed on the server is to place the call to the OrderByDescending() method before the call to Select(). The query will look like this:
    var context = new FluentModel();
    var q = context.GetAll<Item>().OrderByDescending(i => i.Items.Count())
                                  .Select(i => new
                                  {
                                      i.Id,
                                      i.Name,
                                      i.Items
                                  });
    var result = q.Take(10).ToList();
    The SQL statement generated in this case is:
    SELECT  TOP(??T) a.*,  (SELECT COUNT(1)
                    FROM [Items] b WHERE a.[Id] = b.[ItemsId])  AS xj1
    FROM [Item] a
    ORDER BY xj1 DESC

    Additionally, I would suggest to you to take a look at the LINQ Guide section in our documentation. There you can find details and examples demonstrating the LINQ implementation of Telerik Data Access. 

    Regarding rewriting the rest of the queries on your side, let me assure you that Telerik Data Access does not impose the LINQ Query syntax as an explicit requirement for your code.

    I hope you find it feasible. If you need further assiatance, do not hesitate to get back to us.


    Regards,
    Doroteya
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  5. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 03 Mar 2014 Link to this post

    Hello Greg,

    Indeed, the flow of execution of queries that follow the pattern:
    var query = dbContext.GetAll<T>()
                   .Select(<selecting_expression>)
                   .OrderByDescending(<ordering_condition>);
    is exactly as the one you describe. Additionally, your suggestion about placing the call to the OrderByDescending() method before the call to the Select() method matches the recommended approach for composing this type of statements.

    Regarding the issues in the SQL statements generated by the LINQ implementation of Telerik Data Access, I am sorry for the inconvenience caused, and let me assure you that the effort on our side towards improvement and introduction of new capabilities is constant. In fact, some of the problems you experienced are already fixed and available for download with our latest official release and others can be easily worked around with the help of the rest of the features of Telerik Data Access. Please kindly find the details below:

    1. Wrong SQL generated with joined subqueries
    In this thread, Telerik Data Access was producing an incorrect query for the given LINQ expression, which utilized a join clause inside the where clause. This post suggest an alternative approach to the query composition that produces the needed SQL statement.

    2. Invalid SQL generated for bool expressions
    The issue reported in this thread is related to the generation of invalid SQL query based on a scenario which requires a cast from bool to bool?. The suggestion in it involves a change in the configuration of the bool properties in the model (to avoid the cast) and a slight modification of the where clause of the query.

    3. Exception when using local variables in LINQ
    Here, the scenario required the usage of a local IQueryable<T> variable in a LINQ expression. At the time it was reported, Telerik Data Access was supporting it, but with versions Q3 2013 SP2 and later, the statement produces the needed result. The details are available in this post.

    4. Invalid SQL generated with navigation properties and DefaultIfEmpty
    The issue experienced here is caused by the specific support for the DefaultIfEmpty() method on collection navigation properties. The default behaviour of Telerik Data Access in such situations is to perform an INNER JOIN between the main entity and the related one. At the time present, this continues to be a limitation on our side, but this post suggests a workaround, which utilizes our Fetch Plans capabilities and as a result simplifies the LINQ expression. You can find the details here.

    5. Invalid SQL generated with OR and subqueries
    The issue reported here, brought to our attention a bug in the SQL generation based on the Any() method in combination with OR in the where clause. At the time of the report, this post suggested a workaround, which suggests the usage of join statements. Currently, the issue is fixed, and the query that produced it retrieves the needed results. An additional fix is pending on our side, that will resolve the SQL generation in case Any() is combined with AND in the where clause.

    Do not hesitate to get back to us, in case you have questions or need further assistance.


    Regards,
    Doroteya
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top