A Store Speed Optimization in Progress

October 3, 2007

I am trying to install:

Optimize tep_get_tax_rate() method

I did the on application_top.php edit already.

For some reason, when I go to test my shop I am consistently getting this error after I add an item to my cart (viewing the products page is fine, but I also use Chemo's page cache.. so there might actually be errors but I can't see them just yet?):

Fatal error: Call to a member function on a non-object in /home/**/public_html/includes/functions/general.php on line 340

( ^that shows up on shopping_cart.php)

On line 340:

return $osC_Tax->getTaxRate($class_id, $country_id, $zone_id);

powerdrive · October 8, 2007

I know Chemo mentioned it on the first page but I didn't see how it got resolved. Wondering if anyone can help.

			[17] => select products_name from products_description where products_id = '59' and language_id = '1'
		[18] => select products_name from products_description where products_id = '121' and language_id = '1'
		[19] => select products_name from products_description where products_id = '143' and language_id = '1'
		[20] => select products_name from products_description where products_id = '208' and language_id = '1'
		[21] => select products_name from products_description where products_id = '200' and language_id = '1'
		[22] => select products_name from products_description where products_id = '51' and language_id = '1'
		[23] => select products_name from products_description where products_id = '101' and language_id = '1'
		[24] => select products_name from products_description where products_id = '93' and language_id = '1'
		[25] => select products_name from products_description where products_id = '79' and language_id = '1'

So how do I optimise this lot?

Jan Zonjee · October 8, 2007

So how do I optimise this lot?

Probably comes from includes/modules/new_products.php. Could be featured products too. In case of the former:

Line 20-24

**REPLACE**

 if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
 } else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
 }

**WITH**

 if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
  $new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id where products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
  $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

Line 30

**REPLACE**

$new_products['products_name'] = tep_get_products_name($new_products['products_id']);

**WITH** (so comment out)

// $new_products['products_name'] = tep_get_products_name($new_products['products_id']);

powerdrive · October 8, 2007

That seems to have been done when I installed Featured Products.

This is the section in modules/featured_products.php that I think needs changing, and the bits that I think I need to change are I highlighted.

I don't actually know what to change to, any help would be great.

$featured_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where p.products_status = '1' and f.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);
} else {

$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_FEATURED_PRODUCTS_CATEGORY, $cat_name));

$subcategories_array = array();

tep_get_subcategories($subcategories_array, $featured_products_category_id);

$featured_products_category_id_list = tep_array_values_to_string($subcategories_array);

if ($featured_products_category_id_list == '') {

$featured_products_category_id_list .= $featured_products_category_id;

} else {

$featured_products_category_id_list .= ',' . $featured_products_category_id;

}

$featured_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_CATEGORIES . " c using(categories_id) left join " . TABLE_FEATURED . " f on p.products_id = f.products_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where c.parent_id = '" . $featured_products_category_id . "' and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);

Jan Zonjee · October 8, 2007

That seems to have been done when I installed Featured Products.

This is the section in modules/featured_products.php that I think needs changing, and the bits that I think I need to change are I highlighted.

I don't actually know what to change to, any help would be great.

Actually, I think it is not a good idea to add the table products_description into this multi table join. I'm afraid it will slow down things quite a bit on a site with a lot of products.

I have another suggestion. Download version 4.2.0 of Separate Pricing Per Customer and you will find in catalog/includes/modules/ a file name featured.php.

If you remove some code that is for SPPC alone it should work.

Remove line 33 to 38:

// BOF Separate Pricing Per Customer
 if (isset($_SESSION['sppc_customer_group_id']) && $_SESSION['sppc_customer_group_id'] != '0') {
 $customer_group_id = $_SESSION['sppc_customer_group_id'];
 } else {
  $customer_group_id = '0';
 }

and 81-99 (line numbers before you remove line 33-38):

// get all customers_group_prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
 if ($customer_group_id != '0') {
$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where products_id in (" . implode(',', $list_of_prdct_ids) . ") and pg.customers_group_id = '" . $customer_group_id . "'");
  while ($pg_array = tep_db_fetch_array($pg_query)) {
  $new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '');
}

