[TYPO3-mvc] Extbase/FLOW3 Performance

[Stefan Neufeind]

Hi 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 ... :-)

On 02/20/2012 12:16 AM, Stefan Frömken wrote:
> 
> I'm not here to discuss about Extbase is slow. There are enough post in
> the internet discussing about that. Further I know...you're working on
> it...

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.

[...]

> 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.

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?

[...]

> 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?

[...]

> For now I have created my own class which extends your storage backend.
> I can define my own fields and my view with over houndreds of queries
> was executed within 2.8 seconds. In the beginning of this project with a
> smaller amount of records the view was finnished in 65seconds.
> 
> Indexes are importend and in my opinion you should give the customer the
> possibility to make use of their indexes.

Well, in your repository you are able to create your own queries that
are optimized to your needs. Or what did I miss here?

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. And going back to fetch a few more fields will actually make
performance much worse. 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.
However you write above that selecting the uid-column in addition
resulted in not using indizes for you?


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.

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