2
\$\begingroup\$

I am fairly new to Entity Framework and I am not sure I am really using it to it's max potential. I have this query, that IMHO seems to take a little longer than I feel it should. I have tried various combinations to retrieve all the data I need making as few connections as possible and so far this query yeilds the best results with an average of about 320 ms. Is there anything I can do to make this query faster?

Something to note is that the include for Awe_PageContainerBlurb is a many-to-many relationship based on 3 tables.

var roles = from role in db.aspnet_Roles
            join userRole in db.aspnet_UsersInRoles on role.RoleId equals userRole.RoleId
            where userRole.UserId == userID
            select role;

var awePageData = ((ObjectQuery<Awe_Page>)(from page in db.AweBjects.OfType<Awe_Page>()
                                           join app in db.aspnet_Applications on page.ApplicationId equals app.ApplicationId
                                           where app.ApplicationName == user.ApplicationName
                                               && page.Active && page.Name == pageName
                                           from pageRole in page.aspnet_Roles
                                           where roles.Contains(pageRole)
                                           select page))
                                           .Include("Awe_PageContainerBlurb.Awe_Container")
                                           .Include("Awe_PageContainerBlurb.Awe_Blurb");

var menuItems = from menu in db.AweBjects.OfType<Awe_Menu>()
                join app in db.aspnet_Applications on menu.ApplicationId equals app.ApplicationId
                where app.ApplicationName == user.ApplicationName
                    && menu.Active 
                from menuRole in menu.aspnet_Roles
                where roles.Contains(menuRole)
                select menu;
\$\endgroup\$
  • 1
    \$\begingroup\$ You can also trace the sql statement that produced here and use the tuning adviser or an sql expert to improve it. This might be a lot more helpful. \$\endgroup\$ – Amiram Korach Jul 9 '12 at 3:28
  • 2
    \$\begingroup\$ 320ms? Is this called in a loop a million times? That's hardly poor performance. I've seen some EF code take multiple seconds at times on some pretty heavy metal servers. I'd fire up SQL Profiler and see what queries are coming across and analyze their performance against your schema. \$\endgroup\$ – Jesse C. Slicer Jul 9 '12 at 4:12
  • \$\begingroup\$ I notice you are not using the awePageData variable anywhere? How does it factor in here? Are we missing something? \$\endgroup\$ – Martijn Sep 18 '12 at 12:45
  • \$\begingroup\$ Yes I use it later on in my code. I only included the queries since they are all I really care about. Also, it's important to note that the whole thing can be done in one query using joins and such, but what I found was when I split it into these three quries, I achieved the best results. How it works is Users, Pages and menu items each have roles. A user's roles must match a page's and a menu item's (independant of each other) to be able to access them. A page has placeholders and content. As such, two users may have access to the same page, but one has limited access to menu items. \$\endgroup\$ – saml Sep 18 '12 at 14:29
  • \$\begingroup\$ Are you pulling this from database Views or Tables? If views, EF will run those Include values as OUTER JOIN rather than INNER JOIN. That has been the source of some slowness for me. \$\endgroup\$ – Brannon Jan 5 '13 at 5:11
2
\$\begingroup\$

First things first. Since we are talking about an SQL backend (unless I'm mistaken) - you need to profile SQL and take each (sub)query seperately, then determine which query takes the longest. It would simply be pure guess work, taking answers out of thin air otherwise, since we have no reference for what the bottleneck actually is.

Next, what are you SQL indexes? You have many joins and where clauses. If you don't have your indexes set up correctly, then - there's your problem!

Finally compare your c# query to the actual SQL query. Sometimes your LINQ can look beautiful, but the actual code generated for the query is utter BS. I deal with LINQ2SQL myself and have many times been forced to make a list (ToList()) from the sub-queries, forcing the data into the client, into the realm of c# and doing the final query on top of those lists instead. I may use more memory for a brief period of time, but I gain orders of magnitude more speed...

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.