Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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;
share|improve this question
1  
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. – Amiram Korach Jul 9 '12 at 3:28
1  
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. – Jesse C. Slicer Jul 9 '12 at 4:12
I notice you are not using the awePageData variable anywhere? How does it factor in here? Are we missing something? – Martijn Sep 18 '12 at 12:45
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. – saml Sep 18 '12 at 14:29
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. – Brannon Jan 5 at 5:11
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.