for ($x = 0; $x < $no_of_featured_prdcts; $x++) {
// replace products prices with those from customers_group table
  if (!empty($new_prices)) {
	  for ($i = 0; $i < count($new_prices); $i++) {
		  if ($featured_products[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
			$featured_products[$x]['products_price'] = $new_prices[$i]['products_price'];
		  }
	  }
	} // end if(!empty($new_prices)
} // end for ($x = 0; $x < $no_of_featured_prdcts; $x++)
 } // end if ($customer_group_id != '0')

Then last, change the $specials_query on line 102 to remove the customer_group references to:

	$specials_query = tep_db_query("select products_id, specials_new_products_price, status as specstat from specials where products_id in (" . implode(',', $list_of_prdct_ids) . ") and status = '1' ");

asapcustoms · October 12, 2007

I have added the tax hack, caching on many modules and a few others in this thread after a week of reading it and rewriting the site. We are running even slower than before.

MY Issue:

We have been told by our host to shut our OS system down after over 2 years and 7500 hours of work due to server load over 80%.

Does anyone know why the following errors happen, this is what the host keeps sending but not sure where to start to fix it?

Any help would be great for this stressed team over here.

with mysql high load mysql and whole system:

root@server [/]# uptime

17:45:59 up 22 min, 0 users, load average: 11.57, 9.09, 5.50

root@server [/]# mysqladmin processlist

+-----+---------------+-----------+---------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

+-----+---------------+-----------+---------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

+-----+---------------+-----------+---------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

root@server [/]#

-------------------

Thank you for any info you may provide.

Jan Zonjee · October 12, 2007

I have added the tax hack, caching on many modules and a few others in this thread after a week of reading it and rewriting the site. We are running even slower than before.

MY Issue:

We have been told by our host to shut our OS system down after over 2 years and 7500 hours of work due to server load over 80%.

Does anyone know why the following errors happen, this is what the host keeps sending but not sure where to start to fix it?

A. You are advertising in your signature, that is not allowed on the osC forums.

B. You posted almost exactly the same (you added some text above it) information earlier, that is called cross-posting and is not allowed either.

C. If you spent a week reading this thread you as a professional osC programmer should have recognized that the discussion on the table specials slowing down a large store for which you can add an index on products_id in the table specials would have been a valuable suggestion. If that doesn't help you could try leaving out the table specials in that query (looks like the module/product_listing.php) and querying for specials, adding them in later might be another way. Separate Pricing Per Customer does that (but for another reason).

D. Of course you could leave out the update query here if you don't care about that information:

update products_description set products_viewed = products_viewed+1 where products_id = '7449'

E. You might need a MySQL expert to repair tables or re-index or something.

asapcustoms · October 12, 2007

Jan thanks for any tips. I removed my signature so I would not seem like I am advertising. I alsoposted in this thread because no tips were coming in on the other thread and it felt like this is where it belonged.

Honest mistake...thanks again

outlawtraders · October 13, 2007

Hi,

I'm pretty new to all this, and have looked for an answer in this forum topic but not found anything (well there are answers for STS users), but I'm not using a template. Except define mainpage, does that count?

So far i have this on my index page:

Current Parse Time: 0.494 s with 55 queries

I know my store is quite heavily modified but I'm still getting about 15-20 queries all looking like this:

[11] => select c.categories_id, cd.categories_name from categories c, categories_description cd where parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id = '1' order by sort_order, cd.categories_name

So what can i do about them - does anyone know where they're coming from?

Many Regards

Paula

♥stubbsy · October 14, 2007

Hi,

I wondered if anyone had any advice they could give me with regards the performance of the attributes page in admin? Due to the type of products I sell we have some 10,000 different attributes. The problem I have is that with this number of attributes the attributes page in the admin takes around a minute to load after any update, making it unusable so I am currently using ajax attributes manager to assign attributes to products. Although this works to a fashion, it has it's flaws and I really need to be able to use the out of the box attributes page to do some things.

I wondered has anyone else experienced performance issues with large amounts of attributes and if so what you did to over come them?

Dave

Jan Zonjee · October 14, 2007

I'm pretty new to all this, and have looked for an answer in this forum topic but not found anything

Sure? This was just two pages back...

Jan Zonjee · October 14, 2007

I wondered if anyone had any advice they could give me with regards the performance of the attributes page in admin?

Not really advice or ideas, but you can add Chemo's debug contribution to the admin relatively easy (the install instructions only mention the catalog side, but it is not hard to imagine how to add it to the admin side). In that case you can at least find out what is/are the slow query/queries.

