Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

A Store Speed Optimization in Progress


Guest

Recommended Posts

But perhaps I should firstly focus on the index.php:

I still have these ~200 queries, coming from "select c.categories..... where parent_id = 'xx', which is produced by some queries in the index.php.

IMHO that comes from the category check, how many sub-cats are available, is the deepest end of the cats reached etc.

Is it correct, that the cat query is querying all cat and sub-cats every time?

Can I somehow optimize that ?

Or is this quantity of queries only resulting from my quantity of categories?

 

The select code from languages queries should be easy to solve (it that hasn't been done already in newer versions).

 

The select c.categories..... where parent_id = 'xx' queries are typical for the drop-down in the categories box of the category box enhanced contribution.

 

But you wouldn't see those if you commented out the inclusion of the categories box (unless you use an old version of the STS template contribution where the drop-down queries were executed even if you didn't use the drop-down).

 

For the osC2.2 rc2a with standard install of products I found the following number of queries:

 

index.php: 84, cPath=1: 125, product_info.php?products_id=20: 103

 

With the enhanced category box added:

 

index.php: 104, cPath=1: 147, product_info.php?products_id=20: 126 (so about the number of categories higher...)

 

With an adapted CountProductsStore and an adapted categories box (to use the CountProductsStore):

 

index.php: 43, cPath=1: 43, product_info.php?products_id=20: 42:

 

The adapted includes/classes/CountProductsStore.php (adapted from the optimize categories box contribution):

 

<?php
/* $Id: CountProductsStore.php v 1.2 2010/06/23 JanZ
  an object to store the number of products in a category and which category_id has which parent_id

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

  Copyright (c) 2006 osCommerce

 Released under the GNU General Public License
*/

class CountProductsStore {
 var $prods_in_category, $category_tree, $category_name_tree;

function CountProductsStore() {
   if (SHOW_COUNTS == 'true') {
     if (USE_PRODUCTS_COUNT_CACHE == 'true') {
       $this->prods_in_category = tep_cache_products_count();
     } else {
       $category_query = tep_db_query("select categories_id from categories");
         while ($_categories = tep_db_fetch_array($category_query)) {
         $categories[] = $_categories['categories_id'];
         }

         $products_query = tep_db_query("select count(*) as number_in_category, categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id in (" . implode(",", $categories) . ") group by categories_id");
         while ($_prods_in_category = tep_db_fetch_array($products_query)) {
         $this->prods_in_category[$_prods_in_category['categories_id']] = $_prods_in_category['number_in_category'];
         }
       }
     }
   $category_trees_array = $this->buildCategoryTree();
   $this->category_tree = $category_trees_array['parent_tree'];
   $this->category_name_tree = $category_trees_array['name_tree'];
}

 function CountProductsInCategory($category_id) {
   if (isset($this->prods_in_category[$category_id])) {
     return $this->prods_in_category[$category_id];
   } else {
     return 0;
   }
 }

 function hasChildCategories($category_id) {
   foreach ($this->category_tree as $categories_id => $parent_id) {
     if ($parent_id == $category_id) {
       $ChildCategories[] = $categories_id;
     }
   } // end foreach ($this->category_tree as $categories_id => $parent_id)
   if (isset($ChildCategories)) {
     return $ChildCategories;
   } else {
     return false;
   }
 }

