This project is read-only.

Number of SQL statements issued by the Content Manager query mechanism

Topics: Customizing Orchard, Writing modules
Jul 6, 2011 at 1:56 PM

Please could someone confirm or refute the following:

when querying content items via ContentManager, the number of SQL statements issued against DB will depend on the number of items of requested type stored within DB and number of content parts the type consists of.

If so, how can I reduce the number of DB queries, particularly how can I get all needed content items with single query (I worry about DB load when large number of users will attend the site)?

If needed, sample code can be published that gives me N * M SQL statements, where N = number of content items, M = number of content part the type consists of.


Jul 7, 2011 at 12:32 AM
Edited Jul 7, 2011 at 12:38 AM

You can take a look here where I discussed utilizing nhibernate's second level cache to reduce the number of queries (by about 50-75%).  It looks like this won't be implemented in any way until 2.0 at the earliest, so you'd have to do a custom build of orchard that uses the patch I submitted.

*Edit* That said, I think it is almost impossible to hope that orchard will get to the point where it could get the number down to a single query.  Having a system that is as modular and dynamic as orchard comes with the tradeoff that the individual components are completely ignorant of each other, and it would be very difficult (impossible?) for orchard to be prescient enough to consolidate all items into a single query.  The best case I could envision would be a 2 (or more) pass system where orchard attempted to see what was going to be queried, but even then you would have situations where subsequent queries would be required depending on the query results.

Jul 7, 2011 at 8:04 AM
Edited Jul 7, 2011 at 8:04 AM

Since it sounds like the team isn't planning on integrating this in 1.x, I packaged this up into a module for people to try out.  I'm not putting it into the gallery because there are a couple big caveats: 1) It hasn't been fully tested, and 2) I can almost guarantee that it will blow things up when anyone tries to upgrade to v2.  It should be easy to remove, it doesn't create anything persistent, it just overrides a couple of the core NHibernate config services.  Let me know if anyone tries it out. 

Jul 7, 2011 at 9:34 AM

Thank you for reminding this approach, I think this should be used when all other possible ways are used.
In the neighbors post it was pointed out that: if you exactly know part/records the type consists of, you can use '.Join<SomeOrchardRecord>' call in order to use 'inner join' statements instead of subsequent queries. This may eliminate the N+1 issue. To my mind this situation is similar to the one described here: The difference is that Orchard developers should use some abstractions instead of tuning NHIbernate queries directly.

Dec 10, 2011 at 12:57 PM
borovikpe wrote: If needed, sample code can be published that gives me N * M SQL statements, where N = number of content items, M = number of content part the type consists of.

Hello, I'm just starting out with Orchard and read the above and got a bit of a shock.

Is it correct then that if there are, say, 1000 User items, and I've added another two parts to the User content type, then a listing of all users will result in 3000 queries?

I must be missing something. Perhaps it is that you don't normally query via the Content Manager? But that can't be write. Would someone help put me write please.


Dec 10, 2011 at 3:46 PM
turquoiseowl wrote:

Is it correct then that if there are, say, 1000 User items, and I've added another two parts to the User content type, then a listing of all users will result in 3000 queries?

The question is, under what circumstances would you want to list 1,000 users without any filtering?

When you query through ContentManager you can perform joins to content parts which allow you to filter at the DB level, and will also reduce the number of queries - if the content manager knows about a joined part record, then it will optimise the query and fetch it straight away.

So yes, it'd be possible for 1,000 user items to then cause another 1,000 queries if you enumerated the list for a specific part. If you don't tell the system to prefetch joined records, how can it know about them? The content manager never knows which part records might eventually be glued on, but it's possible to optimise it if you were doing something as crazy as reading 1,000 users at once. But this is a website, why would you ever do that?

I'm not saying there aren't a number of things that could be done to reduce the overall amount of queries; but really, if you were listing 1,000 users, you'd need to customise the query to make sure all required parts were joined.

Dec 10, 2011 at 5:31 PM
Edited Dec 10, 2011 at 5:34 PM

Thanks for the reply randompete.

Well I don't think it's problem for me to join all the parts into the query. So, if I do so, it will only be a single DB query. Is that right?

Many thanks.


BTW, Is Sql Server Profiler the only way to look at the queries produced, or can the application log them? Cheers.

Dec 10, 2011 at 6:57 PM

That's right, it'll optimise the fetch for any part records you've explicitly joined. Of course, if you then access any other parts that weren't included, they'll be lazy loaded one at a time. This is a problem if you run the item through ContentManager.BuildDisplay: all the drivers will run, so any part with a driver will get loaded - even if the shape isn't displayed in the end (this is actually something I'm actively looking for a way to optimise, I'm generating a lot of content displays in some of my code, and it starts to get noticeable once you have a few dozen of them).