outlawtraders · October 14, 2007

Sure? This was just two pages back...

Hi again,

yes, i did see that & installed the optimise category box contrib ... it reduced my queries but left the ones mentioned above. Haven't installed the 'tep_get_category_tree' yet. Will try that next & let you know.

thanks

outlawtraders · October 14, 2007

Well - i added the tep_get_category_tree contrib & it didn't make any difference.

I double checked the optimise Category box contrib (just in case) & i'm pretty sure that's ok

the only thing i didn't do was to add the -Additional optimization information, to save a number of queries for products_name due to includes/modules/new_products.php. because I've got a random prods & dangle carrot contrib installed.

my store is currently:

Current Parse Time: 0.550 s with 55 queries

I've got maybe 100 products still to add -

what do people think ? is this an ok parse time & query number?

btw - many thanks to Jan for getting back to me earlier

♥stubbsy · October 14, 2007

Not really advice or ideas, but you can add Chemo's debug contribution to the admin relatively easy (the install instructions only mention the catalog side, but it is not hard to imagine how to add it to the admin side). In that case you can at least find out what is/are the slow query/queries.

Thanks Jan,

good idea, I hadn't thought of that, I'll give that a go and see where it gets me

Dave

Jan Zonjee · October 14, 2007

Well - i added the tep_get_category_tree contrib & it didn't make any difference.

True, that will only do something in admin (although it is also used in STS).

the only thing i didn't do was to add the -Additional optimization information, to save a number of queries for products_name due to includes/modules/new_products.php. because I've got a random prods & dangle carrot contrib installed.