 function buildCategoryTree() {
   $languages_id = '';
   if (!isset($_SESSION['languages_id']) || $_SESSION['languages_id'] < 1) { 
     $languages_id = 1; 
   } else {
     $languages_id = $_SESSION['languages_id'];
   }
   $category_query = tep_db_query("select c.categories_id, c.parent_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

   while ($categories = tep_db_fetch_array($category_query)) {
    $category_tree_array[$categories['categories_id']] = $categories['parent_id'];
    $category_name_tree_array[$categories['categories_id']] = $categories['categories_name'];
   } 
   $categories_trees_array = array('parent_tree' => $category_tree_array, 'name_tree' => $category_name_tree_array);
   return $categories_trees_array;
 } 

 function getParentCategory($category_id) {
   foreach ($this->category_tree as $categories_id => $parent_id) {
     if ($categories_id == $category_id) {
       return $parent_id;
     }
   } // end foreach ($this->category_tree as $categories_id => $parent_id)
     return false;
   }

 function countChildCategories($category_id) {
   $count = 0;
   foreach ($this->category_tree as $categories_id => $parent_id) {
     if ($parent_id == $category_id) {
       $count += 1;
     }
   } // end foreach ($this->category_tree as $categories_id => $parent_id)
     return $count;
 }

 function getChildCategories($category_id) {
   foreach ($this->category_tree as $categories_id => $parent_id) {
     if ($parent_id == $category_id) {
       $child_category[] = array('categories_id' => $categories_id, 'categories_name' => $this->category_name_tree[$categories_id]);
     }
   } // end foreach ($this->category_tree as $categories_id => $parent_id)
     return $child_category;
 }
}
?>

 

The adapted categories enhanced box:

 

<?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

 changed August 2003, by Nils Petersson
 contribution "Category Box Enhancement", version 1.1
 version with enhanced CountProductsStore object

*/

 function tep_show_category($counter) {

// BoF - Contribution Category Box Enhancement 1.1
   global $tree, $categories_string, $cPath_array, $cat_name;

   for ($i=0; $i<$tree[$counter]['level']; $i++) {
     $categories_string .= "  ";
   }
   $cPath_new = 'cPath=' . $tree[$counter]['path'];
   if (isset($cPath_array) && in_array($counter, $cPath_array) && $cat_name == $tree[$counter]['name']) { //Link nicht anklickbar, wenn angewählt
            $categories_string .= '<a href="';
            $categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">';																	 	 //Link nicht anklickbar, wenn angewählt
   } else {						 																					 //Link nicht anklickbar, wenn angewählt
   $categories_string .= '<a href="';
   $categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">';
   }									 																						 //Link nicht anklickbar, wenn angewählt
   if (tep_has_category_subcategories($counter)) {
     $categories_string .= tep_image(DIR_WS_IMAGES . 'pointer_blue.gif', '');
   }
   else {
     $categories_string .= tep_image(DIR_WS_IMAGES . 'pointer_blue_light.gif', '');
   }

   if (isset($cPath_array) && in_array($counter, $cPath_array)) {
     $categories_string .= '<b>';
   }

   if ($cat_name == $tree[$counter]['name']) {
     $categories_string .= '<span class="errorText">';
   }

// display category name
   $categories_string .= $tree[$counter]['name'];

	if ($cat_name == $tree[$counter]['name']) {
		$categories_string .= '</span>';
   }

   if (isset($cPath_array) && in_array($counter, $cPath_array)) {
     $categories_string .= '</b>';
   }
// 	EoF Category Box Enhancement

   $categories_string .= '</a>';

   if (SHOW_COUNTS == 'true') {
     $products_in_category = tep_count_products_in_category($counter);
     if ($products_in_category > 0) {
       $categories_string .= ' (' . $products_in_category . ')';
     }
   }

   $categories_string .= '<br>';

   if ($tree[$counter]['next_id'] != false) {
     tep_show_category($tree[$counter]['next_id']);
   }
 }
?>
<!-- categories //-->
         <tr>
           <td>
<?php

// BoF - Contribution Category Box Enhancement 1.1
if (isset($cPath_array)) {
   $cat_name = '';
	for ($i=0, $n=sizeof($cPath_array); $i<$n; $i++) {		  
	  if (isset($countproducts->category_name_tree[(int)$cPath_array[$i]])) {
	    $cat_name = $countproducts->category_name_tree[(int)$cPath_array[$i]];
	  }
	}
$cat_name = $categories['categories_name'];
}
// EoF Category Box Enhancement
// display category name

 $info_box_contents = array();
 $info_box_contents[] = array('text' => BOX_HEADING_CATEGORIES);

 new infoBoxHeading($info_box_contents, true, false);

 $categories_string = '';
 $tree = array();

