Share » Forums » Developer » Slow down

Slow down

Slow down

Thursday 02 October 2008 8:57:35 am - 4 replies

Modified on Thursday 02 October 2008 9:00:01 am by Peretjatko Alexandre

Author Message

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

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu