Poor performance with projections in Orchard 1.7.1

Topics: Administration, Core, Troubleshooting
Nov 21, 2013 at 8:19 PM
Hi there,

Trying to speed up a slow instance of Orchard.

Our product page uses a projection to render the data for the page and we are running into the NHibernate N + 1 SELECT query problem. This problem if you are not aware of it is due to lazy loading of data and every time a new piece of information is encountered by the application NHibernate will perform a separate query to extract that one piece of information.

This is not what we want at all. I've searched around and the general fix when usng a ContentManager is to use a QueryHint to tell it to perform a join on the data you also want to retrieve.

However, in our case we are using a projection to build this page. I haven't seen any examples of how you would integrate a query hint into a projection in Orchard 1.7.1. Does anyone know of a way to do this or any other general hints on how I can make this Projection page connect to the database less often. I'm looking to reduce the number of database queries from over 300+ to hopefully 10 or less!


Nov 21, 2013 at 9:03 PM
In 1.7.2 there is a new filter for projections which does exactly that. It's Eager Fetch filter. Add it to your query and select the records you want to be pre loaded.
If you don't want to migrate to 1.7.2 you can take this filter back to 1.7.1
Nov 21, 2013 at 10:20 PM
Hey Sebastien,

Thanks for the tip. I downloaded Orchard 1.7.2 but I can't find an example of where EagerFectchFilter is used at (it is mispelled by the way maybe a fix for 1.8)

I tried adding it to the Filters property of a FilterGroupRecord associated with a projection however that gives a compile error as a FilterGroupRecord expects objects of type FilterRecord only.

Can you provide a sample of how the Eager Fetch Filter is supposed to work please.
Nov 21, 2013 at 10:32 PM
Sebastien, you may want to announce that 1.7.2 is released in orchard-discuss mailing list, if it's not too late. I read in the meeting notes of 12th that it will be released that day or the day after, but totally forgot about it. I just realized it's there with this post.

By the way, thanks for the release everyone.

Nov 21, 2013 at 11:40 PM

@kassobasi sure
Nov 22, 2013 at 4:05 PM
Hey Sebastien,

I've seen where EagerFectcFilter is defined at however I am asking for an example of how to use it.

I did a find all references and I cannot find a single example of where this is used. I tried searching the entire solution for the text "EagerFectchFilter" and I cannot find this either.

If you have a code example on how to use this I would greatly appreciate it.

I've tried using it in combination with a FilterGroupRecord trying to add it to the Filters property of this object type but hat gives a compile error as a FilterGroupRecord expects objects of type FilterRecord only.
Nov 22, 2013 at 4:46 PM
You are talking a projection, it's not in code that you use it, but in the UI
Nov 22, 2013 at 4:54 PM
Hey Sebastien we are building the projection dynamically with C# code though.

ProjectionPart projection = _contentManager.Query<ProjectionPart>().List().FirstOrDefault(a => a.TypeDefinition.Name == "ProjectionPage"
            && a.As<TitlePart>().Record.Title == "All Products");

        if (projection == null)
            projection = _contentManager.Create<ProjectionPart>("ProjectionPage", t =>
                t.As<TitlePart>().Record.Title = "All Products";
                t.As<ProjectionPart>().Record.LayoutRecord = layout;
                t.As<AutoroutePart>().Record.DisplayAlias = "Products/Category/All/";
                t.Record.QueryPartRecord = productQuery.Record;

            //Get Route to link alias to  (ex: Contents/Item/Display/28 )
            var displayRouteValues = _contentManager.GetItemMetadata(projection).DisplayRouteValues;

            //Bind the alias
            _aliasService.Replace(projection.As<AutoroutePart>().Record.DisplayAlias, displayRouteValues, "Autoroute:View");

            //Publish the Projection page
Nov 22, 2013 at 4:57 PM
what is the purpose of creating a projection by code ?
Nov 22, 2013 at 6:18 PM
I'm not certain Sebastien as I'm not the one who created it. It might make more sense to do this in a receipe.

Is there a way to use the EagerFetchFilter inside of C# code?

I found the UI setting for EagerFetchFilter so I am going to play around with it and see if it helps performance.
Nov 22, 2013 at 6:26 PM
If it's in a migration I assume it could be to have it automatically created.
But looking at the code this is not the case, I am really scared by what I am seeing here, I have the impression that this is used to actually execute a query on the fly. Then don't expect the performance to be near to acceptable.
Nov 22, 2013 at 6:30 PM
The code I pasted above to create the projection gets called once the module is enabled if the projection doesn't already exist (why it isn't in migration.cs I am not certain).

Anyway is there a way to add the eager fetch filter in the above code.