A Store Speed Optimization in Progress

aplein · March 17, 2008

Fantastic thread!,

I need help, please.

Fatal error: Call to a member function trail() on a non-object in /var/www/html/catalog/includes/sts_display_output.php on line 232

In sts_display_output.php on line 232

$template['breadcrumbs'] = $breadcrumb->trail(' » ');

What can i Do?

Sorry vor my English.

Thanks Alfred

♥FWR Media · March 17, 2008

Fantastic thread!,

I need help, please.

Fatal error: Call to a member function trail() on a non-object in /var/www/html/catalog/includes/sts_display_output.php on line 232

In sts_display_output.php on line 232

$template['breadcrumbs'] = $breadcrumb->trail(' » ');

What can i Do?

Sorry vor my English.

Thanks Alfred

That has absolutely nothing to do with this thread it's an STS issue.

aplein · March 17, 2008

ok, thanks.

Davee · March 30, 2008

Hi

I'm getting about 150 queries I want to optimize

[15] => select categories_id from categories where parent_id = '373'

[16] => select categories_id from categories where parent_id = '4'

etc...

Is there any way to optimize these

Thanks in advance

Dave

♥FWR Media · March 30, 2008

Hi

I'm getting about 150 queries I want to optimize

[15] => select categories_id from categories where parent_id = '373'

[16] => select categories_id from categories where parent_id = '4'

etc...

Is there any way to optimize these

Thanks in advance

Dave

This is documented in this thread .. just takes a bit of reading.

Davee · March 30, 2008

This is documented in this thread .. just takes a bit of reading.

Robert Thanks...

I have read through the full 32 pages & found that the query is coming from a modified version of new_products

Here is the query

