TYPO3 Extbase queries always super slow

Question

When pulling 1000+ records, now matter how simple, using Extbase in TYPO3 (in a custom extension), it always takes forever. Using custom SELECT statements (like $query->statement( "SELECT......) though is MUCH faster.

I've worked with TYPO3 (and Extbase) for years and years, and I always run into the problem that when working with large amounts (1000+) of records, pulling and displaying data from any table gets REALLY SLOW as the number of records grows. I always end up using $query->statement( "SELECT * from this or that table" ), which for some reason is MUCH faster than using Extbase's built-in query methods that use $query->matching(........);

Does anyone know how to speed up the regular (built-in) query methods so they match the "custom statement method" speed?

I've experienced this issue ever since version 6.x - I'm now using 9.x

Answer 1

Extbase will fetch relations (which aren't lazy-loaded) and will perform a heap of object mapping, class introspection, SQL table/map resolving and more. This is why Extbase will be inherently slower than your query - Extbase simply does a lot, lot more than just a query.

Methods to speed up, the relevance of which depends on your use case which you didn't describe:

• Ensure presence of indexes and keys for every last property that is part of a relation, table join condition clause.
• Mark properties you do not use / rarely use for the particular list view in question, as lazy-loaded to avoid querying them until they are accessed.
• If the above is still not sufficient:
  • Create a flattened table that contains properties extracted from multiple tables.
  • Create a flattened table as SQL view (and allow it to cache)
  • Create a very limited model to reflect your listed object and use this object (with the standard object table or the flattened table)

Each of these will improve the performance iteratively, with the most "bang for your buck" coming from the last group of suggestions.