Hibernating Rhinos

Zero friction databases

Get an insight into what Entity Framework really does under the hoods using EFProf

When using relational databases like SQL Server, MySQL, PostgreSQL, using an O/RM tool like Entity Framework can drastically simplify how you interact with the database data by mapping (or abstracting) the relational data into objects, which are much easier to consume and use in your application.

The problem with such an abstraction is that in a lot of cases, you, or someone else in your team, may use the ORM in a way it you should not be used, which will lead to an application with bad performance. And the real issue about that is that most of the time developers tries to solve those issues down the road, when have some basic mistakes all over the place.

In order to avoid such errors, we developed the Entity Framework Profiler which will give you an insight about what is going on on under the hoods, by showing you the underline SQL that was generated and giving you some highlights/warnings on a bad SQL which should be improved.

In order to see the benefits you can get from using the Entity Framework Profiler we will take nopCommerce, a popular open source e-commerce application that make use of Entity Framework profiler it with the Entity Framework Profiler.

I opened the NopCommerce solution, set the Nop.Web as the default project, installed the EFProf NuGet package and run the website by hitting F5. The browser opened and I get an installation form to fill. I enter the connection string to the SQL Server and I choose the “Use sample data” option. Now let look on the profiler to see what actually the installation action did.

Step1

See what happened. We opened the object context session 3 times. The first object context does nothing. The second one executes 69 queries, most of them to create indexes or stored procedures.  And the third one is inserting our sample data. In both of them we get some alerts such as Select N+1, Too Many Joins or Too Many Database Calls. But since this is an installer which take place just once so we can ignore that.

Now, lets make a search for a product. Searching for shoes, make like 29 queries to the DB:

Step4

As we can see in the alerts section, we can lower the number of queries that we having by fixing the select N+1 issue that we have here.

Clicking on the Computers category and after that on the electronics category and looking on the profiler, I see the following:

Step5

Note that it executed 87 queries. Let pick one of them and analyze it:

Step6

Wow, can you figure out when this query tries to do? I cannot. There is not wondering why the profiler gives the following alerts on this statement:

Step7

So, how can I figure it out? Without the EF Profiler, there is no why to connect between an SQL query to the code that was responsible to generate it. But with EFProf we can just look on the stack trace window and jump right to the code that generated this query:

Step8

And here it is:

/// Gets all categories filtered by parent category identifier
/// </summary>
/// <param name="parentCategoryId">Parent category identifier</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Category collection</returns>
public virtual IList<Category> GetAllCategoriesByParentCategoryId(int parentCategoryId,
    bool showHidden = false)
{
    string key = string.Format(CATEGORIES_BY_PARENT_CATEGORY_ID_KEY, parentCategoryId, showHidden, _workContext.CurrentCustomer.Id, _storeContext.CurrentStore.Id);
    return _cacheManager.Get(key, () =>
    {
        var query = _categoryRepository.Table;
        if (!showHidden)
            query = query.Where(c => c.Published);
        query = query.Where(c => c.ParentCategoryId == parentCategoryId);
        query = query.Where(c => !c.Deleted);
        query = query.OrderBy(c => c.DisplayOrder);

        if (!showHidden)
        {
            //ACL (access control list)
            var allowedCustomerRolesIds = _workContext.CurrentCustomer.CustomerRoles
                .Where(cr => cr.Active).Select(cr => cr.Id).ToList();
            query = from c in query
                    join acl in _aclRepository.Table
                    on new { c1 = c.Id, c2 = "Category" } equals new { c1 = acl.EntityId, c2 = acl.EntityName } into c_acl
                    from acl in c_acl.DefaultIfEmpty()
                    where !c.SubjectToAcl || allowedCustomerRolesIds.Contains(acl.CustomerRoleId)
                    select c;

            //Store mapping
            var currentStoreId = _storeContext.CurrentStore.Id;
            query = from c in query
                    join sm in _storeMappingRepository.Table
                    on new { c1 = c.Id, c2 = "Category" } equals new { c1 = sm.EntityId, c2 = sm.EntityName } into c_sm
                    from sm in c_sm.DefaultIfEmpty()
                    where !c.LimitedToStores || currentStoreId == sm.StoreId
                    select c;

            //only distinct categories (group by ID)
            query = from c in query
                    group c by c.Id
                    into cGroup
                    orderby cGroup.Key
                    select cGroup.FirstOrDefault();
            query = query.OrderBy(c => c.DisplayOrder);
        }

        var categories = query.ToList();
        return categories;
    });

}

As you can see here, it at least using a cacheManager, but even with it we see un-tuned queries.

When you develop an application and uses the Entity Framework Profiler, you can be aware about those issue right from the beginning and avoid repeating the same mistakes, over and over again.

Tags:

Posted By: Fitzchak Yitzchaki

Published at

Originally posted at

Comments

No comments posted yet.

Comments have been closed on this topic.