[TYPO3-mvc] Extbase/FLOW3 Performance

[Stefan Frömken]

Am 20.02.2012 09:16, schrieb Stefan Neufeind:
> Hi Stefan,
Hello Stefan
>
> I can only speak for myself at the moment. Reading your mail it sounded
> like you are quite "disappointed" when writing it. I hope we can turn
> that into "positive energy" somehow ... :-)
I like the way of extbase. It is pretty cool and it is very simple to 
create a little extension based on ONE table. Show some data is not that 
problem, but in a bigger project you will need some performance issues.

>
> We're surely interested in ways to enhance performance. Please keep in
> mind though that it can't be totally MySQL-centric. But reading your
> email it seems that not MySQL-specific functions are part of your
> optimizations but maybe query-optimizations.
> Maybe there is a way to optimize something - I hope so.
Just yesterday I have started to document Indices in MySQ; and how using 
them and prevent filesort. It's german if you're interessted:
http://typo3.sfroemken.de/server/mysql.html
>
> [...]
>
>> Here is a simple query:
>>
>> SELECT SUM(nettobetrag) AS nettobetrag FROM myMillionRecordsTable WHERE
>> ((beleg_datum>= '1293836400' AND beleg_datum<= '1306879200') AND k_nr
>> = '1610280') AND pid IN (0);
>>
>> This query needs 3.6 seconds. But I have to execute similar queries with
>> different dates over a houndred times for one view. What do you think
>> how long the customer have to wait? Too long.
>
> That query only takes this long? Hmm, I expect that should be because it
> can't easily execute it. It has to iterate over all the lines.
That's right. Without an Indice MySQL has to iterate all lines.
>
> But you wrote that you optimized it by adding additional indizes. And
> you really should, since only you know which queries you are going to
> execute on what data.
> So what is it that "Extbase cound have done better" here?
Nothing. This query was only the start to show indices are important.
>
> [...]
>
>> SELECT * FROM tt_content WHERE sys_language_uid=0 ORDER BY l18n_parent;
>
> [...]
>
>> SELECT uid, l18n_parent, sys_language_uid from tt_content where
>> sys_language_uid = 0 order by l18n_parent;
>
> [...]
>
>> SELECT l18n_parent, sys_language_uid from tt_content where
>> sys_language_uid = 0 order by l18n_parent;
>
> I don't see why only in the latter case it would use an index. For where
> and order MySQL is able to use indexes in both cases imho, if you select
> uid as well or not. So where's the problem with that?
If you query them with EXPLAIN you will see that MySQL uses in Indice in 
all cases as you can see in col "key". The normal case is: Search for 
the WHERE part in the indice, get the pointer to find the record faster 
in the table, get the data from the table and return the requested fields.
The difference to the last query is: All fields which have to be return 
are already in the indice. So MySQL don't have to get data out of the 
table because data is in indice. That's the fastest way to get data in 
my opinieon.
>
> [...]
>
> Well, in your repository you are able to create your own queries that
> are optimized to your needs. Or what did I miss here?
That's right, but you also know that this is not the extbase way. Using 
statements is just 0.3 - 0.5ms faster than the extbase way on my little 
virtual machine.
>
> Selecting all columns for Extbase has the background that it doesn't
> know at that point which fields you will need when later in your
> objects.
Absolutely correct. And here we got the problem between usefriendly, 
proformance optimization and a better individualization of queries.

> And going back to fetch a few more fields will actually make
> performance much worse.
Right

> So the idea is to load all that is possible in
> one go - assuming that reading one more field or not is not that much of
> a problem once the row-selection is done.
But exactly this is the problem. I have created my own indices to 
prevent an additional access to the database. All data I need is just in 
the indice and the extbase way gives me no way to query only a part of 
fields/cols. You have started field definitions in storage backend 
already. There is a row called:

$sql['fields'] = array();

The only missing part is $this->parseFields() and a getter/setter for 
fields in the query object.

> However you write above that selecting the uid-column in addition
> resulted in not using indizes for you?
It uses an indice but can't read from the indice because uid was not 
indexed as well, which starts an additional access on the filesystem.
>
>
> It's great that you have such a massive amount of data for testing. And
> it would be great if you could look a bit deeper at what could be done
> on the query-side. Just please keep in mind that we need to find some
> way that Extbase can help you with this and it needs to be "generic" in
> a certain way.
I found many performance problems, but I think in most cases it's a 
user/programmer problem. Here some samples:

Using:

$key = Tx_Extbase_Reflection_ObjectAccess::getProperty($value, 
$this->arguments['optionValueField']);

is much slower than

$methodName = t3lib_div::lowerCamelCase($property);
$key = $value->get$methodName;

It gives me roundabout 3 secs on my very hugh result list.

Next problem:

I have breaked down table generation in fluid up to td/th partials. So I 
have a partial for the table, one for tr and a very often called partial 
for td/th parts. In TYPO3 4.5 showing this result is 3 seconds slower 
than viewing it with TYPO3 4.6. I have merged all these partials into 
one big partial now and it was over 5 seconds faster now.

As you can see. In most cases extbase is not the bottleneck. It's a 
programmer problem. I don't know HOW to make Extbase faster, but Extbase 
should allow the programmer to be fast!

>
> Maybe after getting up this morning and reading your mail again: What
> are the things you see that might be possible to improve?
>
>
> Kind regards,
>   Stefan Neufeind

Stefan