0

So following on from this question, I was looking into why I was seeing poor query performance with SQL Server Compact that was blocking the UI thread. I have LazyLoading disabled and explicitly load the data that I need ahead of time.

To isolate this a little, I ran a test using the following query on the Northwind database to load all Orders, and for each Order load the associated OrderDetails, and for each OrderDetail load the Product and Supplier:

entities.Orders.Include(o => o.Order_Details
                        .Select(od => od.Products.Suppliers))
                .Load();

It took around 9 seconds to execute!

As a comparison I ran this against the Northwind database on my local SQL Server Express and it completed in < 0.1 seconds.

What is wrong with the .Select() that is causing the issue in this query? Why is this select causing such a long execution time for SQL Server Compact, but is fine when ran against SQL Server?

Community
  • 1
  • 1
Jason
  • 617
  • 9
  • 22
  • Did you look at the generated SQL (using DbContext..Log) and analyze it with SQL Server Compact Toolbox? – ErikEJ Feb 24 '15 at 18:09
  • @ErikEJ the SQL from both looks identical (although fieldnames between databases are different), but the main structure is identical. The execution plans are different, but I would expect them to be different due to the extra facilities that SQL Server provides. Nothing out of the ordinary... – Jason Feb 24 '15 at 19:24
  • Have you checked the underlying tables to compare # of records, indexes and if there are any triggers on the tables? Any of those things could affect query performance. – DeadZone Feb 24 '15 at 19:27
  • One more thing... If you manually run the generated SQL, do you see the same results as you get from going through EF? – DeadZone Feb 24 '15 at 19:29
  • Thanks for the comments @DeadZone. This particular query/database is just for an example (the database is available publicly). I am experiencing the problem across all SQL compact databases where I eager load sub-entities by using the .Include(....Select(...)) type pattern – Jason Feb 24 '15 at 20:15

0 Answers0