 $ChildCategories = $countproducts->getChildCategories('0');
 if (count($ChildCategories) > 0) {
   foreach ($ChildCategories as $chca_key => $id_name_array) {
     $tree[$id_name_array['categories_id']] = array('name' => $id_name_array['categories_name'],
                                               'parent' => '0',
                                               'level' => 0,
                                               'path' => $id_name_array['categories_id'],
                                               'next_id' => false);

     if (isset($parent_id)) {
       $tree[$parent_id]['next_id'] = $id_name_array['categories_id'];
     }

     $parent_id = $id_name_array['categories_id'];

     if (!isset($first_element)) {
       $first_element = $id_name_array['categories_id'];
     }
   }
 } // end if (count($ChildCategories) > 0)

 //------------------------
 if (tep_not_null($cPath)) {
   $new_path = '';
   reset($cPath_array);
   while (list($key, $value) = each($cPath_array)) {
     unset($parent_id);
     unset($first_id);
     $ChildCategories = $countproducts->getChildCategories((int)$value);
     if (count($ChildCategories) > 0) {
       $new_path .= $value;
       foreach ($ChildCategories as $chca_key => $id_name_array) {
         $tree[$id_name_array['categories_id']] = array('name' => $id_name_array['categories_name'],
                                              'parent' => (int)$value,
                                              'level' => $key+1,
                                              'path' => $new_path . '_' . $id_name_array['categories_id'],
                                              'next_id' => false);

         if (isset($parent_id)) {
           $tree[$parent_id]['next_id'] = $id_name_array['categories_id'];
         }

         $parent_id = $id_name_array['categories_id'];

         if (!isset($first_id)) {
           $first_id = $id_name_array['categories_id'];
         }

         $last_id = $id_name_array['categories_id'];
       }
       $tree[$last_id]['next_id'] = $tree[$value]['next_id'];
       $tree[$value]['next_id'] = $first_id;
       $new_path .= '_';
     } else {
       break;
     }
   } // end while (list($key, $value) = each($cPath_array))
 }
 tep_show_category($first_element);

 $info_box_contents = array();
 $info_box_contents[] = array('text' => $categories_string);

if ( (basename($PHP_SELF) != FILENAME_SPECIALS)) {
$info_box_contents[] = array('align' => 'left',
                              'text'  => '<font size=-2><b><a href="' . tep_href_link(FILENAME_SPECIALS, '', 'NONSSL') . '">' . BOX_INFORMATION_SPECIALS . '</a></b></font>');
}else{
$info_box_contents[] = array('align' => 'left',
                              'text'  => '<font size=-2><b><a href="' . tep_href_link(FILENAME_SPECIALS, '', 'NONSSL') . '"><span class="errorText">' . BOX_INFORMATION_SPECIALS . '</a></b></font></span>');
 }
if ( (basename($PHP_SELF) != FILENAME_PRODUCTS_NEW)) {
$info_box_contents[] = array('align' => 'left',
                              'text'  => '<font size=-2><b><a href="' . tep_href_link(FILENAME_PRODUCTS_NEW, '', 'NONSSL') . '">' . BOX_INFORMATION_PRODUCTS_NEW . '</a></b></font>');
}else{
$info_box_contents[] = array('align' => 'left',
                              'text'  => '<font size=-2><b><a href="' . tep_href_link(FILENAME_PRODUCTS_NEW, '', 'NONSSL') . '"><span class="errorText">' . BOX_INFORMATION_PRODUCTS_NEW . '</a></b></font></span>');
 }


 function tep_get_paths($categories_array = '', $parent_id = '0', $indent = '', $path='') {
   global $languages_id, $countproducts;

   if (!is_array($categories_array)) $categories_array = array();

   $ChildCategories = $countproducts->getChildCategories((int)$parent_id);
   foreach ($ChildCategories as $chca_key => $id_name_array) {
     if ($parent_id=='0'){
$categories_array[] = array('id' => $id_name_array['categories_id'],
                                     'text' => $indent . $id_name_array['categories_name']);
     }
     else{
$categories_array[] = array('id' => $path . $parent_id . '_' .$id_name_array['categories_id'],
       	                          'text' => $indent . $id_name_array['categories_name']);
     }

     if ($id_name_array['categories_id'] != $parent_id) {
$this_path=$path;
if ($parent_id != '0')
  $this_path = $path . $parent_id . '_';
       $categories_array = tep_get_paths($categories_array, $id_name_array['categories_id'], $indent . ' ', $this_path);
     }
   }

   return $categories_array;
 }
 $info_box_contents[] = array('form' => '<form action="' . tep_href_link(FILENAME_DEFAULT) . '" method="get">' . tep_hide_session_id(),
                              'align' => 'left',
                              'text'  => '<b>' . BOX_DROPDOWN_MENUS . '<br>' . tep_draw_pull_down_menu('cPath', tep_get_paths(array(array('id' => '', 'text' => PULL_DOWN_DEFAULT))), $cPath, 'onchange="this.form.submit();" style="width: 100%"')
                            );
 new infoBox($info_box_contents);
?>

