Symphony.

I have created a data source which simulates the "Archive Overview" format option available in Symphony 1.7. But I experience a performance issue. Creating the XML for 64 news articles grouped by date takes nearly half a second if I select the entry's title field as output. Here are my values for data source execution:

bilder                  0.0699 s
diskussion_sidebar      0.0399 s
kampagnenmotiv          0.0281 s
navigation              0.0201 s
news_archiv             0.2534 s
news_archiv_navigation  0.4418 s
pdfs                    0.0527 s

The data source in question is "newsarchivnavigation" which will output s.th. like:

<news-archiv-navigation>
  <section id="1" handle="news">News</section>
  <year value="2008">
    <month value="04">
      <entry id="731" pdfs="0" bilder="0">
        <titel handle="spd-will-bei-dienstrechtsreform-klarheit-noch-vor">SPD will bei Dienstrechtsreform Klarheit noch vor der Wahl</titel>
      </entry>
  ...
      <entry id="650" pdfs="0" bilder="0">
        <titel handle="die-csu-schwaecht-mit-ihrer-schulpolitik-die-regio">Die CSU schwächt mit ihrer Schulpolitik die Regionen Bayerns</titel>
      </entry>
    </month>
    <month value="03">
      <entry id="642" pdfs="0" bilder="0">
        <titel handle="schneider-kapituliert-vor-reform-des-g8">Schneider kapituliert vor Reform des G8</titel>
      </entry>
  ...
      <entry id="423" pdfs="0" bilder="0">
        <titel handle="ueberwaeltigender-sieg-fuer-spd-in-nuernberg-und-m">Überwältigender Sieg für SPD in Nürnberg und München</titel>
      </entry>
    </month>
    <month value="02">
      <entry id="329" pdfs="0" bilder="4">
        <titel handle="maget-stellt-wahre-schuldenuhr-der-csu-vor-fast">Maget stellt wahre Schuldenuhr der CSU vor – fast fünf Milliarden Euro</titel>
      </entry>
  ...
      <entry id="1" pdfs="0" bilder="0">
        <titel handle="thomas-beyer-csu-gefaehrdet-hausarztversorgung">Thomas Beyer: CSU gefährdet Hausarztversorgung</titel>
      </entry>
    </month>
  </year>
</news-archiv-navigation>

If I deselect the field "titel" (which means including no element, thus creating empty "entry" elements), XML generation takes even longer.

I wonder what will hapen with some hundred entries.

Does anybody know a more efficient way to simulate "Archive Overview" data source behaviour?

Are there any plans to re-introduce "Archive Overview" in Symphony 2?

This is a major concern for me, and something I have lost much sleep over during the last couple of weeks. I will be working on optimisations over the next week or so specifically for revision 6.

Thank you, Alistair.

By the way: Setting query caching to "on" in config.php like this:

$settings['database']['query_caching'] = 'on';

does not seem to change anything. I am on rev 5. Should this not help?

Hmm, it should force all SELECT queries to have SQL_CACHE added to the start. If you remove the setting from config, it defaults that whatever your server says. If it is set to off, you get SQL_NO_CACHE instead.

If you are not noticing any difference, check your MySQL config. Maybe it isn't enabled on your MySQL server yet? Should have something like this:

query_cache_limit       = 1M
query_cache_size        = 16M

Ah, I see, and I checked this. I am rather sure that MySQL caching is working. Could it be that my problem is not query time, but "XML building time"?

Here are the debug screen's "General Details":

Engine Initialisation           0.0076 s
Page creation process started   0.0091 s
XML Built                       1.0082 s
XML Generation                  0.0168 s
Page Built                      1.0282 s
XSLT Transformation             0.0498 s
Page creation complete          1.0873 s

And here the "Full Page Render Statistics":

Total Database Queries           1246
Slow Queries (> 0.09s)           0
Total Time Spent on Queries      0.0707 s
Time Triggering All Events       0.0043 s
Time Running All Data Sources    0.981 s
XML Generation Function          0.0168 s
XSLT Generation                  0.0498 s
Output Creation Time             1.0873 s

Does the above mean that processing takes so long?

I should mention that I am on a webspace with the following virtualisation limitations:

• 1 CPU core (1.6 GHz) – not allocated dynamically!
• RAM 2.048 MB
• PHP memory limit (global): 32 MB
• PHP memory limit (local): 128 MB

I am glad if you can give me a hint how to optimise this.

It would appear that the Database is coping okay, but the actual generating of those entry objects is taking way too long.

I will try to reproduce your setup on our benchmarking site, add in a few thousands entries, and see where the bottlenecks are. The other concerning thing is the 1200+ queries necessary to produce that XML. At the same time I am looking into how I can optimise the database (was up until 2 am last night pondering/experimenting).

Gimme a couple days. I'll let you know what the best way to go is.

Alistair, just in case it helps your research, I could create a copy of the website on a dedicated development webspace and give you access there. You might change anything you like there.