[TYPO3-project-formidable] DATASOURCE logic isn't correct / Formidable Performance
Benjamin Fischer
mailbox at typedown.com
Wed Apr 30 14:48:08 CEST 2008
Hello Jerome, Hello Formidable List,
as I was still plagued by the performance issues when accessing a
datasource:DB with a renderlet:LISTER bound to a searchbox I have
rebuild the XML Configuration file from the scratch. While assembling
all renderlets to suit the application needs I found out that the
execution time arises each time the function $aData =
$this->oDataHandler->_getListData(); is used inside a column of the
lister for example:
<column name="action" type="renderlet:BOX" listHeader="Action">
<recombine>
<userobj>
<php><![CDATA[/*<?php*/
$aData = $this->oDataHandler->_getListData();
$actionname = $aData["action"];
$actionname = str_replace("[", "<br />[", $actionname);
return $actionname;
/*?>*/]]></php>
</userobj>
</recombine>
</column>
as I used _getListData very often in several columns of the lister the
performance always went down further.
Then I found a solution to solve this problem:
Move all manipulations of the raw data to <beforerender><userobj><php>
inside the lister. The difference in the performance was so much
better I could not believe it at first.
Now my question is:
Does $this->oDataHandler->_getListData() reload the whole data each
time it is used and not only the data of the current row?
Are there other ways to access a value inside a lister column/row via PHP?
Thank you for your time, All the best, Benjamin
On Mon, Apr 28, 2008 at 2:43 PM, Benjamin Fischer <mailbox at typedown.com> wrote:
> 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>/
More information about the TYPO3-project-formidable
mailing list