           </td>
         </tr>
<!-- categories_eof //-->

Link to comment
Share on other sites

  • Replies 905
  • Created
  • Last Reply

Wow, Jan, your change brings a great enhancement ! Thanks in advance!

With activated dropdown menu (former numbers where with inactive dropdown) I had 424 queries first,

after the change it dropped to 235 !

 

But somewhere there seems to be some error, because I'm getting an error message:

Warning: Invalid argument supplied for foreach() in /srv/www/includes/boxes/categories.php on line 186

 

In line 185-186 I have found this code:

   $ChildCategories = $countproducts->getChildCategories((int)$parent_id);
   foreach ($ChildCategories as $chca_key => $id_name_array) {

 

This link here telling me, that an array is expected.

So, according to the link, I changed the code to

   $ChildCategories = $countproducts->getChildCategories((int)$parent_id);
   if(empty($chca_key)) {
return false;
}
   foreach ($ChildCategories as $chca_key => $id_name_array) {

and the error message is gone.

But then, the dropdown menu is empty :blink:

So, something seems to be wrong.

 

The select code from languages queries should be easy to solve (it that hasn't been done already in newer versions).

The select code from languages comes from my self-enhanced version of UltimateSEO.

I have to solve this later, now I had it deactivated completely.

 

The select c.categories..... where parent_id = 'xx' queries are typical for the drop-down in the categories box of the category box enhanced contribution.

 

But you wouldn't see those if you commented out the inclusion of the categories box (unless you use an old version of the STS template contribution where the drop-down queries were executed even if you didn't use the drop-down).

That is strange. I have never used any STS template, but as I said before, I saw this queries also with the cat box commented out !

I already made a comparison between my index.php and the vanilla-RC2a-index.php.

Of course, I have changes in my version (got additionally HeaderTags, UltraPics and NewsDesk included), but the code is not so bloated up and no changes in the core cat-queries, which are included in the index.php.

Any other suggestions?

Link to comment
Share on other sites

That is strange. I have never used any STS template, but as I said before, I saw this queries also with the cat box commented out !

Got the sucker !! Woohooo !!

 

It is not my index.php, that causing that query-trouble, but my header.php :angry: !!

Thanks for clearing my mind with your mentioning of STS or other drop-down queries.

 

I'm having a modified header as well with an included search within.

There is also a dropdown menu with all cats in it.

Sorry, sometimes you are not getting the point and not see the real problem still existing. Never thought of the header.php before, really sorry.

 

But is it possible to "optimize" that dropdown as well ?

The code in the header is that:

<tr>
<td class="infoBoxContents" align="middle" valign="middle" colspan="3"><?php echo BOX_ADVSEARCH_SEARCH ?></td>
<td class="infoBoxContents" valign="middle" colspan="3"><?php echo tep_draw_hidden_field('search_in_description','1') . tep_draw_input_field('keywords', '', 'size="10" maxlength="30" style="width: ' . (BOX_WIDTH+100) . 'px"')  ?></td>
<td class="infoBoxContents" valign="top" colspan="3"> </td>
<td class="infoBoxContents" valign="middle" colspan="3"><?php echo BOX_ADVSEARCH_CAT ?></td>
<td class="infoBoxContents" valign="middle" colspan="3"><?php echo tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('id' => '', 'text' => BOX_ADVSEARCH_ALLCAT)))) ?></td>
<td class="infoBoxContents" valign="top" colspan="3"> </td>
<td class="infoBoxContents" valign="middle" colspan="3"><center><?php echo tep_image_submit('button_search.gif', BOX_HEADING_ADVSEARCH); ?></center></td>
</tr> 

 

