Slow down

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

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

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