locked
Are pre-generated ESQL views used for queries with OFTYPE operator? RRS feed

  • Question

  • My data model currently consists of about 500 entities. I am developing a desktop application and so I care about its startup time and latency. As the model grew, I saw startup performance drop to unacceptable values. I started pre-generating the views at build time, as per Performance Considerations (Entity Framework). The performance rose again and I thought that was it. But recently I started getting reports that certain parts of my application are unresponsive on first access. Turns out that those parts issue queries with the OfType<T> LINQ operator. It seems that 1) something is being generated further at runtime for those queries that is not being generated for other queries (ie. ones that query just the base type, without OfType), and 2) no pre-generated views are being accessed (breakpoints in the GetViewXXX() methods are not being hit), even when they are for other queries.

    The issue is easily reproducible: create a data model (model-first) with a base type and two derived ones. It doesn't matter if you use TPH or TPT mapping strategy. Pre-generate the ESQL views using EdmGen and compile them with the generated classes. Set breakpoints in the GetViewXXX() methods in the generated views file. Now observe how executing a query for a derived type, like

    ctx.TptSet.OfType<TptDerived1>().ToArray()

    never causes a breakpoint to be hit, as if the pre-generated views are being ignored. Only executing queries for the base type, like

    ctx.TptSet.ToArray()

    iterates through the pre-generated views, and what's more important, happens instantly, even on first execution.

    Incidentally, if you look at the source of EF, the OfType query execution seems to be hitting the System.Data.Mapping.StorageMappingItemCollection+ViewDictionary.SerializedGeneratedViewOfType method. It does seem to look for generated views in some kind of dictionary.

    Is it possible to pre-generate the something that OfType queries need at build time? Is the slowdown I see occuring during the "Preparing the query" operation as referred to in the linked page, and so is not related to the "Generating views" operation?



    • Edited by Janusz Nykiel Thursday, September 1, 2011 3:47 PM error, clarification
    Thursday, September 1, 2011 3:43 PM

Answers

  • Hi Janusz,

    Thank you for reporting this issue.  

    For queries with OfType, we try to generate a simpler view and if can,  we don’t use the pre-generated view.   While this typically leads to better SQL, as you have observed, it does not take advantage of pregenerated views.  

    A possible workaround for this limitation that would force usage of the pre-generated view is to modify the query in the following manner

    ctx.TptSet.Select(p => new { p1 = p, p2 = p }).Select(p => p.p1).OfType<SimplifiedModel.TptEntityDerived1>()

    However, while the pre-generated view is used in this case, the generated SQL is likely to be more complex as it is built on the view for the entire entity set.

     

    Thanks,

    Kati Iceva,

    Entity Framework Developer


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Janusz Nykiel Wednesday, October 12, 2011 6:27 PM
    Wednesday, October 12, 2011 5:01 PM

All replies

  • On 9/1/2011 11:43 AM, Janusz Nykiel wrote:
     
    > My data model currently consists of about 500 entities.
     
    The fact that you have 500 some entities on a model is an issue. May be
    you need to find a way to segregate entities on more than one model to
    improve performance -- possibly.
     
    Thursday, September 1, 2011 3:49 PM
  • Hi, Januse

    Thanks for your post.

    According to your description, I think the key of the issue may be not the OfType operator, but your large model.  In general we should start thinking about breaking up a model when it has reached 50-100 entities. However, you have 500 entities in the data model.

    One of the major problems you could run into with models generated from big database schemas is performance. There are two main areas where performance gets impacted because of the size of the model:

    1. metadata load time.
    2. view generation

               View generation is a process that compiles the declarative mapping provided by the user into client side Entity Sql views that will be used to query and store Entities to the database. The process runs the first time either a query or SaveChanges happens. The performance of view generation step not only depends on the size of your model but also on how interconnected the model is. If two Entities are connected via an inheritance chain or an Association, they are said to be connected. Similarly if two tables are connected via a foreign key, they are connected. As the number of connected Entities and tables in your schemas increase, the view generation cost increases.

    I can see you use the pre-generate views to improve the performance. However, there're some other methods can do the same thing too. Please read the following articles:

    http://blogs.msdn.com/b/adonet/archive/2008/11/24/working-with-large-models-in-entity-framework-part-1.aspx

    http://blogs.msdn.com/b/adonet/archive/2008/11/25/working-with-large-models-in-entity-framework-part-2.aspx

    I hope this can help you.

     

    have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 7, 2011 6:36 AM
  • I am aware of these guidelines. I am pointing out an issue - inability to pre-generate views used at runtime by queries with OfType operator - that manifests itself regardless of model size or query complexity, and I'm guessing that even at "reasonable" 50-100 entities the delay on first execution would already be noticeable enough to hinder the user experience. I'd like to know if this behavior is by design. I started to work around this problem by doing

    ctx.BaseSet.OfType<Derived>().ToTraceString()

    in a seperate thread, as soon as application starts, for several derived types I know the user will probably be soon querying. The views get "pre"-generated before actual queries use them. It would be best if it was possible to do so at build time, for all derived types present in all sets in the container.

    Wednesday, September 7, 2011 10:53 AM
  • Sheer number of Entities in this large model is likely the primary cause of your performance issue. Can you please create a scale-down stand-alone version of your project, zip the entire solution and email it to me for further testing? (cts-enamulkh@live.com)
    Thursday, September 15, 2011 12:19 AM
  • I've sent you an email with attached solution that reproduces the problem. To reiterate: the problem with OfType queries manifests itself regardless of model size. There are two data models in the solution: my actual data model, and a simplest model that is used to illustrate the problem.

    Thursday, September 15, 2011 7:35 AM
  • Got it....thanks!
    Friday, September 16, 2011 8:41 PM
  • Hi Janusz,

    Thank you for reporting this issue.  

    For queries with OfType, we try to generate a simpler view and if can,  we don’t use the pre-generated view.   While this typically leads to better SQL, as you have observed, it does not take advantage of pregenerated views.  

    A possible workaround for this limitation that would force usage of the pre-generated view is to modify the query in the following manner

    ctx.TptSet.Select(p => new { p1 = p, p2 = p }).Select(p => p.p1).OfType<SimplifiedModel.TptEntityDerived1>()

    However, while the pre-generated view is used in this case, the generated SQL is likely to be more complex as it is built on the view for the entire entity set.

     

    Thanks,

    Kati Iceva,

    Entity Framework Developer


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Janusz Nykiel Wednesday, October 12, 2011 6:27 PM
    Wednesday, October 12, 2011 5:01 PM
  • Thank you! I've noticed that the SQL indeed is more complex for the workaround query in the simplified model, yet in the actual model it is the same as for the normal query. I now have something to work with. It's nice to have options.
    Wednesday, October 12, 2011 6:26 PM