From 1179 queries down to 29 without page cache

December 3, 2004

A store owner that hosts on a dedicated server contacted me for assistance with his unusually slow loading site. Initial impressions were the DHTML category menu was extremely slow to load even after the page had been rendered (sometimes reaching 10 seconds to show). Upon installing the debug query contribution here were the initial stats: .692 seconds parse time and 1179 queries

Upon closer inspection it was found the code was querying for ALL the categories via tep_get_categories() function in 3 places. Since he has nearly 200 categories this was absolutely killing his page performance.

So, the first step was to create an array to hold the contents of tep_get_categories() and pass that to each place that required it. However, this still left his page query count well over 600 (minimum) per page.

To address this I serialized the array and stored it in a cached file. Then in application_top.php I included this code via include directive:

<?

 $file = '/path/to/category_tree.php';
 if (file_exists($file)){
	 $tree = unserialize(file_get_contents($file));
 } else {
	 $tree = tep_get_categories();
   if (file_exists($file)) unlink ($file);
   $fp = fopen($file , 'w');
   $fout = fwrite($fp , serialize($tree));
   fclose($fp);
 }

?>

Great...now we're in business since the category tree was now set as the variable $tree and if the file is not present it creates it automatically. I passed this variable to all the necessary places and it worked like a charm. To address admin adding / deleting / modifying categories I made a similiar script to unlink and write the cache file like this:

<?

   $file = '/path/to/category_tree.php';
	 $tree = tep_get_categories();
   if (file_exists($file)) unlink ($file);
   $fp = fopen($file , 'w');
   $fout = fwrite($fp , serialize($tree));
   fclose($fp);

?>

I included the file in admin/categories.php in the appropriate places. Tested and works like a charm.

With the problem of caching the category tree out of the way I turned my attention to the actual category menu itself. I thought about it and decided to try a novel idea for category menu display. I created a categories.php page that used the passed $tree array and parse it into a collapsable CSS driven menu system.

Here is the CSS:

dd {
margin: 0;
padding: 0;
list-style-type: none;
display:none;
}

dl, dt, ul, li {
margin: 0;
padding: 0;
list-style-type: none;
}
#menu {
position: relative;
top: 0;
left: 0;
}
dl#menu {
width: 18em;
}
dl#menu dt {
cursor: pointer;
margin: 2px 0;;
height: 14px;
line-height: 14px;
text-align: center;
font-weight: bold;
border: 1px solid gray;
background-image: url('menu.gif');
}
dl#menu dd {
border: 1px solid gray;
}
dl#menu li {
text-align: left;
background: #fff;
font-weight: bold;
}
dl#menu li a, dl#menu dt a {
color: #000;
text-decoration: block;
display: block;
border: 0 none;
height: 100%;
}

dl#menu li a:hover, dl#menu dt a:hover {
background-image: url('topgradient.gif');
}

Here is the java script:

<script type="text/javascript">
<!--
window.onload=expand;
function expand(id) {
var d = document.getElementById(id);
for (var i = 0; i<=<? echo sizeof($tree); ?>; i++) {
 if (document.getElementById('smenu'+i)) {document.getElementById('smenu'+i).style.display='none';}
}
if (d) {d.style.display='block';}
}
//-->
</script>

...and finally here is the includes/boxes/categories.php code:

