Q about querying content items/parts

Topics: Core, General, Writing modules
Apr 5, 2012 at 7:07 PM

I have some content items (lets call them MyPart, MyPartType) that contain slide shows. On my home page I'm display a list of MyPart's, with links to the pages. I'm querying the list of MyPart's using IContentManager.GetMany<MyPart>(), passing in a list of integer id's. 


MyPartType contains: { MyPart, CommonPart, AutoroutePart, TitlePart, SlideShowPart }


I profiled the db while loading this page and I'm seeing a lot of queries. There was one thing that seemed inconsistent. It's generating one query for every single SlideShowPart, even though I'm not accessing the slideshows anywhere on the home page.

But the weird thing is that when I add use of @Url.ItemDisplayUrl(MyPart) to the template for MyPart, it generates 3 additional SQL queries for every single MyPart instance. In this case it means 300 extra SQL queries just to generate the URL's. Those extra queries go away if I take Url.ItemDisplayUrl() call out of the template. 


1. why does ItemDisplayUrl() generate additional sql calls (looks like it's to load title part, autoroutepart, to get the URLs), but then the queries for the SlideShowParts *always* run? 
2. Does orchard always issue one query per part, when loading a content item? Or does it sometimes do joins to load all the parts for a content item via a single SQL query?
3. Are there any ways to avoid this? Are there hints or parameters or something I can specify to hint Orchard in what to load and what not to load? For example, on my home page I don't need the slide shows at all, so it is useless and costly for me to have to load all of them through multiple queries. But I do need the slide shows in the detail pages for each MyPart, so I want them loaded there. 


I realize that with caching the drawback of all the queries would be eliminated, but i'm not sure yet I'll be able to use the caching module. As per the other "Orchard 1.4 Performance" thread I'm not sure if I can use caching on pages with dynamic stuff, and when users are not logged in it will also be an issue. The fallback plan would be to implement custom caching, which will be nice to avoid if possible. 

Apr 5, 2012 at 7:11 PM

Note, adding that list of MyParts to home page makes the load time go from ~200ms to 1.2s. On 1.4 , no caching. 

Apr 6, 2012 at 8:08 AM

The issue looks similar with the one I have with taxonomy (causes an additional query per user when listing) :/

Some feedback from a core dev would be nice (or anyone who knows what is going on)

Coordinator
Apr 7, 2012 at 6:23 AM

You should use QueryHint. That's it I think.

Apr 8, 2012 at 7:56 PM
Edited Apr 8, 2012 at 8:04 PM

Thank you. I assumed QueryHint was an enum for some reason, after seeing the intellisense when making the GetMany<>() call. So I passed in QueryHints.Empty. Using QueryHints properly reduced the # of queries for the page from ~500 to ~200.

This is a separate issue: I'll have to update the way the relationships between SlideGroups and Slides are set up in ContentItemSlider to fix the # of queries that are being run there. SlideGroup driver is querying for the individual slides so I can't yet fully optimize the # of queries through QueryHints. I didn't know how to set up 1-N or N-N when I created that module (forked from FeaturedItemSlider actually). 

Here's what I did in case anyone else wants to see an example: 

 

  public IList<MyPart> GetByCategoryName(string categoryName) {
    var session = _sessionLocator.For(typeof(MyPart));
    string sql = @"
      select * 
      from ... 
      where 
      p.name = :CategoryName
    ";
    var query = session.CreateSQLQuery(sql).SetString("CategoryName", categoryName);
    query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(IntDto)));
    var results = query.List<IntDto>();
    var hints = QueryHints.Empty;
    hints = new QueryHints().ExpandRecords<CommonPartRecord, TitlePartRecord, IdentityPartRecord, AutoroutePartRecord>();
    var careerParts = _contentManager.GetMany<MyPart>(
        results.Select(t => t.Id), VersionOptions.Published, hints
    );

    return careerParts.ToList(); 
  }

  internal class IntDto { public virtual int Id { get; set; } }
Apr 8, 2012 at 7:59 PM

Still think it would be cool if GetMany could query out all the parts in a single query. I've done similar stuff through hand written SQL layers, passing in a list of int Id's and querying out all the associated biz objects in a single query using the IN (...) operator. 

Is this possible at all? 

Apr 9, 2012 at 2:28 PM

I've run into something similar trying to query with ContentManager and I'm not exactly sure why or when it decides to start pulling other related parts but it does result in a lot of extra queries. I've had to fall back to using repositories on any queries that pull back larger datasets because of this. I originally thought it was something wrong with the taxonomy module because I noticed a lot of overhead when querying larger data sets that simply had a taxonomy field on the part. However, I'm beginning to think that ContentManager will automatically pull these parts together by design and it just happens to result in a lot of queries in order to bring all the related parts together for each content item.

Apr 9, 2012 at 2:45 PM

Yes. You can reduce it to a single query per content item using the method outlined above. 

Apr 9, 2012 at 5:40 PM

TheMonarch, any reason you went this direction vs. using an IRepository? I'm guessing the nature of the data you are dealing with requires you to go this route, but I'm just trying to wrap my head around when this method is a good choice.

Apr 9, 2012 at 7:26 PM

Because the items I'm displaying in the list are content parts that each have their own page, and I need to link to those pages using the AutoroutePart associated with each item. I'm using ItemDisplayLink to get the URL for each part so IRepository doesn't work well for that. 

For another collection I'm displaying the reason was similar, but with the addition that I have a special template for those parts  with DisplayType = "HomePageSummary" that is handled by the part driver. I took advantage of Orchard's  content rendering system to choose the correct .cshtml template to use to render the items in that HomePageSummary context, so IRepository wouldn't have been convenient. The only time IRepository has been better for me so far has been working with static data for things like droplists, mainly in the Dashboard, but also in a few places in the front end. 

Apr 9, 2012 at 7:29 PM

There's probably a way I could have used Projector too, but my homepage itself is driven by different parameters. I'm not sure if Projections can accept parameters (I don't remember seeing anything like this in my brief look, but could be wrong). 

Developer
Apr 9, 2012 at 8:23 PM

I don't think Projector handles any parameters out of the box except paging, but you could write custom providers of course.

Coordinator
Apr 12, 2012 at 12:59 AM

There are tokens for querystring parameters that you can use with projector.

Developer
Apr 14, 2012 at 6:56 PM

My bad, how could I missed that...