Probably you can add the table products_description in there too (if it isn't already in the query but the author forgot to add products_name to the selected fields, seen that!).

my store is currently:
Current Parse Time: 0.550 s with 55 queries

I've got maybe 100 products still to add -

what do people think ? is this an ok parse time & query number?

It is not too bad, but when you can eliminate those 15-20 you are around the number that is about feasible. It should be possible to get to about 35 IMO.

They look like typical queries from the categories box though. Do you use a templated one or another non-standard one?

I have seen the one Monika Mathé (Monika_in_Germany) described in the sample chapter of her book that has the different top categories in their own box and with some adaptations that too can be made "query free".

outlawtraders · October 16, 2007

They look like typical queries from the categories box though. Do you use a templated one or another non-standard one?

I have seen the one Monika Mathé (Monika_in_Germany) described in the sample chapter of her book that has the different top categories in their own box and with some adaptations that too can be made "query free".

hi again,

yes i looked back & i am using categories enhanced with dropdowns, I'm guessing this is where the queries are coming from - so as to your suggestion about making this query free. How do i start?

many thanks

Paula

Jan Zonjee · October 18, 2007

yes i looked back & i am using categories enhanced with dropdowns, I'm guessing this is where the queries are coming from - so as to your suggestion about making this query free. How do i start?

Tricky. I'm not familiar with that contribution but probably you are going to need the category name. I will post the adapted code needed for Monika's version of the categories box so you can see what changes were made (I mostly commented out the original code).

This is the adapted CountProductsStore.php file:

<?php
/* $Id: CountProductsStore.php v 1.1 2007/00/10 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;
 }
}
?>

And this is the adapted code for the categories box:

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

 Modified by Monika Mathe
 http://www.monikamathe.com

 This recipe was first published in the sample chapter of "Deep Inside osCommerce: The Cookbook".

 Module Copyright (c) 2006 Monika Mathe

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

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License

*/

 function tep_show_category($counter) {
global $tree, $categories_string, $cPath_array, $cat_name;

$cPath_new = 'cPath=' . $tree[$counter]['path'];
$categories_string .= '<a href="';

$categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">';

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

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

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

if (tep_has_category_subcategories($counter)) {
  $categories_string .= '->';
}

$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 //-->
<?php
 if (isset($cPath_array)) {
	for ($i=0, $n=sizeof($cPath_array); $i<$n; $i++) {
/*			$categories_query = tep_db_query("select categories_name from " . TABLE_CATEGORIES_DESCRIPTION . " where categories_id = '" . (int)$cPath_array[$i] . "' and language_id = '" . (int)$languages_id . "'");
		if (tep_db_num_rows($categories_query) > 0)
		  $categories = tep_db_fetch_array($categories_query); */
	$index_of_cpath_array = (int)$cPath_array[$i];
	  }
	$cat_name = $countproducts->category_name_tree[$index_of_cpath_array];
	// $cat_name = $categories['categories_name'];
  }
// display category name

 $num = 0;

/*  $categories_box_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); */
 $categories_box = $countproducts->hasChildCategories(0);
// while ($categories_box = tep_db_fetch_array($categories_box_query))  {
 foreach ($categories_box as $key => $categories_id) {
	$box_id = $categories_id;
//		$box_id = $categories_box['categories_id'];

//now loop through the box-cats and create extra boxes for them
?>
	  <tr>
		<td>
<?php
 $info_box_contents = array();
//  $info_box_contents[] = array('text' => $categories_box['categories_name']);
 $info_box_contents[] = array('text' => $countproducts->category_name_tree[$box_id]);

$num++;
if (tep_has_category_subcategories($box_id)) {
	if ($num == 1) {
		new infoBoxHeading($info_box_contents, true, false, tep_href_link(FILENAME_DEFAULT,'cPath=' . $box_id), tep_href_link(FILENAME_DEFAULT,'cPath=' . $box_id));
	} else {
		new infoBoxHeading($info_box_contents, false, false, tep_href_link(FILENAME_DEFAULT,'cPath=' . $box_id), tep_href_link(FILENAME_DEFAULT,'cPath=' . $box_id));
	}

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

/*		$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$box_id ."' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); */
	$categories = $countproducts->hasChildCategories((int)$box_id);
	foreach ($categories as $key => $categories_id) {
	// while ($categories = tep_db_fetch_array($categories_query))  {
		$tree[$categories_id] = array('name' => $countproducts->category_name_tree[$categories_id],
   //	 $tree[$categories['categories_id']] = array('name' => $categories['categories_name'],
   //										  'parent' => $categories['parent_id'],
								'parent' => $countproducts->getParentCategory($categories_id),
								 'level' => 0,
								 'path' => $categories_id,
  //							   'path' => $categories['categories_id'],
								 'next_id' => false);

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

	   // $parent_id = $categories['categories_id'];
		$parent_id = $categories_id;

		if (!isset($first_element)) {
		//	$first_element = $categories['categories_id'];
			$first_element = $categories_id;
		}
	}

	//------------------------
	if (tep_not_null($cPath)) {
		$new_path = '';
		reset($cPath_array);
		while (list($key, $value) = each($cPath_array)) {
			unset($parent_id);
			unset($first_id);
			$categories_c = $countproducts->hasChildCategories((int)$value);
/*				$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$value . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); */
			if (!categories_c) {
		 //   if (tep_db_num_rows($categories_query)) {
				$new_path .= $value;
				foreach ($categories_c as $key => $categories_id) {
				$tree[$categories_id] = array('name' => $countproducts->category_name_tree[$categories_id],
				  'parent' => $countproducts->getParentCategory($categories_id),
				  'level' => $key+1,
				  'path' => $new_path . '_' . $categories_id,
				  'next_id' => false);
			/*	while ($row = tep_db_fetch_array($categories_query)) {
					$tree[$row['categories_id']] = array('name' => $row['categories_name'],
					'parent' => $row['parent_id'],
					'level' => $key+1,
					'path' => $new_path . '_' . $row['categories_id'],
					'next_id' => false); */
					if (isset($parent_id)) {
					$tree[$parent_id]['next_id'] = $categories_id;
					// $tree[$parent_id]['next_id'] = $row['categories_id'];
					}

		   //		 $parent_id = $row['categories_id'];
					$parent_id = $categories_id;

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

					$last_id = $row['categories_id'];
				}
				$tree[$last_id]['next_id'] = $tree[$value]['next_id'];
				$tree[$value]['next_id'] = $first_id;
				$new_path .= '_';
			} else {
				break;
			}
		}
	}
	tep_show_category($first_element); 

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

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

josh1r · October 22, 2007

Hi all. I could definitely use some help here. I have several different osc sites and they're all quite fast except for one. As far as I can tell I've installed all the same fixes (tax, tep categories, etc...) but my pages are still loading slowly (especially category pages). Manufacturer, product and main page take 1-2 seconds, but category pages takes anywhere from 5 to 60 seconds.

I have 1,500 items in 80 categories (is that just too many categories to ever have a fast store?)

Example of manufacturer page: Current Parse Time: 1.797 s with 29 queries

Example of product page: Current Parse Time: 0.405 s with 38 queries

Main page: Current Parse Time: 0.779 s with 34 queries

Category page: Current Parse Time: 6.336 s with 31 queries

Another Category page: Current Parse Time: 44.215 s with 31 queries

As you can see there are not a lot of queries, but the time is long for the category pages.

What's also odd is that none of the queries takes long at all, yet the total time somehow takes forever. Any help would be greatly appreciated!

Current Parse Time: 44.215 s with 31 queries

QUERY DEBUG:

Array

(

[QUERIES] => Array

(

[0] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration

[1] => select value from sessions where sesskey = 'f43c1346929fdd63bad94f2102f83619' and expiry > '1193083210'

[2] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies

[3] => delete from whos_online where time_last_click < '1193082310'

[4] => select count(*) as count from whos_online where session_id = 'f43c1346929fdd63bad94f2102f83619'

[5] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = 'xx.xxx.xxx.xx', time_last_click = '1193083210', last_page_url = '/julie-sandlau-c-2288.html' where session_id = 'f43c1346929fdd63bad94f2102f83619'

[6] => select banners_id, date_scheduled from banners where date_scheduled != ''

[7] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id

[8] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0

[9] => select categories_name from categories_description where categories_id = '2288' and language_id = '1'

[10] => select count(*) as total from products_to_categories where categories_id = '2288'

[11] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name

[12] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name

[13] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '2288' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name

[14] => select categories_id, parent_id from categories where parent_id in (2247,2248,2249,2250,2251,2252,2253,2254,2255,2256,2257,2258,2259,2260,2261,2262

,2263,2264,2265,2266,2267,2268,2269,2270,2271,2272,2273,2274,2275,2276,2277,2278,

2279,2280,2281,2282,2283,2284,2285,2286,2287,2288,2289,2290,2291,2292,2293,2294,2

295,2296,2297,2298,2299,2300,2301,2302,2303,2304,2305,2306,2307,2308,2309,2310,23

11,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2326,232

7)

[15] => select cd.categories_name from categories c, categories_description cd where c.categories_id = '2288' and cd.categories_id = '2288' and cd.language_id = '1'

[16] => select distinct m.manufacturers_id as id, m.manufacturers_name as name from products p, products_to_categories p2c, manufacturers m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '2288' order by m.manufacturers_name

[17] => select categories_image from categories where categories_id = '2288'

[18] => select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '2288'

[19] => select m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '2288' order by pd.products_name limit 0, 20

[20] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '0' group by tr.tax_priority

[21] => select products_id, products_image, products_tax_class_id, products_price from products where products_status = '1' order by products_date_added desc limit 10

[22] => select products_name from products_description where products_id = '1563' and language_id = '1'

[23] => select specials_new_products_price from specials where products_id = '1563' and status

[24] => select startdate, counter from counter

[25] => update counter set counter = '938283'

[26] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'

[27] => select count(*) as count from banners_history where banners_id = '1' and date_format(banners_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d')

[28] => update banners_history set banners_shown = banners_shown + 1 where banners_id = '1' and date_format(banners_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d')

[29] => select count(*) as total from sessions where sesskey = 'f43c1346929fdd63bad94f2102f83619'

[30] => update sessions set expiry = '1193084695', value = 'cart|O:12:\"shoppingcart\":5:{s:8:\"contents\";a:0:{}s:5:\"total\";i:0;s:6:\"weight\";i:0;s:6:\"cartID\";N;s:12:\"content_type\";b:0;}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"USD\";navigation|O:17:\"navigationhistory\":2:{s:4:\"path\";a:2:{i:0;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:0:{}s:4:\"post\";a:0:{}}i:1;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:1:{s:5:\"cPath\";s:4:\"2288\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = 'f43c1346929fdd63bad94f2102f83619'

)

[TIME] => Array

(

[0] => 0.002429

[1] => 0.001345

[2] => 0.001031

[3] => 0.000946

[4] => 0.000415

[5] => 0.002349

[6] => 0.000735

[7] => 0.001213

[8] => 0.000964

[9] => 0.000654

[10] => 0.000732

[11] => 0.001056

[12] => 0.003290

[13] => 0.000622

[14] => 0.001819

[15] => 0.000771

[16] => 0.003480

[17] => 0.000728

[18] => 0.004059

[19] => 0.003072

[20] => 0.001680

[21] => 0.001309

[22] => 0.001052

[23] => 0.000701

[24] => 0.000843

[25] => 0.000471

[26] => 0.000778

[27] => 0.002423

[28] => 0.002868

[29] => 0.000836

[30] => 0.000457

Jan Zonjee · October 22, 2007

Hi all. I could definitely use some help here. I have several different osc sites and they're all quite fast except for one. As far as I can tell I've installed all the same fixes (tax, tep categories, etc...) but my pages are still loading slowly (especially category pages). Manufacturer, product and main page take 1-2 seconds, but category pages takes anywhere from 5 to 60 seconds.

I have 1,500 items in 80 categories (is that just too many categories to ever have a fast store?)

Example of manufacturer page: Current Parse Time: 1.797 s with 29 queries

Example of product page: Current Parse Time: 0.405 s with 38 queries

Main page: Current Parse Time: 0.779 s with 34 queries

Category page: Current Parse Time: 6.336 s with 31 queries

Another Category page: Current Parse Time: 44.215 s with 31 queries

As you can see there are not a lot of queries, but the time is long for the category pages.

What's also odd is that none of the queries takes long at all, yet the total time somehow takes forever. Any help would be greatly appreciated!

I notice you are using some SEO contribution: last_page_url = '/julie-sandlau-c-2288.html'

Might have something to do with that. I have seen it on a site of someone (who was on a slow server) where certain url's apparently confused the SEO code in the mod rewrite and it took forever to load certain pages (about a 4-5 fold of other pages). I don't know if it was that particular SEO contribution or the way it was installed. Just guessing here :)

It was especially visible on the index page (when you landed) or if you tacked a cPath=## onto the url.

josh1r · October 23, 2007

I notice you are using some SEO contribution: last_page_url = '/julie-sandlau-c-2288.html'

Might have something to do with that. I have seen it on a site of someone (who was on a slow server) where certain url's apparently confused the SEO code in the mod rewrite and it took forever to load certain pages (about a 4-5 fold of other pages). I don't know if it was that particular SEO contribution or the way it was installed. Just guessing here :)

It was especially visible on the index page (when you landed) or if you tacked a cPath=## onto the url.

Thanks for the suggestion. I removed the SEO contrib and unfortunately it didn't change anything :(

Jan Zonjee · October 23, 2007

Thanks for the suggestion. I removed the SEO contrib and unfortunately it didn't change anything :(

Bummer...

Then there must be something slow in the PHP code. You can use the queries debug for this also. If for example you add on index.php before the line where application_top is loaded like so:

  $code_start = microtime();
 require('includes/application_top.php');

Then e.g. if you want to know how much time has passed from that moment till column_left is included you add/change that code to:

<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); 
 $_start = explode(' ', $code_start);
 $_end = explode(' ', microtime());
 $_time = number_format(($_end[1] + $_end[0] - ($_start[1] + $_start[0])), 6);

 $debug['QUERIES'][] = 'time till left column is included';
 $debug['TIME'][] = $_time;
?>
<!-- left_navigation_eof //-->

In the list of queries you would find it back as:

[16] => time till left column is included

In my case Time: [16] => 0.418526

You can move this in your code of index.php and find the slow part. If it is in colum_left.php you move all that code to there and try to pinpoint the slow part there.

Just my 0.02

josh1r · October 24, 2007

You can move this in your code of index.php and find the slow part. If it is in colum_left.php you move all that code to there and try to pinpoint the slow part there.

Just my 0.02

Thanks! I did what you suggested and it narrowed down the problem. It appears to be in the product_listing.php file (in /includes/modules/). I guess that's why it only occurs on category and manuf. pages, not main index or product pages, as those don't have product listings.

I tried the code in various spots in that file and found that on line 78 is where it starts to get bad (so I guess that's the code right above it which is listing split numbers of rows, etc... And depending if I put it on line 78 or below I get anywhere from 20 to 50 rows of this: [20] => time till product_listings is included

Does this mean anything to anyone?

Thanks again!

LINE 63 is OK*******IF I PUT HERE IT'S OK********
   if ( ($column_list[$col] != 'PRODUCT_LIST_BUY_NOW') && ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {
     $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
   }

   $list_box_contents[0][] = array('align' => $lc_align,
                                   'params' => 'class="productListing-heading"',
                                   'text' => ' ' . $lc_text . ' ');
 }

LINE 72 is OK*******IF I PUT HERE IT'S OK********

 if ($listing_split->number_of_rows > 0) {
   $rows = 0;
   $listing_query = tep_db_query($listing_split->sql_query);
   while ($listing = tep_db_fetch_array($listing_query)) {
     $rows++;
LINE 78 ***THIS IS WHERE IT GETS BAD***********
     if (($rows/2) == floor($rows/2)) {
       $list_box_contents[] = array('params' => 'class="productListing-even"');
     } else {
       $list_box_contents[] = array('params' => 'class="productListing-odd"');
     }

TracyS · October 24, 2007

I would install the queries debug contribution and see what queries are being repeated. IMHO, it appears that it most likely is the query itself not the section of code you said has the problem. My reasoning is that the section of code is simply displaying the results of a query. When I was going through and optimizing my pages, I found that a lot of the optimization was not necessarily happening on the page (product_listing.php for example) but was instead happening where the query was, or in how another included page was handling the query (like application_top.php and general.php etc...). Using the queries debug contribution allowed me to narrow it down a bit more :thumbsup:

Thanks! I did what you suggested and it narrowed down the problem. It appears to be in the product_listing.php file (in /includes/modules/). I guess that's why it only occurs on category and manuf. pages, not main index or product pages, as those don't have product listings.

I tried the code in various spots in that file and found that on line 78 is where it starts to get bad (so I guess that's the code right above it which is listing split numbers of rows, etc... And depending if I put it on line 78 or below I get anywhere from 20 to 50 rows of this: [20] => time till product_listings is included

Does this mean anything to anyone?

Thanks again!
LINE 63 is OK*******IF I PUT HERE IT'S OK********
   if ( ($column_list[$col] != 'PRODUCT_LIST_BUY_NOW') && ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {
     $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
   }

   $list_box_contents[0][] = array('align' => $lc_align,
                                   'params' => 'class="productListing-heading"',
                                   'text' => ' ' . $lc_text . ' ');
 }

LINE 72 is OK*******IF I PUT HERE IT'S OK********

 if ($listing_split->number_of_rows > 0) {
   $rows = 0;
   $listing_query = tep_db_query($listing_split->sql_query);
   while ($listing = tep_db_fetch_array($listing_query)) {
     $rows++;
LINE 78 ***THIS IS WHERE IT GETS BAD***********
     if (($rows/2) == floor($rows/2)) {
       $list_box_contents[] = array('params' => 'class="productListing-even"');
     } else {
       $list_box_contents[] = array('params' => 'class="productListing-odd"');
     }

josh1r · October 24, 2007

I would install the queries debug contribution and see what queries are being repeated. IMHO, it appears that it most likely is the query itself not the section of code you said has the problem. My reasoning is that the section of code is simply displaying the results of a query. When I was going through and optimizing my pages, I found that a lot of the optimization was not necessarily happening on the page (product_listing.php for example) but was instead happening where the query was, or in how another included page was handling the query (like application_top.php and general.php etc...). Using the queries debug contribution allowed me to narrow it down a bit more :thumbsup:

At the risk of sounding like an idiot, don't I already have the queries debug installed? Isn't that where I'm seeing everything at the bottom of the page? Here's what i see:

 => index.php
                           [mode] => NONSSL
                           [get] => Array
                               (
                               )

                           [post] => Array
                               (
                               )

                       )

                   [1] => Array
                       (

 => index.php
                           [mode] => NONSSL
                           [get] => Array
                               (
                                   [cPath] => 2250
                               )

                           [post] => Array
                               (
                               )

                       )

               )

           [snapshot] => Array
               (
               )

       )

)

COOKIE:

Array
(
   [cookie_test] => please_accept_for_session
   [osCsid] => b217b2602a2464336a8c920e960d9bbd
)

POST:

Array
(
)

GET:

Array
(
   [cPath] => 2250
)

Normally it's between 15 and 30 seconds, not sure why it's only 9 right now.

Is this the contribution you're referring to or is there something else I should install?

Thank you again for all your help!

A Store Speed Optimization in Progress

Recommended Posts

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived