CompiledQuery.Compile with open access?

[Jeff]

Hey guys, I'm seeing a lot of problems with lengthy load times on particular pages that I use OpenAccess. After using the OpenAccess Profiler, I found my queries were actually returning rather quickly, and no alerts were present. I thought perhaps since LINQ is renowned for it's slowness when uncompiled that I would try compiling my queries to see if that would make my page load faster.
I assumed that OpenAccess and entities were compatible and tried this: 

private static readonly Func<FEHPEntities, int, IQueryable<Order>> myquery =
            CompiledQuery.Compile<FEHPEntities, int, IQueryable<Order>>(
                (ctx, userid) => ctx.Orders.Where(
                    o => o.UserID == userid &&
                         (o.Paid == false || o.Jobs.Any(j => j.StatusID == 2))
                         && o.OrderDateTime != null));

But evidently I was wrong. It tells me that OpenAccess.FEHPEntities must be convertible to System.Data.Objects.ObjectContext. Am I approaching this incorrectly? Is it possible to create a compiled query within OpenAccess? Thanks so much for any help you can provide
-Jeff

[Greg]

CompiledQuery class is part of the Entity Framework and as such has nothing to do with OpenAccess. Those are two separate products. As far as I know queries are always automatically compiled and cached by OA.

[Jeff]

Thanks Greg for the quick reply. I had gathered that they weren't as related as I had assumed. I guess my question should have been: Despite the OpenAccess Profiler and Tuning Advisor saying that there are no issues with my queries and the total query execution time is less than 500ms, why is it taking 8-12 seconds for these linq queries to complete (a performance profiler ran on the page points to the queries as the culprit, specifically OpenAccess.Item:OpenAccessEnhancedGet_ItemLanguages taking 8 seconds and OpenAccess.DataObjects.ForwardQueryResult:Resolve taking 2 seconds in one case). What can I do to reduce this? How can I further debug the issue? 

[Greg]

I see nothing wrong with your query, it should be translated to a simple sql statement. Could you share more about your project, e.g. database schema, rlinq model, are you using fetch plans, etc?

[Jeff]

Well it's an existing site with well over 200 tables and many relaionships. Unfortunately I think it would be a bit of a security risk to share the entire rlinq model publicly, but here's a larger portion of the code for the specific page if it helps at all:

using Telerik.OpenAccess.FetchOptimization;
using System.Linq;
using OpenAccess;
using System.Collections.Generic;
namespace MyAccount.Controls 
{
    /// <summary>
	///		Summary description for Addresses.
	/// </summary>
	public partial class MessagesAndNotifications : MyAccountControl {
		public List<Order> NeedPaymentList = new List<Order>();
		public List<Order> ProofsList = new List<Order>();
		public List<ChildOrder> ChildOrderHoldList = new List<ChildOrder>();
		public List<Job> JobsNeedFilesList = new List<Job>();
		public List<Job> MailingJobsNeedFilesList = new List<Job>();
		public List<Job> ProofNeedsApprovalList = new List<Job>(); // (#01)
		public Pending_group_request pgr;
		public User UserRequest;
		public bool IsCreditHold;
 
