Search

Before I begin: my findings are based on personal experience only, I have not done any extensive benchmarking, nor have I tested these techniques on different setups.

So what have I done this time?

This time I tried to turture my webserver as much as possible, by outputting lots of entries (around 300, heavily filtered) at the same time. Even though these entries are very simple (just a title and an upload field edit: and an SBL), Symphony had a very hard time keeping up:

 albums_album_foto   0.6990 s from 226 queries

Now, 226 queries by itself isn't really a problem, but when it takes 0.7 seconds to execute them, it is!

So, I edited the datasource to pull in all the data in a single query (with the help of a few JOINS), and here is the result:

albums_album_foto   0.0320 s from 1 query

Wow. Yes, that's a huge difference!

Yeah, sure, but..

Ok, I have to admit, these results aren't really, really fair, because they both use the SQL cache heavily. And, ofcourse, when you only need to run a single query this is lightning fast. When the SQL cache is not used (because it is not fresh, for example) the results are closer together:

 albums_album_foto  0.2461 s from 1 query
 albums_album_foto  0.7713 s from 226 queries

Still, this is a very interesting: the standard version seems to gain a bit from the caching, but not too much, while the single query version gains heavily (a factor 10!).

I know there has been a lot of improvement on this already in the past year(s), but still, manually rewriting slow datasources can make a big difference, especially on a busy website.

What version of Symphony are you trying this with? Getting 300 entries from a section that has two fields should never yield more than a handful of queries these days, so something is obviously wrong here.

Would you be able to send me a demo build so I can investigate?

I am doing this from 2.2.5, and the majority of the queries are from the SBL field, which executes a query for every single entry.

If you really think there is something wrong with my setup I am willing to setup an account for you, but I would rather discuss that in private because of the nature of the project.

the majority of the queries are from the SBL field, which executes a query for every single entry

Aha, yes, this is a problem with this field. I was going to say — if it was just two text input fields, then there wouldn't be any more than about 10-15 queries for the data source.

I'll put together a demo 2.3 build with text input and SBL fields to see if there's anything that can be done. Do you have the "show count of entries in related sections" checkbox ticked in the datasource? That will perform a SQL count() lookup for every entry returned, which could be the bottleneck in this instance.

I know there has been a lot of improvement on this already in the past year(s), but still, manually rewriting slow datasources can make a big difference, especially on a busy website.

Definitely! Symphony is an abstraction, which will always have a performance hit. If you need it to be the fastest it can be, raw SQL is the way to go.

Do you have the "show count of entries in related sections" checkbox ticked in the datasource?

Nope, I had it before, but I worked my way around it because it was even slower.

I have now replaced all the slow queries with a single query with lots of joins, and the average load times have decreased from over 2s to 0.8s, which is barely acceptable.

but I would rather discuss that in private because of the nature of the project

No probs. Perhaps you could outline briefly the section structure (fields and relationships) and how your DS is set up (filters, sorting, included elements). I'd love to get a similar build locally to see if any core optimisations could be made... and even compare 2.2.5 to 2.3.0.

Sure. The most important sections are these:

Photos (file:unique upload field, album:SBL)
Albums (title:unique input field, year:SBL)
Years (year:number field)

Then I pre-filled the years from 1956 to 2030, and created a datasource (years) to only return years that are equal to or less than the current year, and set param output to ID.

Next, I filtered the Albums (albums) on $ds-years, and set it to output the title and the year and the ID as its output parameter.

With a custom query I then fetched the first image from each album in $ds-albums.

My Album section currently has 170 entries, if that makes any difference;)

I'd love to hear what you found!

Create an account or sign in to comment.

Symphony • Open Source XSLT CMS

Server Requirements

  • PHP 5.3-5.6 or 7.0-7.3
  • PHP's LibXML module, with the XSLT extension enabled (--with-xsl)
  • MySQL 5.5 or above
  • An Apache or Litespeed webserver
  • Apache's mod_rewrite module or equivalent

Compatible Hosts

Sign in

Login details