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

[Benjamin Fischer]

Hi Jerome,

Thank you for your answer. I know about the caching feature but
unfortunately in this case the lister is bound to a searchform and
additionally is controlled by GET params using paramsFromGet="true".

Maybe the time consumption comes from several 	instances of
<recombine> <userobj> <php> inside the lister columns with the PHP
Scriptlets accessing the $aData = $this->oDataHandler->_getListData();
severall times for some evaluations and formating?

Best Regards, Benjamin


On Thu, Apr 24, 2008 at 6:03 PM, Jerome Schneider <typo3dev at ameos.com> wrote:
> 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>/