so especially this call is causing my trouble:

tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('id' => '', 'text' => BOX_ADVSEARCH_ALLCAT))))

Link to comment
Share on other sites

But somewhere there seems to be some error, because I'm getting an error message:

Warning: Invalid argument supplied for foreach() in /srv/www/includes/boxes/categories.php on line 186

 

In line 185-186 I have found this code:

   $ChildCategories = $countproducts->getChildCategories((int)$parent_id);
   foreach ($ChildCategories as $chca_key => $id_name_array) {

Ah, I didn't get that error so I thought it would be OK to leave it like that. It was way past my bedtime too :)

 

Just wrap it around an if statement that looks if there actually is an array:

 


 function tep_get_paths($categories_array = '', $parent_id = '0', $indent = '', $path='') {
   global $languages_id, $countproducts;

   if (!is_array($categories_array)) $categories_array = array();

   $ChildCategories = $countproducts->getChildCategories((int)$parent_id);
   if (count($ChildCategories) > 0) {
     foreach ($ChildCategories as $chca_key => $id_name_array) {
       if ($parent_id=='0'){
  $categories_array[] = array('id' => $id_name_array['categories_id'],
                                     'text' => $indent . $id_name_array['categories_name']);
       }
       else{
  $categories_array[] = array('id' => $path . $parent_id . '_' .$id_name_array['categories_id'],
       	                          'text' => $indent . $id_name_array['categories_name']);
       }

       if ($id_name_array['categories_id'] != $parent_id) {
  $this_path=$path;
  if ($parent_id != '0')
    $this_path = $path . $parent_id . '_';
         $categories_array = tep_get_paths($categories_array, $id_name_array['categories_id'], $indent . ' ', $this_path);
       }
     } // end foreach ($ChildCategories as $chca_key => $id_name_array)
   } // end if (count($ChildCategories) > 0)

   return $categories_array;
 }

Link to comment
Share on other sites

But is it possible to "optimize" that dropdown as well ?

Yes, actually the code for the function tep_get_categories is very much alike the code for tep_get_paths as in categories box enhanced. This is a version that will do the queries if $countproducts isn't an object but uses the object when available (zero queries!).

 

 function tep_get_categories($categories_array = '', $parent_id = '0', $indent = '') {
   global $languages_id, $countproducts;

   if (!is_array($categories_array)) $categories_array = array();

   if (isset($countproducts) && is_object($countproducts)) {
   $ChildCategories = $countproducts->getChildCategories((int)$parent_id);
     if (count($ChildCategories) > 0) {
       foreach ($ChildCategories as $chca_key => $id_name_array) {
         $categories_array[] = array('id' => $id_name_array['categories_id'],
                                     'text' => $indent . $id_name_array['categories_name']);

         if ($id_name_array['categories_id'] != $parent_id) {
           $categories_array = tep_get_categories($categories_array, $id_name_array['categories_id'], $indent . '  ');
         }
       } // end foreach ($ChildCategories as $chca_key => $id_name_array) 
     } // end if (count($ChildCategories) > 0)
   } else {
     $categories_query = tep_db_query("select c.categories_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where parent_id = '" . (int)$parent_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");
     while ($categories = tep_db_fetch_array($categories_query)) {
       $categories_array[] = array('id' => $categories['categories_id'],
                                   'text' => $indent . $categories['categories_name']);

       if ($categories['categories_id'] != $parent_id) {
         $categories_array = tep_get_categories($categories_array, $categories['categories_id'], $indent . '  ');
       }
     }
   }

   return $categories_array;
 }

 

Note that with the changed version of CountProductsStore.php you are now using caching of the object is probably not an option. Haven't looked at that yet.

 

Due to using KissER Error Handling & Debugging instead of the Queries Debug contribution another little flaw was found. The function getChildCategories in CountProductsStore.php needs a little change:

 

 function getChildCategories($category_id) {
   $child_category = array(); // added this line
   foreach ($this->category_tree as $categories_id => $parent_id) {
     if ($parent_id == $category_id) {
       $child_category[] = array('categories_id' => $categories_id, 'categories_name' => $this->category_name_tree[$categories_id]);
     }
   } // end foreach ($this->category_tree as $categories_id => $parent_id)
     return $child_category;
 } // end function getChildCategories($category_id)

 

Another note. I found that changing the language would not result in an immediate change in the language of the menu and drop-downs. That would happen on the next click. This is because the language is set after countproducts is instantiated.

Moving that down a few lines helped:

 


// include the language translations
 require(DIR_WS_LANGUAGES . $language . '.php');

// include CountProductsStore object
 require(DIR_WS_CLASSES . 'CountProductsStore.php');
 $countproducts = new CountProductsStore();

// currency

Link to comment
Share on other sites

Ah, I didn't get that error so I thought it would be OK to leave it like that. It was way past my bedtime too :)

 

Just wrap it around an if statement that looks if there actually is an array:

.........

Jan, thanks you so much for your support, works great!

 

 

Another note. I found that changing the language would not result in an immediate change in the language of the menu and drop-downs. That would happen on the next click. This is because the language is set after countproducts is instantiated.

Moving that down a few lines helped:

Cool! But also a sidenote, first I moved up the language-include by error, which did not help.

Moving down as you said has done the trick.

 

 

Due to using KissER Error Handling & Debugging instead of the Queries Debug contribution another little flaw was found. The function getChildCategories in CountProductsStore.php needs a little change:

Is this code-change only required by KissER Error .... contrib?

Is the change also useful, if only the Queries debug contrib is applied?

 

 

Now to the corrected queries within my header:

your code-change in function tep_get_categories...

Note that with the changed version of CountProductsStore.php you are now using caching of the object is probably not an option. Haven't looked at that yet.

Worked also great with no additional queries, thanks again.

But can you explain that a little bit more, please? Just for understanding, what that mean in detail.

Link to comment
Share on other sites

Is this code-change only required by KissER Error .... contrib?

Is the change also useful, if only the Queries debug contrib is applied?

No, I used that instead of Queries debug. A bit simpler to install and it also keeps track of errors (some of which may go unnoticed as the one in that function, it was a E_NOTICE error but still better to solve it). I wouldn't add it now that you have Queries debug installed.

 

Now to the corrected queries within my header:

 

Worked also great with no additional queries, thanks again.

But can you explain that a little bit more, please? Just for understanding, what that mean in detail.

 

In the countproducts the whole category setup for a particular language is stored, in the sort order that comes from the database. With some simple functions this object is used to see if a category has child categories or which parent category it has instead of querying the database for that information.

 

With 20 categories the number of queries is small enough to not really be noticed but with 200 queries (or with a slow server) those queries add up in time.

 

So everything is now done in PHP instead of PHP going to the MySQL database for a query and get the results, process them etc., then again, and again and again etc.

Link to comment
Share on other sites

Alright, thanks.

And what did you meant with that?

Note that with the changed version of CountProductsStore.php you are now using caching of the object is probably not an option. Haven't looked at that yet.

Any disadvantages in caching? Although I would like to get away from caching anyway...

Link to comment
Share on other sites

Any disadvantages in caching? Although I would like to get away from caching anyway...

Sorry, I'm mistaken. Have looked at it a little better but the CountProductsStore.php file only can cache the products count (that you switched off). The additional functions that were added in the CountProductsStore.php posted above are not affected by that.

 

This particular cache is independent of all the other (three I believe) caches. The original caching functions of osC are not bad or detrimental although with 200 categories you probably get a lot of cache files...

Caching doesn't always help. E.g. a new visitor that gets the osCsid appended to links doesn't benefit from the cached categories of osC.

Link to comment
Share on other sites

Ah, ok.

But now, I think, I have some strange settings set in my admin >_<

Normal osC cache function is set to off.

Page cache function from Chemo is set to off.

Category count function is set to off

BUT category count cache is set to on

:lol:

 

But anyway, I don't think, that should be a matter.

 

Jan, thank you so much for your patience and support !!

What a pity, that there is no "thank you"-function in this forum.

Thanks again.

Link to comment
Share on other sites

Another note. I found that changing the language would not result in an immediate change in the language of the menu and drop-downs. That would happen on the next click. This is because the language is set after countproducts is instantiated.

Moving that down a few lines helped:

 


// include the language translations
 require(DIR_WS_LANGUAGES . $language . '.php');

// include CountProductsStore object
 require(DIR_WS_CLASSES . 'CountProductsStore.php');
 $countproducts = new CountProductsStore();

// currency

Ah, Jan, sorry for coming up again, but maybe this is not finally solved, because I got feedback from visitors, that the site itself shows content in german, but the categories in english.

Caching is disabled, all old cache files are deleted (if this might be a problem).

The change you mentioned is done by moving the count-require down under the language-require.

 

May I send you the beginning of my application_top per PM for a quick review ?

 

Thanks in advance,

Regards

Andreas

Link to comment
Share on other sites

  • 1 month later...
  • 2 months later...

Hi all,

 

I'm having a play around with the multi stores contribution and have a problem query when searching which is taking between 5 and 7 seconds on a dedicated server.

 

I was wondering if anyone had a few hours spare and fancied a challenge ;)

 

any thoughts on this one...

 

            [19] => select distinct p.products_image, m.manufacturers_name,  m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id and s.stores_id = '1' INNER JOIN products_to_stores p2s  on p.products_id = s.products_id left join products_attributes pa on p.products_id = pa.products_id left join products_options po on pa.options_id = po.products_options_id left join products_options_values pov on pa.options_values_id = pov.products_options_values_id, products_description pd, categories c, products_to_categories p2c where p2s.stores_id = '1' and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id  and ((pd.products_name like '%baller%' or p.products_model like '%search term%' or m.manufacturers_name like '%search term%' or po.products_options_name like '%search term%' or pov.products_options_values_name like '%search term%') ) order by pd.products_name limit 0, 30

 

Cheers

 

Dave

Link to comment
Share on other sites

There was code here somewhere either in this thread or somewhere else. If anyone can help I would appreciate it.

 

The code, I think was for, storing the configuration and/or settings in a file every so often by retrieving it from the database thus helping the page loads overall.

Link to comment
Share on other sites

There was code here somewhere either in this thread or somewhere else. If anyone can help I would appreciate it.

 

The code, I think was for, storing the configuration and/or settings in a file every so often by retrieving it from the database thus helping the page loads overall.

 

 

Nevermind found it: http://www.oscommerce.com/community/contributions,1862

Link to comment
Share on other sites

  • 2 weeks later...

Hi

I have made these changes and it has made an amazing difference in queries and speed. The slowest part of my site now is the stylesheet loading. Is there a way to cache the stylesheet? Or is that just taking things too far?

Link to comment
Share on other sites

  • 1 month later...

Hello,

 

I ran the debugger and the following query takes 5 to 7 seconds to load.

 

 

[112] => select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit 10

 

Can this be further optimized?

 

Any help would be appreciated.

Link to comment
Share on other sites

The slowest part of my site now is the stylesheet loading. Is there a way to cache the stylesheet? Or is that just taking things too far?

The browser of the client is caching the stylesheet. Since it is a static file there is nothing to cache server side.

Link to comment
Share on other sites

I ran the debugger and the following query takes 5 to 7 seconds to load.

 

 

[112] => select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit 10

 

Can this be further optimized?

If you can find where this query comes from you could try rewriting it to only use the table products:

 

select distinct p.products_id from products p where p.products_status = '1' and p.products_ordered > 0 order by p.products_ordered desc limit 10

Then retrieve the products_id's and query for the products_name from the table products_description. Then combine the results in an array and use that for the module/box where this code is coming from. Since the tables to work with are now smaller (not the combination of products and products_description) that should be faster.

Link to comment
Share on other sites

Hi

I have made these changes and it has made an amazing difference in queries and speed. The slowest part of my site now is the stylesheet loading. Is there a way to cache the stylesheet? Or is that just taking things too far?

 

There are a few ways to easily accomplish this (compress and sent proper headers)...

 

This is the easiest for most people:

 

### add this to your site root htaccess

AddHandler application/x-httpd-php .css .js

 

### Use this instead (below) if needed - PHP5 as default

### AddHandler application/x-httpd-php5 .css .js

 

### end of htaccess edit

 

Then place this at the very TOP of your stylesheet (EVERY stylesheet on your site and admin)

 

<?php

ob_start ("ob_gzhandler");

header("Content-type: text/css");

header("Cache-Control: must-revalidate");

$offset = 60 * 60 * 24 * 3;

$ExpStr = "Expires: " . gmdate("D, d M Y H:i:s", time() + $offset) . " GMT";

Header($ExpStr);

?>

 

 

 

Place this rule at the top, in EVERY javascript file

<?php

ob_start('ob_gzhandler');

header("Content-type: text/javascript");

header('Cache-Control: must-revalidate');

header('Expires: '.gmdate('D, d M Y 23:59:59', strtotime('+1 week')).' GMT');

ob_start('compress');

function compress($buffer){

$buffer = preg_replace("/((?:\/\*(?:[^*]|(?:\*+[^*\/]))*\*+\/)|(?:\/\/.*))/", "", $buffer);

$buffer = str_replace(array("\r\r\r","\r\r","\r\n","\n\r","\n\n\n","\n\n"),"\n",$buffer);

return $buffer;}

?>

 

 

NOTE: If your javascript is not formated correctly, you can remove this part of the script that you placed at the top of your javascript files to (used only to further compress):

 

ob_start('compress');

function compress($buffer){

$buffer = preg_replace("/((?:\/\*(?:[^*]|(?:\*+[^*\/]))*\*+\/)|(?:\/\/.*))/", "", $buffer);

$buffer = str_replace(array("\r\r\r","\r\r","\r\n","\n\r","\n\n\n","\n\n"),"\n",$buffer);

return $buffer;}

 

You would then only have this at the top of your javascript files:

 

<?php

ob_start('ob_gzhandler');

header("Content-type: text/javascript");

header('Cache-Control: must-revalidate');

header('Expires: '.gmdate('D, d M Y 23:59:59', strtotime('+1 week')).' GMT');

?>

 

If you only wish to compress your stylesheets.... then remove the .js from the addhandler and only mod your stylesheets.

### add this to your site root htaccess

AddHandler application/x-httpd-php .css

 

 

This will server up compressed files to every browser that supports such. You should see your files are a fraction of the size.

To check that you are serving compressed files, check your site here: http://www.websiteoptimization.com/services/analyze/

and view the results.

 

Kind regards,

Debs

Link to comment
Share on other sites

What I have in my htacess is this

 

 

# BEGIN Expire headers

 

ExpiresActive On

ExpiresDefault "access plus 1 seconds"

ExpiresByType image/jpeg "access plus 2592000 seconds"

ExpiresByType image/png "access plus 2592000 seconds"

ExpiresByType image/gif "access plus 2592000 seconds"

ExpiresByType image/x-icon "access plus 2592000 seconds"

ExpiresByType text/css "access plus 604800 seconds"

ExpiresByType text/javascript "access plus 604800 seconds"

ExpiresByType application/javascript "access plus 604800 seconds"

ExpiresByType text/html "access plus 2592000 seconds"

 

# END Expire headers

 

Would I need to also add the last post?

Link to comment
Share on other sites

What I have in my htacess is this

 

 

# BEGIN Expire headers

 

ExpiresActive On

ExpiresDefault "access plus 1 seconds"

ExpiresByType image/jpeg "access plus 2592000 seconds"

ExpiresByType image/png "access plus 2592000 seconds"

ExpiresByType image/gif "access plus 2592000 seconds"

ExpiresByType image/x-icon "access plus 2592000 seconds"

ExpiresByType text/css "access plus 604800 seconds"

ExpiresByType text/javascript "access plus 604800 seconds"

ExpiresByType application/javascript "access plus 604800 seconds"

ExpiresByType text/html "access plus 2592000 seconds"

 

# END Expire headers

 

Would I need to also add the last post?

 

The last post (by me) would also compress your files. GZip compression. This means you can drastically reduce the size of all those bloated css and javascript files. No you can not use your .js and .css expire headers at the same time, as they are included in the code, if used in the post above. It is not needed unless you are trying to reduce your page size and speed.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...