This question is locked. New answers and comments are not allowed.
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?
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?