How to Improve Performance of Entity Framework Query ?

What is Eager Lording?

• When using Eager Loading, the related entities are loaded along with your target entity set
• You use an Include statement in your query to indicate which related entities you want to bring in

Bad way of using Eager Loading

• Single Call for Database Server with Many Include - BAD

public Invoice GetInvoice(string providerKey, string ownerKey, Guid id)

        {

            return (from owner in Catalog.Owners

                    where owner.Key == ownerKey

                    from invoice in owner.Invoices

                    where invoice.Provider.Key == providerKey

                    where invoice.Id == id

                    select invoice)

                    .Include(i => i.Owner.Credits)

                    .Include(i => i.Provider)

                    .Include(i => i.Items.Select(s => s.Allocation.Service))

                    .Include(i => i.Items.Select(s => s.Allocation.Pet))

                    .FirstOrDefault();

        }

Before breaking the query: Time for load the data  ~ 17 s

Best way of using Eager Loading

• 2 or More Small Data Request from Database Server - BEST

public Invoice GetInvoice(string providerKey, string ownerKey, Guid id)

        {

            //split the eager method(Include) for better performances 

            var invoiceObject = (from owner in Catalog.Owners

                                 where owner.Key == ownerKey

                                 from invoice in owner.Invoices

                                 where invoice.Provider.Key == providerKey

                                 where invoice.Id == id

                                 select invoice)

                                 .Include(i => i.Owner.Credits)

                                 .Include(i => i.Provider)
                                 .FirstOrDefault();

            invoiceObject = (from owner in Catalog.Owners

                             where owner.Key == ownerKey

                             from invoice in owner.Invoices

                             where invoice.Provider.Key == providerKey

                             where invoice.Id == id

                             select invoice)

                             .Include(i => i.Items.Select(s => s.Allocation.Service))

                             .Include(i => i.Items.Select(s => s.Allocation.Pet))
                             .FirstOrDefault();

                 return invoiceObject;

        }

After breaking the query: Time for load the data ~ 0.038 s



Performance Boost ~ Over 400 times than before

I have used JetBrains dotTrace Performance Tool for do the above Testing.

Do you Need to know more about JetBrains dotTrace ?

Are You an Entity Framework Lover ?

Conclusion

• If you would like to use Eager loading method for data loading with entity framework then use brake query method
• Don’t use more than 2 Includes on single query (MAX = 2 Includes per Query)
• Break them as above for better performances
• It will give Over 400X performance gain

P.S.

In detail Answers for Comments Posted by Anonymous September 6, 2012 12:23 AM and

Anonymous September 9, 2012 7:27 AM Developers :

Part 1 : 

You can see that when I ran the first part of the invoiceObject by using debuger it generated Dynamic Proxy for Provider object But Not for the Items List object. 

                            
Part 2 : 

In below screen you can see it Generated Data for Items List object But Nothing Happened to the Provider Object which was generated earlier (Your override concept is not happening here.Because 2 Sub queries are Merged by using Union. Theses are not happening like basic programming concepts.Its generating T-Sql by using Dynamic Proxies.).Because of that in this methodology (BEST) is giving Same Out Put as Earlier one (BAD) but Very Very Speedy way.(400 times faster than BAD scenario) 

 
                       

Happy Coding.

May Be Use Full To You :

How to Enable jQuery Intellisense in Visual Studio 2010 ?
How to Convert Linq Entity Query into T-SQL ?
How to Disable a Button Using Jquery and CSS ?