Performance killer - 30 queries per request?

Topics: Customizing Orchard, General, Installing Orchard, Troubleshooting, Writing modules, Writing themes
Apr 15, 2011 at 3:49 PM
Edited Apr 19, 2011 at 5:05 PM

Seeing huge amounts of SQL queries per page.

  • Is this expected?

While using SQL Profiler I noticed that each page request generates a large number of SQL queries - about 30/page in this case.
I tried clicking the home page a number of times and it seems that the same number of queries is generated with each request.
Below is just the top 3 of the queries (I can provide all 30 if it helps).  FYI - This is a brand new install of 1.1 - just the standard home page.

I thought I read about Orchard adding caching a while back - so the question is why isn't being cached?  Maybe I haven't turned on the caching?

1 exec sp_executesql N'SELECT contentite0_.Id as Id512_0_, contentite0_.Data as Data512_0_, contentite0_.ContentType_id as ContentT3_512_0_
FROM Orchard_Framework_ContentItemRecord contentite0_ WHERE contentite0_.Id=@p0',N'@p0 int',@p0=1
2 exec sp_executesql N'SELECT versions0_.ContentItemRecord_Id as ContentI6_1_, versions0_.Id as Id1_, versions0_.Id as Id513_0_, versions0_.Number
as Number513_0_, versions0_.Published as Published513_0_, versions0_.Latest as Latest513_0_, versions0_.Data as Data513_0_, versions0_.ContentItemRecord_id
as ContentI6_513_0_ FROM Orchard_Framework_ContentItemVersionRecord versions0_ WHERE versions0_.ContentItemRecord_Id=@p0',N'@p0 int',@p0=1
3 exec sp_executesql N'SELECT contenttyp0_.Id as Id514_0_, contenttyp0_.Name as Name514_0_ FROM Orchard_Framework_ContentTypeRecord contenttyp0_
WHERE contenttyp0_.Id=@p0',N'@p0 int',@p0=1
Apr 19, 2011 at 5:04 PM

FYI - the same queries are executed each time (for the same page). 

To test this, I clicked on the same page 3 times - each time collecting the queries that were generated.  Then I compared the queries for all 3 and they were exactly the same...

Coordinator
Apr 19, 2011 at 5:49 PM

We are currently investigating on it, though it does not mean there is any bug. The nature of Orchard is that contents is aggregated across parts, which explains the number of queries. To answer the caching remark, there is no Content Caching right now, just infrastructure one, like list of modules, themes, services, ... the stuff that can't be done as a module. Content caching can be done as a module, that's why we did not implement it out of the box, and different techniques could be use (distributes, sliding, expiration, invalidation, ...). This would be an awesome module if you want to do it by yourself ;)

 

Apr 19, 2011 at 8:04 PM

Thanks for the reply Sebastien. 

While it's good to hear that it's being investigated, I must say I'm quite disappointed.  Remark?  A CMS without caching will scale poorly.

So, here's a little joke we like to tell:  What is a CMS without scaling and fast response?  Answer: It's a web site people don't visit very much.
...I would have thought that performance and scaling would have been top design goals.  Especially with all of the attention the need for fast web sites has been given in the press.  I'm really disappointed to hear it wasn't on the list.  What a bummer.

A couple things:

  • I don't think the queries are just for content.  E.g. I just added a page with nothing but a title.  There's literally 17 words on the entire page - yet there are still 30 SQL queries.
  • Granted, a number of those queries could probably be joined?!?
Coordinator
Apr 19, 2011 at 8:21 PM

Nobody said it wasn't on the list. What happened is that we focused first on those performance issues that were the biggest bottlenecks, based on the profiling data that we gathered (the common name for designed for performance and scaling is premature optimization). The main design goal for Orchard has been flexibility and extensibility.

On this specific problem, there are several things that can be done and that we are investigating. An obvious one is output caching. As Sébastien said, we didn't do that yet because it's very easily done as a module (we made sure of adding the required extensibility points for that to work). Instead, we focused on problems and features in the core that couldn't be added as modules. Makes sense?

Another thing is improving the querying itself, and you are right that some of these queries can be joined. Of course this is easier said than done because of the distributed nature of a CMS such as Orchard (parts can be added to a content item at any time) but it's definitely not impossible and something that we are exploring.