$categorie_query = tep_db_query("SELECT categories_id from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$cats[$i] . "'"); 
while ($categorie = tep_db_fetch_array($categorie_query)) {
$cats[] = $categorie['categories_id'];
}

Is there any way to modify this code to get the query to query like select categories_id from categories where parent_id = '373, 4, etc..

Thanks again

Dave

♥FWR Media · March 31, 2008

Robert Thanks...

I have read through the full 32 pages & found that the query is coming from a modified version of new_products

Here is the query
$categorie_query = tep_db_query("SELECT categories_id from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$cats[$i] . "'"); 
while ($categorie = tep_db_fetch_array($categorie_query)) {
$cats[] = $categorie['categories_id'];
}
Is there any way to modify this code to get the query to query like select categories_id from categories where parent_id = '373, 4, etc..

Thanks again

Dave

You might want to click on the contributions tab on the left.

Davee · March 31, 2008

You might want to click on the contributions tab on the left.

Thanks Rob

Are you saying, modify this Contribution? parent_id Query Optimisation

Dave

♥FWR Media · March 31, 2008

Thanks Rob

Are you saying, modify this Contribution? parent_id Query Optimisation

Dave

If I understood your needs correctly.

Davee · March 31, 2008

If I understood your needs correctly.

It was "sort of" right but got round it in the end many thanks

One final query to optimise if possible? I don't think it's possible to make it any quicker :(

[19] => 0.095782

[19] => select distinct p.products_id, p.products_quantity, p.products_si, p.products_mi, p.products_price, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as specials_new_products_price, pd.products_name from products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id in (230, 279, 280, 281, 282, 283) and p.products_status = '1' order by p.products_date_added desc limit 100

adam777 · April 2, 2008

Thankyou all!

RC2a now works a lot faster.

Are the following contributions compatible with RC2a, work together (compatible with each other), and usefull? Any information is appreciated.

1. Chemo's Tax contribution

2. Faster Page Loads, Less DB queries

3. osC Advanced Cache Class

4. HTML Cache

Thanks

Adam

homewetbar · April 2, 2008

Hello,

I'm attempting to optimize my store and I've identified two main areas needing improvement. Hopefully someone can tell me how to optimize these two queries.

The first is in catalog/functions/general.php

// Return true if the category has subcategories
// TABLES: categories
 function tep_has_category_subcategories($category_id) {
$child_category_query = tep_db_query("select count(*) as count from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$category_id . "'");
$child_category = tep_db_fetch_array($child_category_query);

This is generating tons of queries as I'm sure it does for everyone else's store, one for every category. like this:

		   [19] => select count(*) as count from categories where parent_id = '26'
		[20] => select count(*) as count from categories where parent_id = '26'

The second area is I am using a modified version of Ultimate SEO URLS that pulls the SEO product URL from a field in the database, one query for every single product...

It is in catalog/functions/html_output.php and the code is:

					case 'products_id':
					$rewrite_product = true;
					if ( defined('PRODUCT_NAME_'.$p2[1]) ){
				// Custom Code 6/11/07 to allow for seo name to be entered in admin
						  $seo_url_query = tep_db_query("SELECT products_seo_url as psu FROM ".TABLE_PRODUCTS_DESCRIPTION." WHERE products_id='".$p2[1]."' LIMIT 1");
			 			$seo_url = tep_db_fetch_array($seo_url_query);
						  if($seo_url['psu'] != '') {
							$seoName = trim( $seo_url['psu'] ) . '-p-';
						} else {
						 	$seoName = 'gift-p-';
			 			}
						$rewrite_page_product = $seoName . $p2[1] . '.html';
			 	// END custom code
					} else { $seo = false; }
					break;

It is creating a query for every single product listed under the category displayed like so:

			[58] => SELECT products_seo_url as psu FROM products_description WHERE products_id='81' LIMIT 1
		[59] => SELECT products_seo_url as psu FROM products_description WHERE products_id='81' LIMIT 1

Can anyone please offer help on how to optimize these two codes? Please... =) Thanks in advance!

Jan Zonjee · April 2, 2008

I'm attempting to optimize my store and I've identified two main areas needing improvement. Hopefully someone can tell me how to optimize these two queries.

The first is in catalog/functions/general.php

Check the addons for optimize categories box 1.2. That should fix that.

The second area is I am using a modified version of Ultimate SEO URLS that pulls the SEO product URL from a field in the database, one query for every single product...
It is in catalog/functions/html_output.php and the code is:
					case 'products_id':
					$rewrite_product = true;
					if ( defined('PRODUCT_NAME_'.$p2[1]) ){
				// Custom Code 6/11/07 to allow for seo name to be entered in admin
						  $seo_url_query = tep_db_query("SELECT products_seo_url as psu FROM ".TABLE_PRODUCTS_DESCRIPTION." WHERE products_id='".$p2[1]."' LIMIT 1");
			 			$seo_url = tep_db_fetch_array($seo_url_query);
						  if($seo_url['psu'] != '') {
							$seoName = trim( $seo_url['psu'] ) . '-p-';
						} else {
						 	$seoName = 'gift-p-';
			 			}
						$rewrite_page_product = $seoName . $p2[1] . '.html';
			 	// END custom code
					} else { $seo = false; }
					break;
It is creating a query for every single product listed under the category displayed like so:
			[58] => SELECT products_seo_url as psu FROM products_description WHERE products_id='81' LIMIT 1
		[59] => SELECT products_seo_url as psu FROM products_description WHERE products_id='81' LIMIT 1
Can anyone please offer help on how to optimize these two codes? Please... =) Thanks in advance!

If you look at SPPC, modules products_listing and new_products you can see how first all the results are stored in an array and the products_id in another before doing more queries using a list of products_id's: and products_id IN (24, 22, 90, etcetera)

homewetbar · April 2, 2008

Check the addons for optimize categories box 1.2. That should fix that.

If you look at SPPC, modules products_listing and new_products you can see how first all the results are stored in an array and the products_id in another before doing more queries using a list of products_id's: and products_id IN (24, 22, 90, etcetera)

Thank you I found that contrib and it helps the first issue dramtically!

One the second issue, can please you elaborate on what I should change in SPPC (also what is SPPC?), modules products_listing and new_products as I am totally confused as to what you are talking about?

Jan Zonjee · April 2, 2008

On the second issue, can please you elaborate on what I should change in SPPC (also what is SPPC?), modules products_listing and new_products as I am totally confused as to what you are talking about?

Never mind, if you look at this version of includes/new_products.php which uses the same technique in those SPPC modules you should get the idea:

<?php
/*
 $Id: new_products.php #### 2008-01-11 00:52:16Z hpdl $

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

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/
?>
<!-- new_products //-->
<?php
 $info_box_contents = array();
 $info_box_contents[] = array('text' => sprintf(TABLE_HEADING_NEW_PRODUCTS, strftime('%B')));
 global $languages_id;

 new contentBoxHeading($info_box_contents);

 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);
 }

 $row = 0;
 $col = 0;
 $info_box_contents = array();
 while ($_new_products = tep_db_fetch_array($new_products_query)) {
$new_products[] = $_new_products;
$products_id_array[] = $_new_products['products_id'];
 }
 $product_names_query = tep_db_query("select products_id, products_name from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id in (" . implode(',', $products_id_array) . ") and language_id = '" . (int)$languages_id . "'");
 while ($_product_names = tep_db_fetch_array($product_names_query)) { 
  $product_names[] = array('products_id' => $_product_names['products_id'], 'products_name' => $_product_names['products_name']);  
 }

 $no_of_new_products = count($new_products);
 $no_of_product_names = count($product_names);
 for ($x = 0; $x < $no_of_new_products; $x++) {
if (!empty($product_names)) {
  for ($i = 0; $i < $no_of_product_names; $i++) {
	if ($new_products[$x]['products_id'] == $product_names[$i]['products_id']) {
	  $new_products[$x]['products_name'] = $product_names[$i]['products_name'];
	}
  }	
}
 } // end for ($x = 0; $x < $no_of_new_products; $x++)
//	$new_products['products_name'] = tep_get_products_name($new_products['products_id']);
 for ($x = 0; $x < $no_of_new_products; $x++) { 
$info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText" width="33%" valign="top"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $new_products[$x]['products_image'], $new_products[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products[$x]['products_id']) . '">' . $new_products[$x]['products_name'] . '</a><br>' . $currencies->display_price($new_products[$x]['products_price'], tep_get_tax_rate($new_products[$x]['products_tax_class_id'])));

$col ++;
if ($col > 2) {
  $col = 0;
  $row ++;
}
 }

 new contentBox($info_box_contents);
?>
<!-- new_products_eof //-->

homewetbar · April 2, 2008

Thank you I appreciate you attempting to help however I'm not as advanced of a coder as yourself so I'm having trouble following along, I understand now the problem is in the modules/product_listing query doing a query or two for every product.

Can anyone explain in more detail how to optimize the queries in modules/product_listing.php so they only use one or a few queries instead of one or two for every listing?

adam777 · April 3, 2008

Thank you I appreciate you attempting to help however I'm not as advanced of a coder as yourself so I'm having trouble following along, I understand now the problem is in the modules/product_listing query doing a query or two for every product.

Can anyone explain in more detail how to optimize the queries in modules/product_listing.php so they only use one or a few queries instead of one or two for every listing?

Hi Keith

If you are using oscommerce 2.2MS2 it is detailed in the thread, just before everyone begins talking about caching pages.

Jan

Is that new_products.php file to be inserted into RC2a or 2.2MS2 ?

Thanks

Adam

homewetbar · April 3, 2008

Hi Keith

If you are using oscommerce 2.2MS2 it is detailed in the thread, just before everyone begins talking about caching pages.

Jan

Adam

Hi Adam, Thanks but there are a ton of MS2 forums both in news and discussions, can you point to the forum you are refering to?

Jan Zonjee · April 3, 2008

Is that new_products.php file to be inserted into RC2a or 2.2MS2 ?

In RC2a another solution was chosen, this was a proposal because in the past it was found that adding the table products_description to the query in the end took more time than doing all the seperate queries. Despite that in RC2a products_description was added to the tables in the query (my feeling is the lacking index on products_id in the table specials might have caused the effect).

You can use both versions (this one and the RC2a one) in any version of 2.2MS2 and higher.

♥stubbsy · April 21, 2008

♥FWR Media · April 21, 2008

♥stubbsy · April 21, 2008

why aren't my posts appearing today???

♥FWR Media · April 21, 2008

Hi Stubbsy

Could you run exactly the same page again and confirm that queries ..

63 (horrible)

19 (nasty)

48 (nasty)

Are still showing similar values.

My initial recommendation is run . .

alter table orders_status_history add index idx_osh_orders_id (orders_id);

then check query 63, should make a load of difference.

♥stubbsy · April 21, 2008

♥stubbsy · April 21, 2008

that is a re-run of the same page.

A Store Speed Optimization in Progress

Recommended Posts

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