none
EntityFramework Poor Query Performance with SQL Compact RRS feed

  • Question

  • I raised this question on Stack Overflow 3 months ago but it has not been answered and I am having more and more instances of this problem.

    The problem is if I nest .Include and .Select method calls within the linq, the query has terrible performance. In this post I am just using the standard Northwind sample database as the problems can be seen there as well.

    For example, from the NorthWind database lets say I want to return all Orders, and within each order I want the OrderDetails populated, and then within the OrderDetails entity, I also want the Products and suppliers also populated.

    The query I have is 

    	var list = entities.Orders.Include(o => o.Order_Details.Select(od => od.Products.Suppliers)).ToList();

    When I run this query, it takes nearly 9 seconds to execute!

    As an alternative, I can individually load all the tables concerned and then just access the Orders entity:

    entities.Orders.Load();
    entities.Order_Details.Load();
    entities.Products.Load();
    entities.Suppliers.Load();        
    
    var list = entities.Orders.ToList();

    This executes in < 0.25 seconds

    Obviously I don't want to have to load data like this, I want to be able to query the data using linq, and only pull back the actual data I need.

    This is only against SQL Compact databases.  There are no missing indexes, or problems with the database. Indeed, examples against the Northwind sample database exhibit the same problems. If I move the database to SQL server, it executes perfectly.  

    If I log the SQL that is emitted, I get this:

    Opened connection at 17/06/2015 14:16:29 +01:00
    
    SELECT 
        [Project1].[Order ID] AS [Order ID], 
        [Project1].[Customer ID] AS [Customer ID], 
        [Project1].[Employee ID] AS [Employee ID], 
        [Project1].[Ship Name] AS [Ship Name], 
        [Project1].[Ship Address] AS [Ship Address], 
        [Project1].[Ship City] AS [Ship City], 
        [Project1].[Ship Region] AS [Ship Region], 
        [Project1].[Ship Postal Code] AS [Ship Postal Code], 
        [Project1].[Ship Country] AS [Ship Country], 
        [Project1].[Ship Via] AS [Ship Via], 
        [Project1].[Order Date] AS [Order Date], 
        [Project1].[Required Date] AS [Required Date], 
        [Project1].[Shipped Date] AS [Shipped Date], 
        [Project1].[Freight] AS [Freight], 
        [Project1].[C1] AS [C1], 
        [Project1].[Order ID1] AS [Order ID1], 
        [Project1].[Product ID] AS [Product ID], 
        [Project1].[Unit Price] AS [Unit Price], 
        [Project1].[Quantity] AS [Quantity], 
        [Project1].[Discount] AS [Discount], 
        [Project1].[Product ID1] AS [Product ID1], 
        [Project1].[Supplier ID] AS [Supplier ID], 
        [Project1].[Category ID] AS [Category ID], 
        [Project1].[Product Name] AS [Product Name], 
        [Project1].[English Name] AS [English Name], 
        [Project1].[Quantity Per Unit] AS [Quantity Per Unit], 
        [Project1].[Unit Price1] AS [Unit Price1], 
        [Project1].[Units In Stock] AS [Units In Stock], 
        [Project1].[Units On Order] AS [Units On Order], 
        [Project1].[Reorder Level] AS [Reorder Level], 
        [Project1].[Discontinued] AS [Discontinued], 
        [Project1].[Supplier ID1] AS [Supplier ID1], 
        [Project1].[Company Name] AS [Company Name], 
        [Project1].[Contact Name] AS [Contact Name], 
        [Project1].[Contact Title] AS [Contact Title], 
        [Project1].[Address] AS [Address], 
        [Project1].[City] AS [City], 
        [Project1].[Region] AS [Region], 
        [Project1].[Postal Code] AS [Postal Code], 
        [Project1].[Country] AS [Country], 
        [Project1].[Phone] AS [Phone], 
        [Project1].[Fax] AS [Fax]
        FROM ( SELECT 
            [Extent1].[Order ID] AS [Order ID], 
            [Extent1].[Customer ID] AS [Customer ID], 
            [Extent1].[Employee ID] AS [Employee ID], 
            [Extent1].[Ship Name] AS [Ship Name], 
            [Extent1].[Ship Address] AS [Ship Address], 
            [Extent1].[Ship City] AS [Ship City], 
            [Extent1].[Ship Region] AS [Ship Region], 
            [Extent1].[Ship Postal Code] AS [Ship Postal Code], 
            [Extent1].[Ship Country] AS [Ship Country], 
            [Extent1].[Ship Via] AS [Ship Via], 
            [Extent1].[Order Date] AS [Order Date], 
            [Extent1].[Required Date] AS [Required Date], 
            [Extent1].[Shipped Date] AS [Shipped Date], 
            [Extent1].[Freight] AS [Freight], 
            [Join2].[Order ID] AS [Order ID1], 
            [Join2].[Product ID1] AS [Product ID], 
            [Join2].[Unit Price1] AS [Unit Price], 
            [Join2].[Quantity] AS [Quantity], 
            [Join2].[Discount] AS [Discount], 
            [Join2].[Product ID2] AS [Product ID1], 
            [Join2].[Supplier ID1] AS [Supplier ID], 
            [Join2].[Category ID] AS [Category ID], 
            [Join2].[Product Name] AS [Product Name], 
            [Join2].[English Name] AS [English Name], 
            [Join2].[Quantity Per Unit] AS [Quantity Per Unit], 
            [Join2].[Unit Price2] AS [Unit Price1], 
            [Join2].[Units In Stock] AS [Units In Stock], 
            [Join2].[Units On Order] AS [Units On Order], 
            [Join2].[Reorder Level] AS [Reorder Level], 
            [Join2].[Discontinued] AS [Discontinued], 
            [Join2].[Supplier ID2] AS [Supplier ID1], 
            [Join2].[Company Name] AS [Company Name], 
            [Join2].[Contact Name] AS [Contact Name], 
            [Join2].[Contact Title] AS [Contact Title], 
            [Join2].[Address] AS [Address], 
            [Join2].[City] AS [City], 
            [Join2].[Region] AS [Region], 
            [Join2].[Postal Code] AS [Postal Code], 
            [Join2].[Country] AS [Country], 
            [Join2].[Phone] AS [Phone], 
            [Join2].[Fax] AS [Fax], 
            CASE WHEN ([Join2].[Order ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
            FROM  [Orders] AS [Extent1]
            LEFT OUTER JOIN  (SELECT [Extent2].[Order ID] AS [Order ID], [Extent2].[Product ID] AS [Product ID1], [Extent2].[Unit Price] AS [Unit Price1], [Extent2].[Quantity] AS [Quantity], [Extent2].[Discount] AS [Discount], [Extent3].[Product ID] AS [Product ID2], [Extent3].[Supplier ID] AS [Supplier ID1], [Extent3].[Category ID] AS [Category ID], [Extent3].[Product Name] AS [Product Name], [Extent3].[English Name] AS [English Name], [Extent3].[Quantity Per Unit] AS [Quantity Per Unit], [Extent3].[Unit Price] AS [Unit Price2], [Extent3].[Units In Stock] AS [Units In Stock], [Extent3].[Units On Order] AS [Units On Order], [Extent3].[Reorder Level] AS [Reorder Level], [Extent3].[Discontinued] AS [Discontinued], [Extent4].[Supplier ID] AS [Supplier ID2], [Extent4].[Company Name] AS [Company Name], [Extent4].[Contact Name] AS [Contact Name], [Extent4].[Contact Title] AS [Contact Title], [Extent4].[Address] AS [Address], [Extent4].[City] AS [City], [Extent4].[Region] AS [Region], [Extent4].[Postal Code] AS [Postal Code], [Extent4].[Country] AS [Country], [Extent4].[Phone] AS [Phone], [Extent4].[Fax] AS [Fax]
                FROM   [Order Details] AS [Extent2]
                INNER JOIN [Products] AS [Extent3] ON [Extent2].[Product ID] = [Extent3].[Product ID]
                LEFT OUTER JOIN [Suppliers] AS [Extent4] ON [Extent3].[Supplier ID] = [Extent4].[Supplier ID] ) AS [Join2] ON [Extent1].[Order ID] = [Join2].[Order ID]
        )  AS [Project1]
        ORDER BY [Project1].[Order ID] ASC, [Project1].[C1] ASC
    
    
    -- Executing at 17/06/2015 14:16:29 +01:00
    
    -- Completed in 5 ms with result: SqlCeDataReader
    
    
    
    Closed connection at 17/06/2015 14:16:37 +01:00

    In the above example, the log says it is executed in 5ms using SqlCeDataReader, however it then blocks for around 8-9 seconds and then emits the line "Closed connection..."

    Any ideas what is causing the poor performance?

    Thanks

    Wednesday, June 17, 2015 1:22 PM

Answers

  • > is there a way I can re-write the original linq query ...?

    Not using .Include.  The alternative is to load the related entities with separate queries and let the DbContext "knit" them together by fixing up the relationships.

    entities.Order_Details.Load();
    entities.Products.Load();
    entities.Suppliers.Load();        
    
    var list = entities.Orders.ToList();
    

    (You don't need to .Load() and .ToList() Orders)

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 22, 2015 3:18 PM

All replies

  • >Any ideas what is causing the poor performance?

    My guess is the lack of a query optimizer in CE?

    for CE, why not just use lazy loading?

    >As an alternative, I can individually load all the tables concerned

    Also not really a bad option.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, June 17, 2015 1:27 PM
  • Hello JasonRiley,

    >>Any ideas what is causing the poor performance?

    You could use lazy-load as David mentioned, or if you are using ObjectContext, try the ObjectContext.LoadProperty Method (Object, String), the include method would result repeat data for the master table(Orders in your provided query), you could check this link, there is a detail description about this.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 18, 2015 1:39 AM
    Moderator
  • Hi David,

    Thanks for the reply. Unfortunately Lazy Loading isn't useful in this case as a lot of the data has to be loaded upfront, hence the use of Eager loading.

    >Any ideas what is causing the poor performance?

    >>My guess is the lack of a query optimizer in CE?

    I am coming to the same conclusion.  It appears to be the depth of .Include statements that causes the problem. If I break the request into two lines:

    entities.Products.Include("Suppliers").Load();
    var list = entities.Orders.Include("Order_Details").ToList();

    Then the overall execution time is significantly reduced. Not as quite as fast as individually loading the tables but <0.5 sec.

    The nested .Include looks like it generates the unwieldy SQL and whilst that can be handled just fine by SQL Server through optimisation, SQL Compact struggles.  I am just surprised that more people haven't reported problems, I guess there aren't many people using EntityFramework with SQL Compact...either that, or they are all using LazyLoading.

    J

    Thursday, June 18, 2015 9:31 AM
  • Hi Fred,

    Thanks for the reply.  I can't use Lazy Loading in this situation, the data has to be loaded up front. I did check out your link, but I'm not using ObjectContext so thanks for the suggestion but that isn't really an option.

    J

    Thursday, June 18, 2015 9:42 AM
  • What EF version do you use?

    Have you tried DbContext.Configuration.UseDatabaseNullSemantics = false ?


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, June 18, 2015 12:36 PM
  • Hello JasonRiley,

    >> I did check out your link, but I'm not using ObjectContext so thanks for the suggestion but that isn't really an option.

    If you are using DbConext, you could still use the LoadProperty as below:

    using (DFDBEntities1 db = new DFDBEntities1())

    {

                    db.Database.Log = Console.Write;

                    db.Configuration.LazyLoadingEnabled = false;

                    ObjectContext objectContext = ((IObjectContextAdapter)db).ObjectContext;

                    Order order = db.Orders.FirstOrDefault();

                    objectContext.LoadProperty(order, o => o.OrderDetailSets);

       }

    It would query these two tables separately rather than generate a join by using include.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 19, 2015 4:33 AM
    Moderator
  • Hi Erik,

    Thanks for your reply,

    I am using V6.1.1 of EntityFramework and EntityFramework.SqlServerCompact in the actual project that I am seeing problems with, and v6.1.3 of EntityFramework and EntityFramework.SqlServerCompact in the test project with the NorthWind examples.

    I had not tried specifying the UseDatabaseNullSemaintic = false. I have made that change and run some tests, but it doesn't appear to make any difference, the emitted SQL is the same, and thus the resulting execution time is also the same.

    Note that I can take the generated SQL (above) and run it within SSMS or SQL Server Compact Toolbox and I get the same performance/execution time.

    J

    Friday, June 19, 2015 9:52 AM
  • Thanks for the info.

    You need to rewrite / redo the Query then in order to avoid OUTER JOINs

    https://technet.microsoft.com/en-us/library/ms172984(v=sql.110).aspx


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Friday, June 19, 2015 9:55 AM
  • Hi Erik,

    Thanks, I read the link to the article.  I understand that if I were writing the SQL manually I shouldn't be using outer joins, but it is the "linq to sql" that is generating the sql, so that is not really under my control.  

    Are you suggesting that I re-write the linq so that it doesn't generate the outer joins? If that is the case, then I will have some very specific code intended for use only with SQL Compact. If I move the database to SQL Server, I would have to revisit all the linq, or put something in place now to cope with executing different linq depending on the database.

    What it looks like to me at the moment is that eager loading really doesn't work very well with SQL Compact when eager loading deeper tables/objects.  One level is fine, but two or more, just doesn't work well. 

    Thanks

    J

    Saturday, June 20, 2015 9:49 AM
  • Why need a special version of the rewrite for SQL Server, I am sure the rewritten LINQ Query will Work well there as well.

    And you will have to face the fact that SQL Server Compact and SQL Server are two completely different products, that share the same API (T-SQL)


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Saturday, June 20, 2015 9:55 AM
  • Hi Erik,

    Yes that is a good point, they are different products, I shouldn't be expecting them to operate the same.

    Maybe I'll post this as a separate question on SO but out of interest, is there a way I can re-write the original linq query (that will result in different SQL that doesn't have the outer joins), whilst still eager loading data with the .Include method, ? 

    Thanks

    J

    Monday, June 22, 2015 3:04 PM
  • > is there a way I can re-write the original linq query ...?

    Not using .Include.  The alternative is to load the related entities with separate queries and let the DbContext "knit" them together by fixing up the relationships.

    entities.Order_Details.Load();
    entities.Products.Load();
    entities.Suppliers.Load();        
    
    var list = entities.Orders.ToList();
    

    (You don't need to .Load() and .ToList() Orders)

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 22, 2015 3:18 PM
  • Hi David,  

    Thanks for that, yes that was my backup solution. I was just curious (more for learning than anything else), to see if there was a way to re-write the linq to do it in one line, maybe by using a linq join or something like that to hint to linq to entities how to generate the sql.  

    Thanks

    J

    Monday, June 22, 2015 3:38 PM