Hi. The view generation takes a long time. In out project we have just 300 entities and already 80 seconds on a powerful system. Also, it just uses one cpu, which is not a surprise for a very complex algorithm. The msdn site assures that the algorithm used is exponential in the worst case, and their own model with 1000+ tables takes more than an hour.

What part of the view generation takes exponential time? Is the algorithm already expressed in a more abstract manner? Or does one need to read the entire view generation code (and of course some accompanying code, to know the data structures, preprocesses etc)? I really like to be able to make my move on the code, but I really hope for some information better that the raw source code.

Also, please note that the power tools throws exceptions for many users, so view generation for Code first is impossible for them (including me!)

Another option is to use T4 templates for generating views for CodeFirst apps. They are on the VS Gallery. Here is the blog post the describes how to use them: http://blog.3d-logic.com/2012/06/13/entity-framework-codefirst-view-generation-templates-on-visual-studio-code-gallery/

The foundations of view generation are outlined in this paper: http://www.cse.buffalo.edu/~mpetropo/CSE736-SP10/pubs/p461-melnik.pdf

Hi
Thank. I'll look to know if the T4 generator works for me. Also, thank you for pointing to the paper. That's exactly what I'm looking for.

Ok. I read your paper. It always feels good to know about the efforts that underlie a working system. Especially, the emphasis on a formal approach to ensure a lossless roundtrip. I suggest that you put a link to this paper somewhere that more people may see it and better appreciate the system they use.

BTW, as I responded to your blog post, I've been unable to generate views (we building EF6 an use it), so I still need to improve my speed. I have two suggestions:

1. Allowing some kind of runtime view generation without checking the roundtrip condition (It is implied from the paper that it takes most of the view generation time). Most of our developer work does not include changing the model. Even when they do change the model, most of their work is about adding a simple property, or a very simple table. It was great if we could disable this, and have a nightly, or hourly job make sure the view is in good conditions. In other words, roundtrip condition checking be removed from every first run of the application and delegated to the development team (trust us, we can handle such a task!)
2. I profiled the view generation, and 81% of the time was spent in ViewGenContext.CreateConstraintsForForeignKeyAssociationsAffectingThisWarapper(). Most of the time elapsed in this code is spent in Enumerating an IEnumerable, obtained by filtering EdmEntityContainer.BaseEntitySets. The moral of this for me is that a simple ReadOnlyCollection is not appropriate to keep this data, and a better data structure will yield a 5X speed boost. Fortunately, this collection is not used in many places among the code and I'll be able to understand all queries we perform on the list. I'll inform you if any improvements are made.

Hi.

The problem with CreateConstraintsForForeignKeyAssociationsAffectingThisWarapper() is that allForeignKeyAssociationSets and oneToOneForeignKeyAssociationsForThisWrapper are not materialized, the last query: oneToOneForeignKeyAssociationSetsForThisWrapper makes unnecessarily large loops inside unnecessarily large loops.

I put .ToList() after the query that generates allForeignKeyAssociationSets. Also, merged the first and second queries that make oneToOneForeignKeyAssociationsForThisWrapper and appended it with another ToList(). finally I had to add .ToList() to the third query as well.

These changes cut the total time spent in this method from 70 seconds to about 120ms. The difference was much greater that what I expected, so I actually had to ask my colleagues to verify I did not break anything.

The complete code for the updated method is:

private void CreateConstraintsForForeignKeyAssociationsAffectingThisWarapper(
            FragmentQueryKB rightKB, MemberDomainMap rightDomainMap)
        {
            PublicData.Stopwatch.Start();
            //First find the entity types of the sets in these cell wrappers.
            var entityTypes = m_cellWrappers.Select(it => it.RightExtent).OfType<EntitySet>().Select(it => it.ElementType);
            //Get all the foreign key association sets in these entity sets
            var allForeignKeyAssociationSets =
                    m_entityContainerMapping.EdmEntityContainer.BaseEntitySets.OfType<AssociationSet>().Where(it => it.ElementType.IsForeignKey).ToList();
            //Find all the foreign key associations that have corresponding sets
            var oneToOneForeignKeyAssociationsForThisWrapper = allForeignKeyAssociationSets.Select(it => it.ElementType).Where(
                            it => (it.AssociationEndMembers.All(endMember => endMember.RelationshipMultiplicity == RelationshipMultiplicity.One))).ToList();
            //Find all the 1:1 associations from the above list
            //oneToOneForeignKeyAssociationsForThisWrapper =
            //        oneToOneForeignKeyAssociationsForThisWrapper.Where(
            //                it => (it.AssociationEndMembers.All(endMember => endMember.RelationshipMultiplicity == RelationshipMultiplicity.One)));
            //Filter the 1:1 foreign key associations to the ones relating the sets used in these cell wrappers.
            oneToOneForeignKeyAssociationsForThisWrapper =
                    oneToOneForeignKeyAssociationsForThisWrapper.Where(
                            it => (it.AssociationEndMembers.All(endMember => entityTypes.Contains(endMember.GetEntityType())))).ToList();

            //filter foreign key association sets to the sets that are 1:1 and affecting this wrapper.
            var oneToOneForeignKeyAssociationSetsForThisWrapper =
                    allForeignKeyAssociationSets.Where(it => oneToOneForeignKeyAssociationsForThisWrapper.Contains(it.ElementType));

            //Collect the facts for the foreign key association sets that are 1:1 and affecting this wrapper
            foreach (var assocSet in oneToOneForeignKeyAssociationSetsForThisWrapper)
            {
                rightKB.CreateEquivalenceConstraintForOneToOneForeignKeyAssociation(assocSet, rightDomainMap);
            }
            PublicData.Stopwatch.Stop();
        }

Some statistics and an apology: we have about 400 entities, prior to this code change, our first query took 67047 ms from which 63579 ms was spent in the given method, now it takes 4058 ms, 117 ms of which is in the aforementioned method.

The first and last line of the changed code I submitted contains the Time-keeping code I wrote for myself, sorry. 

Hi Alireza,

This looks really promising and we would love to consider it for EF6. Would it be possible for you to contribute it?

Thanks,

Andrew.

Hello Andrew.

I did it now. the relevant pull request is: http://entityframework.codeplex.com/SourceControl/network/forks/AlirezaHaghshenas/ImproveViewGenerationSpeed/contribution/3507.

Bests.

Thanks @Alireza!  I came across that issue a while ago, but didn't have the time or understanding to fix it: http://stackoverflow.com/questions/10757019/entity-framework-initialization-is-slow-what-can-i-do-to-bootstrap-it-faster.  Nice work.  Now if only they'd backport the patch to EF5...

Hi, the fix made by Alireza works like a charm view generation time is decreased in my case from 25 min to 2!

Is there any chances to have an update of EF5 with those changes??

Max

That's good news, I'm happy to hear that.

I hope that other areas of code that affect startup time be treated as well. Currently, there are certain features of EF that make it very appealing in serious projects, while some unexpected gotchas tend to prevent that.

I wish that EF team used to spend more time on fixing such problems, rather than adding new fancy features.