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

[Benjamin Fischer]

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)