Forums / Developer / Slow down

"Please Note:
  • At the specific request of Ibexa we are changing this projects name to "Exponential" or "Exponential (CMS)" effective as of August, 11th 2025.
  • This project is not associated with the original eZ Publish software or its original developer, eZ Systems or Ibexa".

Slow down

« 
Previous topic
|
Developer
|
Next topic
 »
Author Message

Peretjatko Alexandre

Thursday 02 October 2008 8:57:35 am

Hi ti all,

I'm developping a special operator to record access statistics and I have a verey very slow response in my template.

My template is something like :

{foreach $nodes as $node}
     $l_INT_StatHebdo	= get_stat_semaine( $node.contentobject_id )
     <li>{$node} = {$l_INT_StatHebdo}</li>
{/foreach}

My operator :

function getVuesCountSemaine( $p_INT_NodeId ){
	$l_INT_DebutSemaine	= time()-(7 * 24 * 60 *60 );

	include_once( 'lib/ezdb/classes/ezdb.php' );
	$l_OBJ_db		=& eZDB::instance();
	$l_STR_Requete	= "SELECT count(*) as nb_acces FROM ".TABLE_HEBDOMADAIRE
		." WHERE object_id = '".$p_INT_NodeId
		."' AND date_enregistrement >= '".$l_INT_DebutSemaine
		."';";
	$l_TAB_Resultat	= $l_OBJ_db->arrayQuery($l_STR_Requete);
	unset( $l_OBJ_db);
	return $l_TAB_Resultat[0]['nb_acces'];
}

My table contains about 500 000 lines.

When I comment the operator call in the template the page display is very fats ( less than 1 second ), but when I call the operator, the page display is about 10 seconds !!!

Any suggestion to have a beter performence ?

Alex.

Regards

Alex.
--------------------------------------------------------------
Personnal website : http://www.alex-design.fr

André R.

Thursday 02 October 2008 9:27:22 am

You are doing a sql call pr node, with 500 000 nodes that adds 500 000 sql calls, about 499 500 above what is recommended as maximum pr page..
You should instead fetch it once before the loop and return the result as a keyed array (where object_id is key) to be used inside the loop.

To be able to do that you'll need to GROUP BY the object id so you can get both count and object id in the sql result.

Like this only with COUNT instead of SUM:
http://www.w3schools.com/sql/sql_groupby.asp

eZ Online Editor 5: http://projects.ez.no/ezoe || eZJSCore (Ajax): http://projects.ez.no/ezjscore || eZ Publish EE http://ez.no/eZPublish/eZ-Publish-Enterprise-Subscription
@: http://twitter.com/andrerom

Peretjatko Alexandre

Friday 03 October 2008 12:51:04 am

Oupssss... I'm sorry, my explanations where not as clear as I thaught....

In fact I do not loop on the whole data, but only on a little part ( something like 20 ). I sould write something like that :

$SomeNodes = [The 20 data that I want to display]

{foreach $SomeNodes as $node}
         $l_INT_StatHebdo   = get_stat_semaine( $node.contentobject_id )
         <li>{$node} = {$l_INT_StatHebdo}</li>
{/foreach}

Alex.

Regards

Alex.
--------------------------------------------------------------
Personnal website : http://www.alex-design.fr

André R.

Friday 03 October 2008 3:18:30 am

ok. Well if your database has high trafic and/or many rows this could happen when you try to fetch data and doesn't use table index properly.
Looks like this is a custom table (ref: 'date_enregistrement'), so a bit difficult to help unless you post the table setup with indexes.

eZ Online Editor 5: http://projects.ez.no/ezoe || eZJSCore (Ajax): http://projects.ez.no/ezjscore || eZ Publish EE http://ez.no/eZPublish/eZ-Publish-Enterprise-Subscription
@: http://twitter.com/andrerom

Peretjatko Alexandre

Friday 03 October 2008 5:43:20 am

I've solved my problem !!

In fact the trouble was the table structure herself !
In my operator, I use a request like :

SELECT count(*) as nb_acces FROM tbl_stats WHERE object_id = My_ID

and the table structure was :

CREATE TABLE `tbl_stats` (
  `id` int(11) NOT NULL auto_increment,
  `nom_contenu` varchar(127) collate utf8_unicode_ci NOT NULL default '',
  `action` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  `uri` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `user_id` int(11) NOT NULL default '0',
  `date_enregistrement` int(11) NOT NULL default '0',
  `object_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2958188 ;

I've solved the problem by adding a key on the object_id column like that :

CREATE TABLE `tbl_stats_zi` (
  `id` int(11) NOT NULL auto_increment,
  `nom_contenu` varchar(127) collate utf8_unicode_ci NOT NULL default '',
  `action` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  `uri` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `user_id` int(11) NOT NULL default '0',
  `date_enregistrement` int(11) NOT NULL default '0',
  `object_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `object_id` (`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2958188 ;

.... and I've ordered the table on the object_id column.
And now when I run my SELECT I've got the result about 10 time faster than it used to be before the keying and the ordering action !

Many thanks for the time you've spend on this post André.

Regards

Alex.
--------------------------------------------------------------
Personnal website : http://www.alex-design.fr