This project is read-only.

Taxonomies and excessive queries

Topics: Core, General
Apr 15, 2013 at 4:33 AM
I was doing some sql profiling, and have found some stuff that seems strange and inefficient.
  1. On my frontend, I have it showing a page of 50 content items belonging to a taxonomy term. It results in 125 queries to the database. For each content items, it is querying the bodypart once, then it's doing 20 each of these queries:
exec sp_executesql N'SELECT autoroutep0_.Id as Id84_0_, autoroutep0_.UseCustomPattern as UseCusto2_84_0_, autoroutep0_.CustomPattern as CustomPa3_84_0_, autoroutep0_.DisplayAlias as DisplayA4_84_0_, autoroutep0_.ContentItemRecord_id as ContentI5_84_0_ FROM Orchard_Autoroute_AutoroutePartRecord autoroutep0_ WHERE autoroutep0_.Id=@p0',N'@p0 int',@p0=193
exec sp_executesql N'SELECT titlepartr0_.Id as Id85_0_, titlepartr0_.Title as Title85_0_, titlepartr0_.ContentItemRecord_id as ContentI3_85_0_ FROM Title_TitlePartRecord titlepartr0_ WHERE titlepartr0_.Id=@p0',N'@p0 int',@p0=193
Is there some way to optimize this? Do I have something misconfigured possibly? It actually isn't too awful slow, but I wouldn't call it snappy, and this is on a powerful development server doing nothing else. So I'm worried it will be a dog on a production server.
  1. Here's another eye opener. In the dashboard, if I click taxonomies, it does a query for every single content item from what it looks like. For me that is over 10,000 queries. Why!?
This takes several seconds, again on my dedicated server with a quad cpu and 16GB RAM.

Apr 15, 2013 at 10:25 AM
Oh they know.

This is the sole reason why we aren't using Taxonomy : We have 60k+ content items, it would just blow up when it queries all content items...

Same goes for import/export (read: don't try it if you actually have a decent amount of content..)
Apr 15, 2013 at 3:01 PM
Thanks AimOrchard. Wanted to make sure it wasn't just me.

Is there another way to categorize this content then?

Funny, I remember how, before ORMs, I would optimize my sql so that I only did one database hit per page request (if not cached). Now I'm trying to tell myself that 100+ queries per page request is ok, but I'm not convinced.
Apr 15, 2013 at 3:28 PM
Yeah we had the same 'issues' with the amount of queries. You really do not want to know the amount of queries our website executes on first request ;)

And that is after optimizing it already... Guess this is the 'bad' thing about a 'flexible' CMS.
Apr 15, 2013 at 5:43 PM
Please open two different bugs for this, I will open them for the next release, now that Taxonomies is to be included as a core module.
Apr 15, 2013 at 11:09 PM
I get stopped recently by same kind of problem, not using taxonomies but projection with items composed of many parts, using several languages on the site, at that day I had no time to drive an analysis on my log....because the cache start working (for some unknown reason it was not ok from the start) and it solves all the response time problems.
But for sure in some situations, if we don't optimize, Orchard generates a huge amount of requests, and I must say that I am not in position to optimise Orchard at the query level because I find the way we could query unclear, no documentation.
Cache systems have always been solution for massive DB access, especially on a home page where there is only read-only access ... but could we manage a readonly access in Orchard ? And transaction isolation level ?
Apr 16, 2013 at 4:52 PM