[TYPO3-project-4-3] Improving the language overlay performance

[Ernesto Baschny [cron IT]]

Hi Francois,

cool stuff, I have also thought about that issue some times.

The non-performance gain on your tests might come from the fact that 
your mysql server has query_cache enabled, which means that several 
repeated queries return very fast (almost like it has been a single 
query). If you instruct apache benchmark to fetch the same page again 
and again, the same queries will probably be generated.

So maybe you could try to fetch "random" records, making sure that no 
repeated records are being fetched on each call.

Reading through the code it looks correct. Having a function like that 
in core would probably make the world a better place, as many more 
extensions might start doing it "right" when it comes to translated records.

Cheers,
Ernesto

Francois Suter wrote: on 16.09.2008 13:00:

> I have been working recently on extensions that can display any record 
> from the TYPO3 database. As such I have had a lot to do with the overlay 
> process with 2 main worries: performance and a useful API.
> 
> The performance is the most important topic. Right now the typical way 
> to get a list of records is to use t3lib_db::exec_SELECTquery() and then 
> call t3lib_page::getRecordOverlay() for each record. The latter queries 
> the database each time, to retrieve the correct overlay. This would seem 
> to be a performance killer on a high-traffic site, especially with 
> USER_INT plug-ins (think search engines, for example).
> 
> I tried to develop an alternate process, by querying all the relevant 
> overlays in one go, and matching them on the PHP-side (precise matching 
> is necessary because of versioning), thereby reducing the number of 
> calls to the database.
> 
> I have bundled the code in an extension called "overlays" which I will 
> attach to this mail (if allowed by the list server) or that can be 
> gotten from forge [1].
> 
> I made some performance test on a page containing a USER_INT plugin 
> which calls 4 different tables, with a varying number of records each 
> (actually 1, 1, 6 and 25, respectively). I'm not a specialist of 
> benchmarking and I just used Apache bench with the following parameters:
> 
> ab -n 250 -c 50 [URL of test page]
> 
> and this clearly hasn't pushed the limits of my local server very far. 
> On several runs I see nearly no performance difference, but maybe I 
> should be pushing my server further and making more runs and averaging 
> the figures. I still wanted to describe all of this to you before going 
> much further, as a kind of sanity check. Do you think that what I'm 
> doing makes sense or am I barking up the wrong tree?
> 
> I know that I have added some refinements to the whole overlay process 
> (for example, by making sure that the query contains all the necessary 
> fields for performing the overlay), so it may be that this additional 
> processing offsets part of the reduced database calls gains.
> 
> Basically the "overlays" extension contains a class called tx_overlays 
> which contains methods to be called statically. In terms of API, there's 
> a method called tx_overlay::getAllRecordsForTable() which takes the same 
> arguments as t3lib_db::exec_SELECTquery() and takes care of returning 
> properly overlaid records. It calls on methods like 
> tx_overlays::getLanguageCondition() and 
> tx_overlays::getEnableFieldsCondition() that take care of setting the 
> right SQL clauses for the developer.
> 
> So it's quite easy to test, because all you have to is include the 
> class.tx_overlays.php class file and call 
> tx_overlay::getAllRecordsForTable() with the same arguments as 
> t3lib_db::exec_SELECTquery() and you will get your fully overlaid 
> recordset.
> 
> I would be very glad to have your feedback about this.