And of course, as always, suggestions and patches are more than welcome.

Apr 19, 2011 at 11:34 PM
Edited Apr 20, 2011 at 2:55 PM

>> based on the profiling data that we gathered (the common name for designed for performance and scaling is premature optimization). 
OK ;-) fair enough - although, I think that's a little different than planning for a caching infrastructure.

>> The main design goal for Orchard has been flexibility and extensibility.
That's actually what I meant when I said it's not on the list.  I also went back and looked at the Mission and realized it wasn't there either.  So some of my frustration is with myself for not noticing ealier - sorry, if it sounded like it wasn't on any list anywhere.  On the other hand, it's not on either of these and they are your main goals...

>>...output caching
OK, and it may be a great solution - especially if the "each time some content is published" option somehow knows everytime a change is made.  Is this the case (where in code would I look)?
Another suggestion is to look at it in terms of Orchard being a container into which users put content (and developers put modules/widgets/themes).  The container both uses, and provides - a caching infrastructure which module developers can also take advantage of.

>> we made sure of adding the required extensibility points for that to work (output caching)

  • Where would I look to find the extensibility points?
Coordinator
Apr 20, 2011 at 12:07 AM

One way to implement output caching is to act at the shape level: you could implement IShapeTableProvider (look for examples of that in the code) and handle OnDisplaying and OnDisplayed from there. From OnDisplaying, you can look-up the cache for that shape and write the ChildContent property on the context with the string that came out of the cache if there was a hit. This will short-circuit the rest of the rendering for that shape. From OnDisplayed, you would read the child content property off the context and store that into the cache.

You can apply this on the smallest of shapes, you can apply it on widgets or you can apply it on the outermost shape on the page, which is the Layout.

To do the actual storing, we have a CacheManager API that is using ASP.NET's cache but with a cleaner API that is also aware of multi-tenancy.

And of course, there is always the more traditional approach of using the server's kernel output caching facilities for the whole URL/resource. The way you would leverage that would be by implementing an action filter, probably.

Apr 20, 2011 at 12:53 PM

It was suggested in another discussion thread that the current Warmup feature already provides page-level cacheing which is unfortunately only enabled while the site is loading.

A very simple to implement cache would be to just allow the Warmup module to serve the cached pages all the time.

Apr 20, 2011 at 5:24 PM

Yes, I like that suggestion a lot ;-)

I wonder if security might be a pitfall - it might get interesting is if the user should not have access to something on that page.  But, given that Orchard has no way to restrict access to content, maybe it's not that big a deal?  Hmm, I wonder what happens in the current warmup feature if you include say one of the dashboard pages or the create blog page...

Apr 20, 2011 at 7:58 PM

You're right, there's an issue there; didn't think it through. Just to display the login details and Dashboard link once someone's logged in means page-level cacheing is only good for unauthenticated users. So shape-level cache as Bertrand suggests could be the way, but even that gets really complicated when there are all kinds of variations that could happen depending on the current user's roles.

Apr 25, 2011 at 1:02 PM

Sure there's an issue with authenticated users but on an internet facing site, most users will be anonymous. It makes a big difference in terms of scalibility.

Most Wordpress caching plug-ins will only return cached page for anonymous users, while serving "normal" page for authenticated users or users that have left some comment.

+1000 for a generalization of the warm-up module.

May 8, 2011 at 2:34 PM
Edited May 8, 2011 at 2:36 PM

Caching pages for anon users will definitely improve scalability. However, if you just want to speed up the administration page load times, caching can still be done, the caches just need to be invalidated when changes to the data are made.   In the past I've had a lot of luck with using AOP to declaratively cache and invalidate expensive method calls.  

Take a look at this example from http://cache.codeplex.com/

Cache.Cacheable("UniqueKeyForThisMethod")] //cache using this key plus parameter(s)
public SomeObject GetObjectById(Int32 Id)
{
...
}

[Cache.TriggerInvalidation("UniqueKeyForThisMethod")] //delete from cache using this key and passed parameter(s)
public void RemoveObjectById(Int32 Id)
{
..
}

This is using postsharp for method interception, which should work with orchard without a problem.  Autofac might have hooks to pull off method interception as well, I'm not sure though.