#56 Menuman generates too many queries

[Eloi George]

While database loadtesting a module, I realized that
MenuManager was generating a separate database query
for each menu item. In this site's case, it caused 206
database queries per pageview!

To get the database queries down to 1 per menu block
(in my case, 2 total), I rewrote functions
build_items() and get_item_child() in
/mod/menuman/class/Menu.php. Everything's now done in
memory, which according to PHPWS_Timer reduced the
execution time of that module by 50%! The data in the
memory db is unset as it is used to reduce memory
requirements.

Here are the replacement functions:

/**
* build_items
*
* Builds the menu_items array
*/
function build_items() {
$level = 0;
unset($this->menu_items);
$sql = "SELECT * FROM
{$GLOBALS['core']->tbl_prefix}mod_menuman_items WHERE
menu_id='$this->menu_id' ORDER BY menu_item_order";
$menu_item_result = $GLOBALS['core']->query($sql);
if($menu_item_result->numrows() == 0)
return false;

/* Initialize toplevel and child_data arrays */
$toplevel = $child_data = array();
while($menu_item =
$menu_item_result->fetchrow(DB_FETCHMODE_ASSOC)) {

if($menu_item['menu_item_id']==$menu_item['menu_item_pid'])
$toplevel[] = $menu_item;
else {
if(!isset($child_data['menu_item_pid']))
$child_data['menu_item_pid'] = array();
$child_data['menu_item_pid'][] = $menu_item;
}
}
unset($menu_item_result);

foreach($toplevel as $menu_item) {
$this->menu_items[$menu_item['menu_item_id']] =
new PHPWS_MenuItem($menu_item, $level);
if(isset($child_data[$menu_item['menu_item_id']])) {

$this->get_item_child($menu_item['menu_item_id'],
$level, $child_data);
$level--;
}
}
} // END FUNC build_items

/**
* get_item_child
*
* Gets children for the build_items function
*
* @param integer $parent_id id of the parent item
*/
function get_item_child($parent_id, &$level,
&$child_data) {
if(!isset($child_data[$parent_id]))
return false;

$level++;
foreach($child_data[$parent_id] as $menu_item) {
$this->menu_items[$menu_item['menu_item_id']] =
new PHPWS_MenuItem($menu_item, $level);
if(isset($child_data[$menu_item['menu_item_id']])) {

$this->get_item_child($menu_item['menu_item_id'],
$level, $child_data);
$level--;
}
}
unset($child_data[$parent_id]);
} // END FUNC get_item_child

[Anonymous]

Logged In: YES
user_id=400519

Thanks. This is implemented in version 0.10.1