none
Trouble optimizing this query RRS feed

  • Question

  • I am using RIA services and EF 4.0 as my databse layer , hosted by an MVC web app

    I have upwards of 2000 members on my website and the issue I have is allowing large seraches of user details since I use a very normalized SQL database back end.  I use view models to agregate my data and have been forced to store current search data in session since I do not have a specialized state server. 

    Anyways long story short , my most expensive task is generating the user detail pages since they costist of browsable lists of similar user accounts.  I came up with this ugly query agregate all the data but it runs quite slowly even with a maximum of 200 profiles allowable for searches.  god forbid when 100s of members are running the same search.

     *** the operations causing the slowdown are near the bottom i.e the further sub queries in the model being populated ****

    I have a the following linq query,

     //TO DO
    
      //Get these initalized
    
      myService datingservicecontext = new myService().Initialize();
    
      PostalDataService postaldataservicecontext = new PostalDataService().Initialize();
    
      myFTSEntities db = new myFTSEntities();
    
      PostalData2Entities postaldb = new PostalData2Entities();
    
    
    
    
    
     
    
      
    
      //lmited to 200 results for now til we speed up this query 
    
      public IEnumerable<ProfileBrowseModel> GetQuickProfileWithUsername(List<MemberSearchViewModel> searchModel, profile UserProfile)
    
      {
    
       return (from x in searchModel
    
         join f in db.ProfileDatas
    
         on x.ProfileID equals f.ProfileID
    
         select new ProfileBrowseModel
    
         {
    
          SearchProfileData = f,
    
          quickSearch = x,
    
          MyCatchyIntroLineQuickSearch = Extensions.Chop(f.AboutMe.ToString(), 10),
    
          Myprofile = UserProfile,
    
          SearchCriteria = new ProfileCriteriaModel(f),
    
          MyCriteria = new ProfileCriteriaModel(UserProfile.ProfileData),
    
          PeekedAtThisMember = db.ProfileViews.Any(r=>r.ProfileID == f.ProfileID && r.ProfileViewerID == UserProfile.ProfileID),
    
          IntrestSentToThisMember = db.Interests.Any(r => r.ProfileID == UserProfile.ProfileID && r.InterestID == f.ProfileID),
    
          BlockedThisMember = db.Mailboxblocks.Any(r => r.ProfileID == UserProfile.ProfileID && r.BlockID == f.ProfileID),
    
          //to do add that members block status to u i guess to depending on speed of these queries
    
          LikedThisMember = db.Likes.Any(r => r.ProfileID == UserProfile.ProfileID && r.LikeID == f.ProfileID)
    
         }).Take(200).ToList();
    
      }
    
    

    I have read that maybe pre-compiling my query would work but it seems to me a large join might work better than what i am doing however I don't know how to convery join results into boolean values as my model expects.

    I want to be able to at least have 500- 1000 records returned in this query , I database is a SQL 2008 R2 box with 10gigs of ram

    any ideas ?


    Tuesday, June 14, 2011 9:26 PM

Answers

  • Hi ola,

    Welcome!

    To tell truth, I'm not familar with RIA service, we can imporve the performance of EF in this link: http://www.ef-faq.org/performance.html

    there are many factors result in the performance issue, so we should find where cut our performance, we can watch sql profiler to know how long the query ran. by the way, the internet also should take into account.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 15, 2011 1:08 PM
    Moderator

All replies

  • Hi ola,

    Welcome!

    To tell truth, I'm not familar with RIA service, we can imporve the performance of EF in this link: http://www.ef-faq.org/performance.html

    there are many factors result in the performance issue, so we should find where cut our performance, we can watch sql profiler to know how long the query ran. by the way, the internet also should take into account.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 15, 2011 1:08 PM
    Moderator
  • Hi ola,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 20, 2011 3:55 AM
    Moderator