		protected override void PreProcessRequest()
		{
            //grab certain objects with others to make reduce queries:
            FetchStrategy fetchStrategy = new FetchStrategy();
            fetchStrategy.LoadWith<Order>(o=>o.Jobs);
            fetchStrategy.LoadWith<Job>(j => j.Item, j => j.Proofs, j => j.Order);
	    fetchStrategy.LoadWith<ModifierDatum>(m => m.ModifierItem);
            DatabaseContext.FetchStrategy = fetchStrategy;
 
            //set us up the queries
		    NeedPaymentList = DatabaseContext.Orders.Where(
		        o => o.UserID == LoggedInUser.UserID &&
		        (o.Paid == false || o.Jobs.Any(j => j.StatusID == 2))
		        && o.OrderDateTime != null).ToList();
 
		    ProofsList = DatabaseContext.Orders.Where(
		        o => o.UserID == LoggedInUser.UserID &&
		        o.OrderDateTime != null && o.Paid == true &&
		        o.Jobs.Any(j =>
		                   j.StatusID != 5 && j.StatusID != 15 && j.StatusID != 17 && j.StatusID != 43 &&
		                   j.Proofs.Any(p => p.Accepted == false && p.Active == true))).OrderByDescending(o => o.OrderDateTime).ToList();
 
		    ChildOrderHoldList =
		        DatabaseContext.ChildOrders.Where(
                    c => c.Order.UserID == LoggedInUser.UserID && c.Order.Paid == true &&
		            c.Paid == false && c.ChildOrderItems.Any()).ToList();
 
		    JobsNeedFilesList =
		        DatabaseContext.Jobs.Where(
		            j =>
		            j.Order.UserID == LoggedInUser.UserID && j.Order.Paid == true && j.NeedFiles == true && j.StatusID != 15 &&
		            j.StatusID != 16 && j.StatusID != 17 && j.StatusID != 43).OrderByDescending(j => j.Order.OrderDateTime).
		            ToList();
 
		    MailingJobsNeedFilesList =
		        DatabaseContext.Jobs.Where(
		            j =>
		            j.Order.UserID == LoggedInUser.UserID && j.Order.Paid == true &&
		            (j.Item.ItemSubCategoryID == 91 || j.Item.ItemSubCategoryID == 92 || j.Item.ItemSubCategoryID == 93)
		            && j.NeedMailingList == true && j.StatusID != 15 && j.StatusID != 17 && j.StatusID != 43).
		            OrderByDescending(j => j.Order.OrderDateTime).ToList();
 
		    pgr = LoggedInUser.Pending_group_requests.FirstOrDefault();
 
		    IsCreditHold = LoggedInUser.CreditHold == true;
 
		    ProofNeedsApprovalList =
		        DatabaseContext.Jobs.Where(
		            j =>
		            j.Order.UserID == LoggedInUser.UserID && j.Order.Paid == true &&
		            j.StatusID == 5 && j.Proofs.Any(p => p.Accepted == false && p.Active == true)).ToList();
		}
	}
}

 
As you can imagine there is a good bit of looping through these things on the .ascx page, but the fetch strategies were added to prevent the need for further queries from that page. The question is: since the profiler says the queries are less than half a second in total, why does it take 10 seconds on the methods mentioned in the previous post? Is it taking a long time to render the data returned into the relevant objects?

[Greg]

It might be a wild guess, but my best bet would be to check just what exactly is sent to the db server.

OA might send an overly complicated SQL that takes a long time to execute, or decide that the query is too complex and evaluate some parts on the client which could lead to pulling lots of data that you wouldn't need, or send multiple queries when materializing entites doing unnecessary roundtrips. There's not much of a universal solution here: you could merge the NeedsPaymentList/ProofsList queries then sort them out later to save a roundtrip but you might not gain any performance from it. You could substitute your fetch strategy by executing two simple separate queries for parent and child entities and letting relation fixup bind them together, it might get faster, it might not.
You might want to tweak your queries until you see what you need. Generally you should be aiming for a fixed number of roundtrips that is not related to the number of materialized entities.

The actual method you referenced (Item.OpenAccessEnhancedGet_ItemLanguages) is actually the 'enhanced' ItemLanguages property on your Item class. The delay might be caused by lazy loading this collection (from the name I'm guessing this is a navigation property) on every Item. You might want to try adding LoadWith<Item>(i => i.ItemLanguages) to your fetch strategy. Sometimes OA only materializes a 'hollow' object for which only the primary key is actually loaded and the rest is deferred until the first non-PK property access. That means one query for each object, which should be avoided at all costs unless you're working with very few objects.

[Alexander]

Hello Jeff,

Greg is right, the queries are always compiled and cached, so later executions should perform faster even if different parameters are used.
At this point you could try different approaches (with or without fetch strategies) and decide which fits best your scenario. For example, the first query would execute much faster if the fetch strategy is disabled, as if it is enabled, OpenAccess would also read and materialize the referenced Job objects as well as their associated Items and Proofs. If you do not need all of these objects later, maybe it would be better to exclude some of the fetch strategy options.

One thing that you could find useful is the ability to see the generated SQL by calling ToString on the linq query:

var query = DatabaseContext.Orders.Where(
                o => o.UserID == LoggedInUser.UserID &&
                (o.Paid == false || o.Jobs.Any(j => j.StatusID == 2))
                && o.OrderDateTime != null);

string sql = query.ToString();
NeedPaymentList = query.ToList();

This way you can see more easily the complexity of the query and how much data is going to be read.
Hope that helps.

All the best,
Alexander
the Telerik team

[Jeff]

Thank you Greg for pointing me in the right direction, and all the useful information, and thank you Alexander for the additional information, I really appreciate it.
Alexander: I know that it isn't obvious from the supplied code, but I actually do reference those objects that I fetch on the associated ascx page. I actually pinpointed my issue in an insert that was happening many times per request (by accident I assure you) on the control that this one inherits from. But the information you guys have provided will help me in the future as I learn to use, and optimize my use of OpenAccess (which is by far the best ORM i've had a chance to work with). Thank you!