904 replies to this topic

[Chemo]

Hello,

I have been working on optimizing the MS2 codebae for several months and am going to do a test site from scratch so everyone can follow along.

This is my development server and I'm going to keep the admin area open for everyone.  Don't mess this up with malicious stuff or I'll lock it down.

On the bottom of every page there is a debug output with parse time, query count, and the actual queries used to construct the page.  I'll be using this info to optimize the queries.

Can anyone take a look and see any redundant queries?  It's a loaded question since I've already been down this block and know where there is opportunity to streamline.  Let's see if anyone wants to follow along as we get this down to 10 queries per page and sub .1 second render times (read: bad ass cart performance).

The only requirement to the optimizations are a net increase in performance and NO LOSS OF FUNCTIONALITY.

Vanilla Install Stats:
Parse Time: .328 seconds
Queries - 92

Disable Category Counts:
Parse Time: .259 seconds
Queries: 52

So, save 40 queries right off the bat for disabling category counts.

More optimizations tomorrow...whe I start to dissect the code and get this puppy screaming.

Edited by sparky, 07 January 2005 - 12:41 PM.

[Chemo]

So now I have removed the banner code and the stats have dropped:

Page Render Time: .221 seconds
Queries: 47

Tomorrow I am going to the sources of these apparently redundant queries:

            [12] => 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
            [13] => select products_name from products_description where products_id = '21' and language_id = '1'
            [14] => select specials_new_products_price from specials where products_id = '21' and status
            [15] => 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 = '1' group by tr.tax_priority
            [16] => 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 products p left join specials s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit 9
            [17] => select products_name from products_description where products_id = '7' and language_id = '1'
            [18] => 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 = '1' group by tr.tax_priority
            [19] => select products_name from products_description where products_id = '21' and language_id = '1'
            [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 = '1' group by tr.tax_priority
            [21] => select products_name from products_description where products_id = '10' and language_id = '1'
            [22] => 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 = '1' group by tr.tax_priority
            [23] => select products_name from products_description where products_id = '24' and language_id = '1'
            [24] => 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 = '1' group by tr.tax_priority
            [25] => select products_name from products_description where products_id = '13' and language_id = '1'
            [26] => 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 = '1' group by tr.tax_priority
            [27] => select products_name from products_description where products_id = '27' and language_id = '1'
            [28] => 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 = '1' group by tr.tax_priority
            [29] => select products_name from products_description where products_id = '2' and language_id = '1'
            [30] => 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 = '1' group by tr.tax_priority
            [31] => select products_name from products_description where products_id = '16' and language_id = '1'
            [32] => 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 = '1' group by tr.tax_priority
            [33] => select products_name from products_description where products_id = '5' and language_id = '1'
            [34] => 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 = '1' group by tr.tax_priority
            [35] => select p.products_id, pd.products_name, products_date_available as date_expected from products p, products_description pd where to_days(products_date_available) >= to_days(now()) and p.products_id = pd.products_id and pd.language_id = '1' order by date_expected desc limit 10
            [36] => 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
            [37] => select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added desc limit 10
            [38] => 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 = '1' group by tr.tax_priority
            [39] => 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 = '1' group by tr.tax_priority

Can anyone guess where they are located?

Edited by Chemo, 31 October 2004 - 05:08 AM.

[Chemo]

First, I removed the banner (none of my sites or client sites use banners).  This brought the page query count down to:

Page Render Time: .221 seconds
Queries: 47

Second, I optimized the tep_get_tax() method and also tep_get_tax_description() method.  Previously, I approached the same problem and used a session based solution.  In retrospect, this was a sloppy -- but highly effective -- way of decreasing the query count since each price displayed on the shopping cart used 1-2 additional queries just to get the tax rate (even if you chose to disable display with tax).

The code for MS3 approaches this fundamental problem of excessive queries with a nice tax class.  Basically, I used the next tax class for MS3 and adapted it for MS2.

Page Parse Time: .179 seconds
Queries: 36

Now, I'm going to look at this set of queries:

            [17] => select products_name from products_description where products_id = '7' and language_id = '1'
            [18] => select products_name from products_description where products_id = '21' and language_id = '1'
            [19] => select products_name from products_description where products_id = '10' and language_id = '1'
            [20] => select products_name from products_description where products_id = '24' and language_id = '1'
            [21] => select products_name from products_description where products_id = '13' and language_id = '1'
            [22] => select products_name from products_description where products_id = '27' and language_id = '1'
            [23] => select products_name from products_description where products_id = '2' and language_id = '1'
            [24] => select products_name from products_description where products_id = '16' and language_id = '1'
            [25] => select products_name from products_description where products_id = '5' and language_id = '1'

They are all the same...basically querying for the product name.  This is a sign to me that a query somewhere can be optimized to use a JOIN and not a separate method to query.

Where is it though?  Can anyone find it before I fix it?

[bruyndoncx]

something like this in product_listing.php ?
          $listing['products_name'] = tep_get_products_name($listing['products_id']);

the actual query being defined in index.php and advanced_search_result.php

Edited by bruyndoncx, 31 October 2004 - 02:49 PM.

[bruyndoncx]

can you share the tax class modification you made for MS2 ?

[Chemo]

Implement MS3 Tax Class for MS2

[Chemo]

For those following along the query count is now down to 27.  

The vanilla install was 92 queries and we have gotten that down to 27 with only a few modifications.  This is a 70% reduction with only 3 minor file changes and a configuration setting.

There are other optimization techniques I'll use in the upcoming days and also add a few popular contributions.  Similiarly, I'll optimize the queries for each added contribution.

[wizardsandwars]

Next thing I would do is eliminate the 'Page Requests since'. This is a useless stat, as it counts each time anyone requests a page form the site, including any and all search engine spiders.

Also, I think there are some very good indexes we could add to the DB to help the query time a little. Probalby won't make a huge difference with a stock store, but once a couple hundred products or more are added, this will help tremendously.

Other suggestions

- Remove tables in favor of css placement
- Enable gzip compression
- install the configuration case contribution

[RavenWulf]

Chemo, on Oct 31 2004, 02:30 PM, said:

First, I removed the banner (none of my sites or client sites use banners).  This brought the page query count down to:

Page Render Time: .221 seconds
Queries: 47

Second, I optimized the tep_get_tax() method and also tep_get_tax_description() method.  Previously, I approached the same problem and used a session based solution.  In retrospect, this was a sloppy -- but highly effective -- way of decreasing the query count since each price displayed on the shopping cart used 1-2 additional queries just to get the tax rate (even if you chose to disable display with tax).

The code for MS3 approaches this fundamental problem of excessive queries with a nice tax class.  Basically, I used the next tax class for MS3 and adapted it for MS2.

Page Parse Time: .179 seconds
Queries: 36

Now, I'm going to look at this set of queries:

            [17] => select products_name from products_description where products_id = '7' and language_id = '1'
            [18] => select products_name from products_description where products_id = '21' and language_id = '1'
            [19] => select products_name from products_description where products_id = '10' and language_id = '1'
            [20] => select products_name from products_description where products_id = '24' and language_id = '1'
            [21] => select products_name from products_description where products_id = '13' and language_id = '1'
            [22] => select products_name from products_description where products_id = '27' and language_id = '1'
            [23] => select products_name from products_description where products_id = '2' and language_id = '1'
            [24] => select products_name from products_description where products_id = '16' and language_id = '1'
            [25] => select products_name from products_description where products_id = '5' and language_id = '1'

They are all the same...basically querying for the product name.  This is a sign to me that a query somewhere can be optimized to use a JOIN and not a separate method to query.

Where is it though?  Can anyone find it before I fix it?

<{POST_SNAPBACK}>

Chemo

umm I am new to php and such and was wondering where and how you removed the banner code.  was this done in the footer?

Could you please explain?

Thank you
RavenWulf

[Chemo]

Sure...

In application_top you'll find code that looks like the following around line 448 or so:

// auto activate and expire banners
require(DIR_WS_FUNCTIONS . 'banner.php');
tep_activate_banners();
tep_expire_banners();

Replace it with this code:

// auto activate and expire banners
#  require(DIR_WS_FUNCTIONS . 'banner.php');
#  tep_activate_banners();
#  tep_expire_banners();

Notice, I used the old style # comment tag.  Yes, hard habit are hard to break (for those that know wha I mean).

Next, in footer.php you'll find code that looks like this:

<?php
  if ($banner = tep_banner_exists('dynamic', '468x50')) {
?>
<br>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td align="center"><?php #echo tep_display_banner('static', $banner); ?></td>
  </tr>
</table>
<?php
  }
?>

Change it to this:

<?php
  #if ($banner = tep_banner_exists('dynamic', '468x50')) {
?>
<!-- remove banner code
<br>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td align="center"><?php #echo tep_display_banner('static', $banner); ?></td>
  </tr>
</table>
//-->
<?php
  #}
?>

Notice, I used the classic HTML comment tag and also the old style comment # for the PHP.

This change netted us 5 reduced queries per page.

As a side note, have you thought about installing a debug code on your site to help with optimizing?  It is the same kind of output on the bottom of the dev site.

[996]

What kind of code would i use to show this: Queries: 52

thanks.   Mod seems to be working great but would love to see how many queries it cut down

[Dragonmom]

This is so interesting! I'll be following this development.
many of my visitors arrive via dialup, so load time is important to me.
I can only add contribs- I don't understand how to modify code. I removed the date and visitor counter first thing, is there a queriy that I need to remove also? I don't see anything in application_top.php

[Jerah]

Chemo, you sir are a legend.

I've been wading through the other optimisation thread looking for ways of cutting queries and upping speed for my (heavily customised) site. It's great to find a thread that starts off right at the basics - the base code.

Keep up the good work!

[boxtel]

Chemo, on Oct 31 2004, 02:30 PM, said:

First, I removed the banner (none of my sites or client sites use banners).  This brought the page query count down to:

Page Render Time: .221 seconds
Queries: 47

Second, I optimized the tep_get_tax() method and also tep_get_tax_description() method.  Previously, I approached the same problem and used a session based solution.  In retrospect, this was a sloppy -- but highly effective -- way of decreasing the query count since each price displayed on the shopping cart used 1-2 additional queries just to get the tax rate (even if you chose to disable display with tax).

The code for MS3 approaches this fundamental problem of excessive queries with a nice tax class.  Basically, I used the next tax class for MS3 and adapted it for MS2.

Page Parse Time: .179 seconds
Queries: 36

Now, I'm going to look at this set of queries:

            [17] => select products_name from products_description where products_id = '7' and language_id = '1'
            [18] => select products_name from products_description where products_id = '21' and language_id = '1'
            [19] => select products_name from products_description where products_id = '10' and language_id = '1'
            [20] => select products_name from products_description where products_id = '24' and language_id = '1'
            [21] => select products_name from products_description where products_id = '13' and language_id = '1'
            [22] => select products_name from products_description where products_id = '27' and language_id = '1'
            [23] => select products_name from products_description where products_id = '2' and language_id = '1'
            [24] => select products_name from products_description where products_id = '16' and language_id = '1'
            [25] => select products_name from products_description where products_id = '5' and language_id = '1'

They are all the same...basically querying for the product name.  This is a sign to me that a query somewhere can be optimized to use a JOIN and not a separate method to query.

Where is it though?  Can anyone find it before I fix it?

<{POST_SNAPBACK}>

I believe it is in the new_products module where function tep_get_products_name($new_products['products_id']); is called in a loop. Makes no sense since the product name is already retrieved in the products query.
I commented that function out without problems.

PS. great contribution "page_cache", improves a lot. Maybe one could filter out the possible session_id's from the parameters.

Amanda.

[Chemo]

996, on Oct 31 2004, 07:07 PM, said:

What kind of code would i use to show this: Queries: 52

thanks.   Mod seems to be working great but would love to see how many queries it cut down

<{POST_SNAPBACK}>

I'll put it into a contribution and post it...should be a quick and install.

Dragonmom, on Oct 31 2004, 09:02 PM, said:

This is so interesting! I'll be following this development.
many of my visitors arrive via dialup, so load time is important to me.
I can only add contribs- I don't understand how to modify code. I removed the date and visitor counter first thing, is there a queriy that I need to remove also? I don't see anything in application_top.php

<{POST_SNAPBACK}>

There are several areas that can be optimized for your particular site!  Some want the banner code whereas others don't.  Some love the hit counter at the bottom whereas others don't.  See the trend?  The stock osCommerce installation has them all in there so someone does not have to add but if they don't need them it just kills the query count.

Tell me what you want disabled and I'll give you the code to take it out...

Jerah, on Oct 31 2004, 09:21 PM, said:

Chemo, you sir are a legend.

I've been wading through the other optimisation thread looking for ways of cutting queries and upping speed for my (heavily customised) site. It's great to find a thread that starts off right at the basics - the base code.

Keep up the good work!

<{POST_SNAPBACK}>

Thank you very much   Wait until you see code that I haven't released yet that will bring the count down to 5 queries and sub .1 second page load times!

boxtel, on Nov 1 2004, 01:23 AM, said:

I believe it is in the new_products module where function tep_get_products_name($new_products['products_id']); is called in a loop. Makes no sense since the product name is already retrieved in the products query.
I commented that function out without problems.

PS. great contribution "page_cache", improves a lot. Maybe one could filter out the possible session_id's from the parameters.

Amanda.

<{POST_SNAPBACK}>

YOU ARE CORRECT!  Use a LEFT JOIN to pull the name as well and it eliminates a number of queries!

[Chemo]

For those that requested the query output and page parse times I have added the contribution and can be downloaded here: Output Queries Debug

Now, let's get it installed so you can see the effectiveness of the optimizations

[Chemo]

BTW, if you use the query and parse time debug contribution in combination with the page cache you'll have to add it as a global variable in the cache_this_page() method of the includes/classes/page_cache.php file.

For instance, the cache_this_page() method should look like this:

/*###############################################################################
###

cache_this_page() method
	- no arguments
	- no return

################################################################################
###*/
	function cache_this_page () {
  global $debug;  
  if ($this->is_guest && ENABLE_PAGE_CACHE =='true') {
 	 $this->debug_output['is_guest_check'] = 'customer_id not set - cache_this_page()';
 	 if ( $this->cache_file_exists && !$this->cache_file_is_expired ) {
    $this->debug_output['file_exists_and_is_not_expired'] = 'file exists and is not expired';
    echo str_replace("<%CART_CACHE%>", $this->customer_cart, file_get_contents($this->cache_file) );    
    $this->debug();
    include_once (DIR_WS_INCLUDES . 'application_bottom.php');
    exit;
 	 } # END file exists and is not expired 	 
 	 if ( !$this->cache_file_exists || $this->cache_file_is_expired ) {
    $this->debug_output['no_file_or_expired'] = 'file does not exist or is expired'; 
    $this->write_cache_file = true;
    $this->output_ob = true;
    ob_start();
    $this->debug_output['ob_started'] = 'ob started @ '. time();
 	 } # END is file does not exist or is expired 	 
  } # END if $this->is_guest  
	} # END cache_this_page method

NOTICE: all I did was add the $debug as global at the top!

[Chemo]

I have added my page cache contribution and here are the stats:

Page Parse Time: .082 seconds
Queries: 8

As of now, I only have the index.php file cached so product_info.php and others are still fully generated.  However, you can see the benefit of having the page cached.

In addition, I have documented each change to the code which can be viewed at the bottom of the page.  The info contained is: name of change, parse time, query count, and reference (download link, reference link, etc.) so that others can go directly to the contribution.

Some contributions have decreased the query count considerable and others not so much.  However, each is important for their own reason.

Next, I'll start working on the category pages and reviews.

My goal here is to get each page of the store down to about 10 queries (no more than) and sub .1 rended times.

Anyone interested in following along?

[RavenWulf]

Chemo, on Nov 1 2004, 01:07 PM, said:

I'll put it into a contribution and post it...should be a quick and install.
There are several areas that can be optimized for your particular site!  Some want the banner code whereas others don't.  Some love the hit counter at the bottom whereas others don't.  See the trend?  The stock osCommerce installation has them all in there so someone does not have to add but if they don't need them it just kills the query count.

Tell me what you want disabled and I'll give you the code to take it out...
Thank you very much   Wait until you see code that I haven't released yet that will bring the count down to 5 queries and sub .1 second page load times!
YOU ARE CORRECT!  Use a LEFT JOIN to pull the name as well and it eliminates a number of queries!

<{POST_SNAPBACK}>

Chemo, Thank you for all of this! I was looking into how to speed things up and all since I have a very modded store and want it as speedy as it can be.  On that, I am still very new at all of this (great at adding contribs and getting things working, but all the rest is still hard work), and was wondering if you could give detailed instructions of what you have done, where, and how?  such as the join function, how do I use it, where, and what does it do? (sorry I like to know more about things than just do this and it will work, I like to know why LOL)

Thank you again for all of this, I eagerly await more!

RavenWulf

[bruyndoncx]

I would but I have 2 excuses
- being a developer, so it wouldn't prove 3.
- got enable/disable categories installed

But you can have my code when you release the contribution