[TYPO3-project-formidable] DATASOURCE logic isn't correct / Formidable Performance

[Jerome Schneider]

Hi Benjamin,
There indeed seems to be something really time-consuming in what you 
describe.

To help a bit: you may cache your list using cacheHash="true" on your 
lister, if you're not binding it with a searchform of course.

<renderlet:LISTER name="mylist" cacheHash="true">
	<!-- ... -->
</renderlet:LISTER>

When'Ill have a bit time I'll profile the code execution with xdebug to 
figure out what's happening there.

Also, thanks for helping !


Benjamin Fischer a écrit :
> Hello formidable List,
> 
> I tried to investigate this performance issue further. If I remove the 
> renderer:TEMPLATE and replace all instances of the renderer:DATE with 
> renderer:TEXT I still get around the same execution times. Looking in 
> the debug call stack shows me that most of the time is used in the 
> DATASOURCE:DB that looks through my database table with ~ 70 000 rows. 
> It takes almost 7 seconds to get the results of the query.
> 
> If I copy the SQL code of the debug log into phpMyAdmin the same result 
> much faster: (25 total, Query took 1.2281 sec).
> 
> Is there a way to speed up the query/result or the whole formidable 
> application?
> 
> All the Best, Benjamin
> 
> 
> 
> 
> 
> Am 23.04.2008 um 12:52 schrieb Benjamin Fischer:
> 
>> Hello Serg, Hello fomidable list,
>>
>> I am not sure if you are right with your assumption that a formidable
>> lister page does not make a limit to SQL querys if I enable
>> <debug>true</debug> in my configuration I can see that SELECT
>> SQL_CALC_FOUND_ROWS and the limit <rows perpage="25" /> is in fact used.
>> Maybe someone from the formidable core team could confirm this.
>>
>> Nonetheless I would be interested if there are options to speed up
>> formidable applications. I am using a lister to display and search in
>> a sql table with currently around 70 000 entries (growing rapidly) and
>> get exec times of about 8.288 seconds for a page with 25 rows. This
>> seems very slow compared to for example phpmyadmin with the same query.
>>
>> Searching through the debug call stack shows me that several instances
>> of the DATE Renderlet might take up most of that time.
>>
>> Example:
>> #16 - AMEOS_FORMIDABLE_RDT_DATE datetime - TIMESTAMP TO DATE CONV.
>> (Total exec. time: 1.921 sec)
>> ...
>> here are the 50 instances for 2 date/times in each row in a lister
>> limited to 25 rows per page.
>> ...
>> #40 - AMEOS_FORMIDABLE_RDT_DATE datetime - TIMESTAMP TO DATE CONV.
>> (Total exec. time: 7.686 sec)
>>
>> can this be true that only the date conversions take up almost 6
>> seconds of the total time of 8 seconds?
>>
>> To test this I have replaced renderlet:DATE with renderlet:TEXT and
>> still get almost the same execution times.
>>
>> This time it seems to be used for rendering the template - so I am not
>> shure were to start the optimization
>>
>> #15 - DATASOURCE:DB [actionlister]
>> (Total exec. time: 0.685 sec)
>> SELECT SQL_CALC_FOUND_ROWS tx_bftma2tracker_log.uid,
>> tx_bftma2tracker_log.session, tx_bftma2tracker_log.datetime,
>> tx_bftma2tracker_log.ip, tx_bftma2tracker_log.host,
>> tx_bftma2tracker_log.browser, tx_bftma2tracker_log.ifgroup,
>> tx_bftma2tracker_log.language, tx_bftma2tracker_log.query as query,
>> tx_bftma2tracker_log.category as category, tx_bftma2tracker_log.count,
>> tx_bftma2tracker_log.title, tx_bftma2tracker_log.image as image,
>> tx_bftma2tracker_log.user, tx_bftma2tracker_log.action as actionuid,
>> tx_bftma2tracker_log.used_name, tx_bftma2tracker_action.action,
>> fe_users.company, fe_users.email, fe_users.first_name,
>> fe_users.last_name, fe_users.name, fe_users.gender FROM
>> tx_bftma2tracker_log, tx_bftma2tracker_action, fe_users WHERE
>> tx_bftma2tracker_log.action = tx_bftma2tracker_action.uid AND
>> tx_bftma2tracker_log.user = fe_users.uid AND
>> tx_bftma2tracker_log.deleted=0 ORDER BY tx_bftma2tracker_log.datetime
>> DESC LIMIT 0, 25
>>
>> #16 - RENDERER TEMPLATE - rendered elements array
>> (Total exec. time: 7.987 sec)
>>
>> I would be very happy if someone with formidable experience could help
>> speeding up exection times of formidable rendering and/oir share his/
>> her experience with the topic. Formidable ist really really great for
>> my needs the only critical point seems to be the execution time in
>> combination with large datasets.
>>
>> Best Regards, Benjamin
>>
>>
>> -- 
>>
>> Benjamin Fischer | Dipl.-Des.
>> -
>> Studio Stuttgart  (D)  +49 711 50 88 97 04
>> Studio Strasbourg  (F)  +33 954 01 87 04
>> -
>> Am 22.04.2008 um 09:22 schrieb Nikitin Sergey:
>>
>>> I have a table fe_users there are about 1300 records.
>>>
>>> Also I have a datasource with sql => SELECT * FROM fe_users WHERE
>>> pid=$pid
>>>
>>> I found that it doesn't make a LIMIT query with LISTER's pager... WHY?
>>> refering to MYSQL doc
>>> (http://dev.mysql.com/doc/refman/4.1/en/limit-optimization.html) I
>>> found
>>> that we can count total result number just using COUNT(*) for all
>>> LIMITED rows (For ex LIMIT 0,10 COUNT(*) will return "10") or
>>> SQL_CALC_FOUND_ROWS for selecting all founded rows. And it will be one
>>> query for a one datasource. Please fix it in next revision, because it
>>> makes a huge memory usage with big tables.
>>>
>>>
>>> P.S. Sorry for my english.
>>>
>>>
>>> Best regards, Serg (nikitinsm at gmail.com)


-- 


*Jérôme Schneider*
/Developer/
/Formidable - Rapid Application Developpement Framework for Typo3
<http://formidable.typo3.ug>/
/Ameos <http://www.ameos.com>/