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

[Francois Suter]

Hi all,

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.

Cheers

-- 

Francois Suter
Cobweb Development Sarl - http://www.cobweb.ch

[1] https://svn.typo3.org/TYPO3v4/Extensions/overlays/trunk
-------------- next part --------------
A non-text attachment was scrubbed...
Name: T3X_overlays-0_0_0-z-200809161259.t3x
Type: application/octet-stream
Size: 6492 bytes
Desc: not available
Url : http://lists.netfielders.de/pipermail/typo3-project-4-3/attachments/20080916/6fd0eb50/attachment.obj