<?php
/*
 $Id: categories.php,v 1.25 2003/07/09 01:13:58 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/
?>
<!-- categories //-->
         <tr>
           <td>
<?php 
 $info_box_contents = array();
 $info_box_contents[] = array('text' => BOX_HEADING_CATEGORIES);

 new infoBoxHeading($info_box_contents, true, false);
 
 $categories_string = '<dl id="menu">'."\n";
 $info_box_contents = array();

 for ($i=0; $i<sizeof($tree);$i++){
$level = substr_count($tree[$i]['text'], ' ');
switch ($level) {
 case 0:
	 if ($i === 0) {
	 $categories_string .= 
	 '<dt onclick="javascript:expand(\'smenu'.$i.'\');">'.$tree[$i]['text']. '</dt>'."\n".
	 '<dd id="smenu'.$i.'">'."\n".'<ul>'."\n";
	 $categories_string .= '<li><a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath='.$tree[$i]['id'], 'NONSSL') . '">' . $tree[$i]['text'] . '</a></li>'."\n";
	 
	 } else {
	 $categories_string .= 
	 '</ul>'."\n".'</dd>'."\n".'<dt onclick="javascript:expand(\'smenu'.$i.'\');">'.$tree[$i]['text'] . '</dt>'."\n".
	 '<dd id="smenu'.$i.'">'."\n".'<ul>'."\n";
	 $categories_string .= '<li><a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath='.$tree[$i]['id'], 'NONSSL') . '">' . $tree[$i]['text'] . '</a></li>'."\n";

	 }
   break;
 case 2: 	 
 case 4:
	 $categories_string .= '<li><a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath='.$tree[$i]['id'], 'NONSSL') . '">' . $tree[$i]['text'] . '</a></li>'."\n";
	 break;
}

 }
 $categories_string .= '</ul>'."\n".'</dd>'."\n".'</dl>'."\n";
 $info_box_contents[] = array('align' => 'left',
                              'text' => $categories_string);

 new infoBox($info_box_contents);
?>
         </td>
         </tr>
<!-- categories_eof //-->

The above code resulted in an extremely flexible and effective alternative to the traditional DHTML menus and is completely CSS driven in terms of presentation. It is vastly faster to render than the DHTML and presents the categories in a logical and easy to navigate manner.

The category tree array cache file system was thoroughly tested and found to be stable and bug free. The CSS was tweaked by the store owner to fit his store theme within minutes.

BEFORE

.692 seconds parse time and 1179 queries

FINAL

.060 second parse time and 29 queries (without page cache!)

//#########################

// Going the extra mile

While creating the code above the server had several performance issues. I offered to recompile Apache (was CGI-PHPSuexec) into SAPI mode and also upgrade his PHP install. This was done to allow a php accelerator which will further increase his server performance. Recompile and accelerator install took about 30 minutes.

FINAL [FINAL :)]

0.009 second parse time and 9 queries (with page cache)

A working example of the CSS driven and collapsible category menu system can be previewed here: MDofPC.

My standard disclaimer: I am not a graphic designer and did not create this site theme. I merely modified code to get him better performance. Please direct all site critique to the store owner.

Harald Ponce de Leon · December 5, 2004

It can be taken further down to 1 (uncached) query - look at the new categoryTree class in CVS ;)

I'm sure you can backport it for MS2 based stores.

dreamscape · December 5, 2004

Just curious, but if the point of this is to be more efficient, why you would then use the "old school" array looping methods, when the newer methods have been proven to be more efficient and faster?

for ($i=0; $i<sizeof($tree);$i++){

December 5, 2004

It can be taken further down to 1 (uncached) query - look at the new categoryTree class in CVS ;)

I'm sure you can backport it for MS2 based stores.

<{POST_SNAPBACK}>

I'll take a look...MS3 never ceases to amaze me :)

Just curious, but if the point of this is to be more efficient, why you would then use the "old school" array looping methods, when the newer methods have been proven to be more efficient and faster?
for ($i=0; $i<sizeof($tree);$i++){
<{POST_SNAPBACK}>

The speed of the loop is insignificant since it does not execute on every page request. It is only executed when the cache file is not present or the admin modifies / adds / deletes a category.

berkedam · December 5, 2004

In all my 4 browsers:

The first time you click on a category it closes immediately again.

On a second try, second click, the menu stays open but after you click on a subcategory the menu closed again completely.

It should stay open IMO untill I click on another menuitem.

That this menu is depending on JS, i.e. it's not working at all if JS is turned off, is another thing I don't like, there should at least be a back-up.

Not everybody uses Opera with Shift + F11 :(

December 5, 2004

In all my 4 browsers:

The first time you click on a category it closes immediately again.

On a second try, second click, the menu stays open but after you click on a subcategory the menu closed again completely.

It should stay open IMO untill I click on another menuitem.

That this menu is depending on JS, i.e. it's not working at all if JS is turned off, is another thing I don't like, there should at least be a back-up.

Not everybody uses Opera with Shift + F11 :(

<{POST_SNAPBACK}>

Great! Thanks for your feedback!

However, this is not released as a contribution but rather the seed for someone to tweak it for their needs and have them release it. The fact is I wanted to give everyone the code but don't want to support it.

So, if you want a javascript backup code one and release it as a contribution...

dreamscape · December 5, 2004

The speed of the loop is insignificant since it does not execute on every page request. It is only executed when the cache file is not present or the admin modifies / adds / deletes a category.

<{POST_SNAPBACK}>

That may be true; however, my point was, if you are really going for efficient performance, then why are you writing inefficient code? Just nit-picking is all ;)

December 5, 2004

That may be true; however, my point was, if you are really going for efficient performance, then why are you writing inefficient code? Just nit-picking is all ;)

<{POST_SNAPBACK}>

The store went from .692 seconds parse time and 1179 queries down to 0.009 second parse time and 9 queries. I had to use a chain saw before breaking out the surgeon's knife...to be honest I don't believe changing the loop will affect parse time except in the 9th or 10th decimal place.

Like I said before...I posted the info as an example or guide. For myself, it was a proof of concept that an alternative to DHTML menus could be created and used successfully on a high volume site.

Clean it up, add a noscript backup block, and then post it in the contribution area as your own work...I honestly don't care. Just be prepared for the nit-pickers :)

July 27, 2005

I know Chemo is no longer around :( but I was wondering if anyone else could port the cvs category tree back to MS2.2

The code currently looks like....

<?php
/*
 $Id: category_tree.php,v 1.2 2004/10/26 20:07:09 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2004 osCommerce

 Released under the GNU General Public License
*/

 class osC_CategoryTree {
   var $root_category_id = 0,
       $max_level = 0,
       $data = array(),
       $root_start_string = '',
       $root_end_string = '',
       $parent_start_string = '',
       $parent_end_string = '',
       $parent_group_start_string = '<ul>',
       $parent_group_end_string = '</ul>',
       $child_start_string = '<li>',
       $child_end_string = '</li>',
       $breadcrumb_separator = '_',
       $breadcrumb_usage = true,
       $spacer_string = '',
       $spacer_multiplier = 1,
       $follow_cpath = false,
       $cpath_array = array(),
       $cpath_start_string = '',
       $cpath_end_string = '',
       $show_category_product_count = false,
       $category_product_count_start_string = ' (',
       $category_product_count_end_string = ')';

   function osC_CategoryTree($load_from_database = true) {
     global $osC_Database, $osC_Session, $osC_Cache;

     if (SHOW_COUNTS == 'true') {
       $this->show_category_product_count = true;
     }

     if ($load_from_database === true) {
       if ($osC_Cache->read('category_tree-' . $osC_Session->value('language'), 720)) {
         $this->data = $osC_Cache->getCache();
       } else {
         $Qcategories = $osC_Database->query('select c.categories_id, cd.categories_name, c.parent_id from :table_categories c, :table_categories_description cd where c.categories_id = cd.categories_id and cd.language_id = :language_id order by c.parent_id, c.sort_order, cd.categories_name');
         $Qcategories->bindRaw(':table_categories', TABLE_CATEGORIES);
         $Qcategories->bindRaw(':table_categories_description', TABLE_CATEGORIES_DESCRIPTION);
         $Qcategories->bindInt(':language_id', $osC_Session->value('languages_id'));
         $Qcategories->execute();

         $this->data = array();

         while ($Qcategories->next()) {
           $this->data[$Qcategories->valueInt('parent_id')][$Qcategories->valueInt('categories_id')] = array('name' => $Qcategories->value('categories_name'), 'count' => 0);
         }

         $Qcategories->freeResult();

         if ($this->show_category_product_count === true) {
           $this->calculateCategoryProductCount();
         }

         $osC_Cache->writeBuffer($this->data);
       }
     }
   }

   function setData(&$data_array) {
     if (is_array($data_array)) {
       $this->data = array();

       for ($i=0, $n=sizeof($data_array); $i<$n; $i++) {
         $this->data[$data_array[$i]['parent_id']][$data_array[$i]['categories_id']] = array('name' => $data_array[$i]['categories_name'], 'count' => $data_array[$i]['categories_count']);
       }
     }
   }

   function buildBranch($parent_id, $level = 0) {
     $result = $this->parent_group_start_string;

     if (isset($this->data[$parent_id])) {
       foreach ($this->data[$parent_id] as $category_id => $category) {
         if ($this->breadcrumb_usage == true) {
           $category_link = $this->buildBreadcrumb($category_id);
         } else {
           $category_link = $category_id;
         }

         $result .= $this->child_start_string;

         if (isset($this->data[$category_id])) {
           $result .= $this->parent_start_string;
         }

         if ($level == 0) {
           $result .= $this->root_start_string;
         }

         $result .= str_repeat($this->spacer_string, $this->spacer_multiplier * $level) . '<a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath=' . $category_link) . '">';
         if ($this->follow_cpath === true) {
           if (in_array($category_id, $this->cpath_array)) {
             $result .= $this->cpath_start_string . $category['name'] . $this->cpath_end_string;
           } else {
             $result .= $category['name'];
           }
         } else {
           $result .= $category['name'];
         }
         $result .= '</a>';

         if ($this->show_category_product_count === true) {
           $result .= $this->category_product_count_start_string . $category['count'] . $this->category_product_count_end_string;
         }

         if ($level == 0) {
           $result .= $this->root_end_string;
         }

         if (isset($this->data[$category_id])) {
           $result .= $this->parent_end_string;
         }

         $result .= $this->child_end_string;

         if (isset($this->data[$category_id]) && (($this->max_level == '0') || ($this->max_level > $level+1))) {
           if ($this->follow_cpath === true) {
             if (in_array($category_id, $this->cpath_array)) {
               $result .= $this->buildBranch($category_id, $level+1);
             }
           } else {
             $result .= $this->buildBranch($category_id, $level+1);
           }
         }
       }
     }

     $result .= $this->parent_group_end_string;

     return $result;
   }

   function buildBranchArray($parent_id, $level = 0, $result = '') {
     if (empty($result)) {
       $result = array();
     }

     if (isset($this->data[$parent_id])) {
       foreach ($this->data[$parent_id] as $category_id => $category) {
         if ($this->breadcrumb_usage == true) {
           $category_link = $this->buildBreadcrumb($category_id);
         } else {
           $category_link = $category_id;
         }

         $result[] = array('id' => $category_link,
                           'title' => str_repeat($this->spacer_string, $this->spacer_multiplier * $level) . $category['name']);

         if (isset($this->data[$category_id]) && (($this->max_level == '0') || ($this->max_level > $level+1))) {
           if ($this->follow_cpath === true) {
             if (in_array($category_id, $this->cpath_array)) {
               $result = $this->buildBranchArray($category_id, $level+1, $result);
             }
           } else {
             $result = $this->buildBranchArray($category_id, $level+1, $result);
           }
         }
       }
     }

     return $result;
   }

   function buildBreadcrumb($category_id, $level = 0) {
     $breadcrumb = '';

     foreach ($this->data as $parent => $categories) {
       foreach ($categories as $id => $info) {
         if ($id == $category_id) {
           if ($level < 1) {
             $breadcrumb = $id;
           } else {
             $breadcrumb = $id . $this->breadcrumb_separator . $breadcrumb;
           }

           if ($parent != $this->root_category_id) {
             $breadcrumb = $this->buildBreadcrumb($parent, $level+1) . $breadcrumb;
           }
         }
       }
     }

     return $breadcrumb;
   }

   function buildTree() {
     return $this->buildBranch($this->root_category_id);
   }

   function getTree($parent_id = '') {
     return $this->buildBranchArray((empty($parent_id) ? $this->root_category_id : $parent_id));
   }

   function calculateCategoryProductCount() {
     foreach ($this->data as $parent => $categories) {
       foreach ($categories as $id => $info) {
         $this->data[$parent][$id]['count'] = $this->countCategoryProducts($id);

         $parent_category = $parent;
         while ($parent_category != $this->root_category_id) {
           foreach ($this->data as $parent_parent => $parent_categories) {
             foreach ($parent_categories as $parent_category_id => $parent_category_info) {
               if ($parent_category_id == $parent_category) {
                 $this->data[$parent_parent][$parent_category_id]['count'] += $this->data[$parent][$id]['count'];

                 $parent_category = $parent_parent;
                 break 2;
               }
             }
           }
         }
       }
     }
   }

   function countCategoryProducts($category_id) {
     global $osC_Database;

     $Qcategories = $osC_Database->query('select count(*) as total from :table_products p, :table_products_to_categories p2c where p2c.categories_id = :categories_id and p2c.products_id = p.products_id and p.products_status = 1');
     $Qcategories->bindRaw(':table_products', TABLE_PRODUCTS);
     $Qcategories->bindRaw(':table_products_to_categories', TABLE_PRODUCTS_TO_CATEGORIES);
     $Qcategories->bindInt(':categories_id', $category_id);
     $Qcategories->execute();

     $count = $Qcategories->valueInt('total');

     $Qcategories->freeResult();

     return $count;
   }

   function setRootCategoryID($root_category_id) {
     $this->root_category_id = $root_category_id;
   }

   function setMaximumLevel($max_level) {
     $this->max_level = $max_level;
   }

   function setRootString($root_start_string, $root_end_string) {
     $this->root_start_string = $root_start_string;
     $this->root_end_string = $root_end_string;
   }

   function setParentString($parent_start_string, $parent_end_string) {
     $this->parent_start_string = $parent_start_string;
     $this->parent_end_string = $parent_end_string;
   }

   function setParentGroupString($parent_group_start_string, $parent_group_end_string) {
     $this->parent_group_start_string = $parent_group_start_string;
     $this->parent_group_end_string = $parent_group_end_string;
   }

   function setChildString($child_start_string, $child_end_string) {
     $this->child_start_string = $child_start_string;
     $this->child_end_string = $child_end_string;
   }

   function setBreadcrumbSeparator($breadcrumb_separator) {
     $this->breadcrumb_separator = $breadcrumb_separator;
   }

   function setBreadcrumbUsage($breadcrumb_usage) {
     if ($breadcrumb_usage === true) {
       $this->breadcrumb_usage = true;
     } else {
       $this->breadcrumb_usage = false;
     }
   }

   function setSpacerString($spacer_string, $spacer_multiplier = 2) {
     $this->spacer_string = $spacer_string;
     $this->spacer_multiplier = $spacer_multiplier;
   }

   function setCategoryPath($cpath, $cpath_start_string = '', $cpath_end_string = '') {
     $this->follow_cpath = true;
     $this->cpath_array = explode($this->breadcrumb_separator, $cpath);
     $this->cpath_start_string = $cpath_start_string;
     $this->cpath_end_string = $cpath_end_string;
   }

   function setFollowCategoryPath($follow_cpath) {
     if ($follow_cpath === true) {
       $this->follow_cpath = true;
     } else {
       $this->follow_cpath = false;
     }
   }

   function setCategoryPathString($cpath_start_string, $cpath_end_string) {
     $this->cpath_start_string = $cpath_start_string;
     $this->cpath_end_string = $cpath_end_string;
   }

   function setShowCategoryProductCount($show_category_product_count) {
     if ($show_category_product_count === true) {
       $this->show_category_product_count = true;
     } else {
       $this->show_category_product_count = false;
     }
   }

   function setCategoryProductCountString($category_product_count_start_string, $category_product_count_end_string) {
     $this->category_product_count_start_string = $category_product_count_start_string;
     $this->category_product_count_end_string = $category_product_count_end_string;
   }
 }
?>

Not being a coder it looks like theres some database storage of the category tree going on here somewhere. Can anyone help out here?

July 27, 2005

I know Chemo is no longer around :( but I was wondering if anyone else could port the cvs category tree back to MS2.2

Not being a coder it looks like theres some database storage of the category tree going on here somewhere. Can anyone help out here?

<{POST_SNAPBACK}>

Has anyone managed to get it ported back yet???

July 27, 2005

Has anyone managed to get it ported back yet???

<{POST_SNAPBACK}>

erm isnt that what I said? ;)

From 1179 queries down to 29 without page cache

Recommended Posts

Guest

Harald Ponce de Leon

dreamscape

Guest

berkedam

Guest

dreamscape

Guest

Guest

Guest

Guest

Archived

Browse

Activity