I just found a great module for inspecting SQL queries:

It lists all the queries and shows you duplicates. If you look under "forks" I've been working on some fixes and improvements.

Dec 11, 2011 at 12:37 AM
Edited Dec 11, 2011 at 5:15 PM

Thanks for getting back to me again randompete.

What I'm trying to do create a Content Type called Organization which has a property called MainContact that references a User content item. I've sort of based it on the last sample in the Creating-1-n-and-n-n-relations doc.

In the View for the Organization content type, I have a drop-down list which allows the MainContact to be selected and gets populated with an enumeration of all User items.

As in the sample, I am populating the enumeration in a service class method as follows:


        public IEnumerable<ViewModel_IdName> GetUsers() 
            // Queries the content manager for all "User" content items, building
            // a collection of ViewModel_IdName objects, one for each user.
            return _contentManager
                .Select(ci => new ViewModel_IdName {
                    Id = ci.Id,
                    Name = GetUserName(ci)


Looking in the sql profiler, that content manager query is resulting in 1 main query like this:


exec sp_executesql N'
    this_.Id as Id50_2_, 
    this_.Number as Number50_2_, 
    this_.Published as Published50_2_, 
    this_.Latest as Latest50_2_, 
    this_.Data as Data50_2_, 
    this_.ContentItemRecord_id as ContentI6_50_2_, 
    contentite1_.Id as Id51_0_, 
    contentite1_.Data as Data51_0_, 
    contentite1_.ContentType_id as ContentT3_51_0_,
     contenttyp2_.Id as Id54_1_,
     contenttyp2_.Name as Name54_1_ 
    lynx_Orchard_Framework_ContentItemVersionRecord this_ 
    inner join lynx_Orchard_Framework_ContentItemRecord contentite1_ 
    on this_.ContentItemRecord_id=contentite1_.Id 
    inner join lynx_Orchard_Framework_ContentTypeRecord contenttyp2_ 
    on contentite1_.ContentType_id=contenttyp2_.Id 
        contenttyp2_.Name in (@p0) 
    and this_.Published = @p1
N'@p0 nvarchar(4),@p1 bit',


then N others like this, where N is one less than the number of User items in the db:


exec sp_executesql N'
    this_.Id as Id38_0_
    , this_.UserId as UserId38_0_
    , this_.Role_id as Role3_38_0_ 
    lynx_Orchard_Roles_UserRolesPartRecord this_ 
WHERE this_.UserId = @p0'
,N'@p0 int'



Noticing that Role is included in the select list, I tried introducing Join<Orchard.Roles.Models.UserRolesPartRecord>() to the query, but without joy because UserRolesPartRecord isn't derived from ContentPartRecord.

Would you advise on whether and how I might get this down to a single query please? Many thanks.


Just a quick update. I've changed the CM query as follows in an attempt to avoid these extra DB queries, but still with one DB query is being run per User content item:

            return _contentManager
                .Select(ci => new ViewModel_IdName {
                    Id = ci.Id,
                    Name = "Hello"

Dec 11, 2011 at 9:27 PM
Edited Dec 11, 2011 at 9:28 PM

Interesting ... UserRolesPartRecord is confusingly named, you're right it's not an actual ContentPartRecord. The UserRolesPart is just a ContentPart, not a ContentPart<T>. I assume it was all created before the whole {X}PartRecord convention arose.

The reason why it's happening is in the UserRolesPartHandler:

    public class UserRolesPartHandler : ContentHandler {
        private readonly IRepository<UserRolesPartRecord> _userRolesRepository;

        public UserRolesPartHandler(IRepository<UserRolesPartRecord> userRolesRepository) {
            _userRolesRepository = userRolesRepository;

            Filters.Add(new ActivatingFilter<UserRolesPart>("User"));
            OnLoaded<UserRolesPart>((context, userRoles) => {
                                    userRoles.Roles = _userRolesRepository
                                        .Fetch(x => x.UserId == context.ContentItem.Id)
                                        .Select(x => x.Role.Name).ToList();

So; when the part loads, the roles are fetched immediately from the repository for UserRolesPartRecord, and it's implemented as a separate query rather than joining to anything in the User item, so there's no way to prefetch in this case.

I'm tempted to raise a workitem over this, and there's a very easy improvement. UserRolesPart.Roles could be simply refactored to use a LazyField, so at least the roles will only be loaded if needed (and usually, you only ever need the roles for the current user). It's not a huge saving in most cases, but it's an easy option.