chrometweaks.org

I am looking for cheap iPage web hosting for my website?

Click Here To View All Answers...


Question I have... I am looking for cheap iPage web hosting for my website? Thanks in advance for any response. Another question on my mind: A vanilla install with featured products, specials, affiliates, articles, coupons, and other additions will typically execute about 100 queries per page. This was unacceptable as my clients store receives thousands of customers daily...I could see the MySQL server sweating under the load.

So, I started looking for ways to get that number down without losing functionality. I first searched the forum and found everything related to "optimization". I implemented table indexes, configuration cache, coded a custom image thumbnailer (basically for the GIF's), and of course turn the category counts off. Those changes did not affect the actual number of queries used to construct the pages so my search continued..

The next thing I did was to create a quick output script to see each query used on each page. This was handy as I immediately noticed several identical queries...so it was time to figure out how to combine them..

The root of the "excessive query problem" was in the call to tep_get_tax_rate method. Each time a product price is displayed it calls the method. Basically, I changed the function to look like this (changes in bold):.

 function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) {.

   global $customer_zone_id, $customer_country_id;.

   if ( ($country_id == -1) && ($zone_id == -1) ) {.

     if (!tep_session_is_registered('customer_id')) {.

       $country_id = STORE_COUNTRY;.

       $zone_id = STORE_ZONE;.

     } else {.

       $country_id = $customer_country_id;.

       $zone_id = $customer_zone_id;.

     }.

   }.

If (!tep_session_is_registered('customer_tax')) {.

 $tax_query = tep_db_query("select sum(tax_rate) as tax_rate from "TABLE_TAX_RATES" tr left join "TABLE_ZONES_TO_GEO_ZONES" za on (tr.tax_zone_id = za.geo_zone_id) left join "TABLE_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 = '"(int)$country_id"') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '"(int)$zone_id"') and tr.tax_class_id = '"(int)$class_id"' group by tr.tax_priority");.

 if (tep_db_num_rows($tax_query)) {.

   $tax_multiplier = 1.0;.

   while ($tax = tep_db_fetch_array($tax_query)) {.

   $tax_multiplier *= 1.0 + ($tax['tax_rate'] / 100);.

     }.

 tep_session_register('customer_tax');.

 $customer_tax = ($tax_multiplier - 1.0) * 100;.

   return ($tax_multiplier - 1.0) * 100;.

 } else {.

   tep_session_register('customer_tax');.

 $customer_tax = 0;.

   return 0;.

 }.

}//End if customer tax not registered.

Return $_SESSION['customer_tax']; //if tx is registered return what's in session.

 }.

The result: the count dropped from 98 queries down to 35 on the index page. Similiar improvements were made across each page on the store..

The intent here is to query for the info once and then present that each time instead of running it through the database for each page. Tax is being calculated / recorded correctly and the speed improvement is astounding..

Thoughts? Feedback?.

BTW, the query count is displayed on each page at the bottom. You can check it out here:.

Client index page.

..

Bobby..

Comments (178)

I'm stumped. I'm not so sure what is the answer. I'll do some poking around and get back to you if I got an anything. You should email the people at iPage as they probably could assist you..

Comment #1

If you're not showing prices with tax, will this query have much effect?..

Comment #2

This change assumes that all products use the same tax class..

If you use different tax rates for different products it will not work...

Comment #3

Thanks Mark..

That's a pity.

I like the basis behind the idea, but you are right. Here in the UK it's possible to have 0%, 6% and 17.5% in one shop, so it wouldn't work.....

Comment #4

Each time a price is presented for a product no matter where it is on the cart the following are called:.

$currencies->display_price( $price,.

Tep_get_tax_rate($id).

).

- tep_get_tax_rate() = 1 query just to get going.

$this->display_price() calls $this->format which then calls.

Tep_add_tax().

This is the method that determines whether tax is displayed but there are no database queries. It is merely an option of displaying it or not...the query was already made with tep_get_tax_rate()..

Remember, the intent here is to query the info once and reuse that throughout the session. The current routine is to query the database on each price render...and I mean EVERYWHERE that a price is rendered..

So, to answer your question...it will decrease your query count as well. Even though you have the display with tax turned off it still gets it from the database on each price render...just doesn't show it..

The only thing I can see that will throw a monkey wrench is if a store has different tax classes. For instance, professional services are sold as well as products. However, in the case of my client and 99% of the stores using osC all products are either taxable or not...

Comment #5

You beat me to the punch....

I posted the multi-tax class shop disclaimer above..

It won't work for those but for one tax class stores this change will cut their query count by 2/3rds. It'll be faster and less resource intensive especially for high volume stores...

Comment #6

OK...how about this for a quick code revision:.

Basically, on each price call it checks to see if the class_id is registered. If not, it will execute the query and register the class_id. Subsequent calls are first checked to see if registered and if so use the session data..

So, this should do the same thing but now incorporate ability to support multiple tax classes.

Function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) {.

   global $customer_zone_id, $customer_country_id;.

   if ( ($country_id == -1) && ($zone_id == -1) ) {.

     if (!tep_session_is_registered('customer_id')) {.

       $country_id = STORE_COUNTRY;.

       $zone_id = STORE_ZONE;.

     } else {.

       $country_id = $customer_country_id;.

       $zone_id = $customer_zone_id;.

     }.

   }.

If (!tep_session_is_registered($class_id)) {.

 $tax_query = tep_db_query("select sum(tax_rate) as tax_rate from "TABLE_TAX_RATES" tr left join "TABLE_ZONES_TO_GEO_ZONES" za on (tr.tax_zone_id = za.geo_zone_id) left join "TABLE_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 = '"(int)$country_id"') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '"(int)$zone_id"') and tr.tax_class_id = '"(int)$class_id"' group by tr.tax_priority");.

 if (tep_db_num_rows($tax_query)) {.

   $tax_multiplier = 1.0;.

   while ($tax = tep_db_fetch_array($tax_query)) {.

   $tax_multiplier *= 1.0 + ($tax['tax_rate'] / 100);.

     }.

 tep_session_register($class_id);.

 $class_id = ($tax_multiplier - 1.0) * 100;.

   return ($tax_multiplier - 1.0) * 100;.

 } else {.

   tep_session_register($class_id);.

 $class_id = 0;.

   return 0;.

 }.

}//End if customer tax not registered.

Return $_SESSION[$class_id];.

 }.

Thoughts? Feedback?.

This post has been edited by.

Chemo.

: 04 September 2004, 16:16..

Comment #7

The code above should work for you...can you make a quick output tool to verify session registration? I know it works for single tax class shops but want to test it on multi class shops before releasing it as a contribution..

Thank you!..

Comment #8

Have you run the above code?.

Just looking at it, I dont think it works..

I better suggestion would be to take a look at the current CVS.. we have fixed this by the creation of a tax class...

Comment #9

Chemo, I am curious if you are importing user data from osc to vbull or the reverse. I like this new version of vbull and was wondering if you found an easy way to integrate users or are you running from the lost password lists? Thanks!..

Comment #10

From initial testing, this does seem to work! I'll spend some more time testing tomorrow and let you know how it goes..

Good work Chemo...

Comment #11

I tested the code and works with at least 3 tax classes. The rates are being registered correctly along with the values (I have an output script that simply print_r's the session, cookie, and query list)...

Comment #12

Neither...I simply pull the latest threads, cache the data, and output on the index...

Comment #13

Try this simple script to output the cookie and session data. What it does is check to see if the output switch is set by URL and if so outputs the data. This way the average customer won't see the output but you can easily check it..

To use, paste this code in application_bottom.php and activate it by including ?output=1 in your URL. For instance, mydomain.com/?output=1 will turn it on and mydomain.com/?output=0 will turn it off..

Be sure to paste it above the session_close.

If ($_REQUEST['output'] == '0') $_SESSION['output'] = '0';.

If ($_REQUEST['output'] == '1' || $_SESSION['output'] == '1').

 {.

 $_SESSION['output'] = '1';.

 echo '<p><b>COOKIE INFO:</b><br>';.

 print_r($_COOKIE).'</p>';.

 echo '<hr>';.

 echo '<p><b>SESSION INFO:</b><br>';.

 print_r($_SESSION).'</p>';.

 }.

Once you get it pasted and saved activate it by setting the switch in the URL. Each class should be listed in the session output. For instance, 1 => *whatever*..

Comment #14

OK...here is the code that has been tested on 4 live stores with different setups..

This version supports virtually unlimited tax classes and registers them only when the customer is logged in. Vistors will always get a tax rate of zero and the query is eliminated. When they log in each tax class will be queried for once and then pulled from session thereafter..

This version is commented....

If there are no objections I'll post this in the contribution area..

Enjoy!.

Function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) {.

   global $customer_zone_id, $customer_country_id;.

   if ( ($country_id == -1) && ($zone_id == -1) ) {.

     if (!tep_session_is_registered('customer_id')) {.

       $country_id = STORE_COUNTRY;.

       $zone_id = STORE_ZONE;.

     } else {.

       $country_id = $customer_country_id;.

       $zone_id = $customer_zone_id;.

     }.

   }.

$classname = 'tax_'.$class_id; //Unique session name for the tax class.

If (tep_session_is_registered('customer_id')) { //Is the customer_id registered?.

 $classname .= '_customer'; //Add _customer to the name since it passed the check.

 if (!tep_session_is_registered($classname)) { //Is the classname already registered?.

 $tax_query = tep_db_query("select sum(tax_rate) as tax_rate from "TABLE_TAX_RATES" tr left join "TABLE_ZONES_TO_GEO_ZONES" za on (tr.tax_zone_id = za.geo_zone_id) left join "TABLE_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 = '"(int)$country_id"') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '"(int)$zone_id"') and tr.tax_class_id = '"(int)$class_id"' group by tr.tax_priority");.

 if (tep_db_num_rows($tax_query)) { //If there are taxes....

    $tax_multiplier = 1.0;.

    while ($tax = tep_db_fetch_array($tax_query)) {.

     $tax_multiplier *= 1.0 + ($tax['tax_rate'] / 100);.

   }    .

   $_SESSION["$classname"] = ($tax_multiplier - 1.0) * 100; //Used the global $_SESSION.  Is there a way to use native API?    .

   return ($tax_multiplier - 1.0) * 100;.

   } else { // There are no taxes so just return 0      .

     $_SESSION["$classname"] = 0; //Used the global $_SESSION.  Is there a way to use native API?    .

     return 0;.

   }.

 } else {  //The class is registered in session so return that.

   return $_SESSION["$classname"];.

   }  .

} else { //The visitor is a guest so output 0.

 return 0;.

 }.

 }..

Comment #15

Hi.

I would like to object just a little.

Not all shops show prices excluding tax for guests, so this wouldn't be good ....

Could you build in the check for this setting ? .

Thanks..

Comment #16

Btw, it's DISPLAY_PRICE_WITH_TAX true/false setting in admin/configuration/Store settings that keeps track of this..

Comment #17

How does the shopping cart know what tax rate to show if they are not customers and hence have no record of address?.

How do you currently display tax to visitors?..

Comment #18

Assumption is that they are all local visitors:.

Quoting from your code.

    if (!tep_session_is_registered('customer_id')) {.

      $country_id = STORE_COUNTRY;.

      $zone_id = STORE_ZONE;.

    } else {..

Comment #19

That part of the code means very little as it never gets to the query if there is no customer_id..

I tell you what...when we get all the bugs worked out of this one I'll throw one together that will do exactly what you want...

Comment #20

Great code - certainly helps with speeds a lot! I think that the inclusion of DISPLAY_PRICE_WITH_TAX true/false is an absolute must - as it is an integral part of how osCommerce works....

Anyway, whether or not that does get added,.

Thanks.

For your excellent work on this...

Comment #21

The good news is guys that this has already been done in MS-3 with the tax class so as soon as MS-3 is out you wont need to upgrade this code to work..

Comment #22

I understand that MS3 makes this a moot point but for the thousands of live shops on MS2 code base that will not upgrade the simple change will cut their query count by 2/3rds..

Maybe instead of using sessions we should code a simple custom tax class for MS2? Or, serialize the data? What are your thoughts?..

Comment #23

Hi All.

Interesting topic, which also appears to be a must to have..

How do the likes of me, ie, no idea how to use this or where to put it go on..

Any help with implementing this mod into our iPage site appreciated..

Plenty of discussion about the code, but I can't see where it's supposed to go..

TIA.

Julian..

Comment #24

That's because I haven't released it yet as a contribution.

Basically, I wanted to target those that are familiar with the osC API to make the changes and test for effectiveness before releasing it to the masses. I'd rather handle support for 10-15 experienced coders than 100-150 newbies. After we get it hammered out I'll release it.....

Comment #25

Whether or not you display price with tax it will help with cutting the queries down..

For example, let's say a shop is NOT displaying price with tax. The database lookup is still executed but simply not output to the customer. Just because it is not displayed with tax DOES NOT mean that the tax was not looked up..

It doesn't matter if a shop displays with tax or not...this contribution should cut the queries down by a significant amount...

Comment #26

For those that care to follow...I have gotten the query count down to 25 with a very simple change..

In includes/modules/default_specials.php comment out this line:.

$default_specials['products_name'] = tep_get_products_name($default_specials['products_id']);.

This works on my clients setup as specials are only shown on the index page and not per category. If you show specials elsewhere do not use this!.

However, if you do use it the change will save one query per product shown...

Comment #27

It shouldnt be too difficult to back port the class to MS-2...

It would make the contribution harder to install though as a large number of changes would need to be made..

I think the solution you have posted should be sufficent for most MS-2 users as long as all possible scenario's are tested..

Comment #28

Has anything been done within the MS3 code to reduce the number of queries within the application?..

Comment #29

Yep.

We have a new tax class which does something similar to what chemo has done with his mod.

And with the DB class and it's caching features the pages load before you have even asked for them..

Comment #30

This I know.

Yes....

It matters quite a lot in a "business to business" or "business to customer" scenario, but no matter, you're not getting where I am coming from I think..

Anyway, thanks for this code - it's very good and I will be making good use of it on a number of Stores..

Thanks...

Comment #31

If anyone is interested I also have some optimizations for default_specials, also_purchased, articles, and a few others that have brought my clients queries down even more...most pages are below 30 queries and sub .4 sec times..

As a quick down and dirty for the coders: look at the default_specials and also_purchased modules. Instead of using the tep_get_product_name() method why not just get the product name directly from the query?.

In the case of my client, this saved an additinal 10-12 queries! Now it's down to about 25 queries on the index page with no loss in functionality...

Comment #32

Sorry for the newbie question, but where do I put the code that is listed in original post? By the way, I don't use/show taxes ever, but from what I read I should still implement this code, correct?..

Comment #33

I've released this a contribution here:.

Http://www.oscommerc...tributions,2417.

The support for this contribution is located here:.

Http://forums.oscomm...howtopic=110585.

Now, to answer your question...you should still implement the code. It will save queries regardless of whether or not you display tax to the customer...

Comment #34

OK I just used this contrib and WOW. My iPage site is at the very least 5 times faster. Great addition!!! Thanks again..

Comment #35

A vanilla install with featured products, specials, affiliates, articles, coupons, and other additions will typically execute about 100 queries per page. This was unacceptable as my clients store receives thousands of customers daily...I could see the MySQL server sweating under the load.

So, I started looking for ways to get that number down without losing functionality. I first searched the forum and found everything related to "optimization". I implemented table indexes, configuration cache, coded a custom image thumbnailer (basically for the GIF's), and of course turn the category counts off. Those changes did not affect the actual number of queries used to construct the pages so my search continued..

The next thing I did was to create a quick output script to see each query used on each page. This was handy as I immediately noticed several identical queries...so it was time to figure out how to combine them..

The root of the "excessive query problem" was in the call to tep_get_tax_rate method. Each time a product price is displayed it calls the method. Basically, I changed the function to look like this (changes in bold):.

 function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) {.

   global $customer_zone_id, $customer_country_id;.

   if ( ($country_id == -1) && ($zone_id == -1) ) {.

     if (!tep_session_is_registered('customer_id')) {.

       $country_id = STORE_COUNTRY;.

       $zone_id = STORE_ZONE;.

     } else {.

       $country_id = $customer_country_id;.

       $zone_id = $customer_zone_id;.

     }.

   }.

If (!tep_session_is_registered('customer_tax')) {.

 $tax_query = tep_db_query("select sum(tax_rate) as tax_rate from "TABLE_TAX_RATES" tr left join "TABLE_ZONES_TO_GEO_ZONES" za on (tr.tax_zone_id = za.geo_zone_id) left join "TABLE_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 = '"(int)$country_id"') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '"(int)$zone_id"') and tr.tax_class_id = '"(int)$class_id"' group by tr.tax_priority");.

 if (tep_db_num_rows($tax_query)) {.

   $tax_multiplier = 1.0;.

   while ($tax = tep_db_fetch_array($tax_query)) {.

   $tax_multiplier *= 1.0 + ($tax['tax_rate'] / 100);.

     }.

 tep_session_register('customer_tax');.

 $customer_tax = ($tax_multiplier - 1.0) * 100;.

   return ($tax_multiplier - 1.0) * 100;.

 } else {.

   tep_session_register('customer_tax');.

 $customer_tax = 0;.

   return 0;.

 }.

}//End if customer tax not registered.

Return $_SESSION['customer_tax']; //if tx is registered return what's in session.

 }.

The result: the count dropped from 98 queries down to 35 on the index page. Similiar improvements were made across each page on the store..

The intent here is to query for the info once and then present that each time instead of running it through the database for each page. Tax is being calculated / recorded correctly and the speed improvement is astounding..

Thoughts? Feedback?.

BTW, the query count is displayed on each page at the bottom. You can check it out here:.

Client index page.

..

Bobby..

Comment #36

I have just implemented this tip. The speed increase is phenomonal..

I'd ask that a member of the Core Team have a look at this and make comment to see if they foresee any problems using it on MS2. Thanks in advance!.

Chemo.

: Many thanks for this Tip...

Comment #37


This question was taken from a support group/message board and re-posted here so others can learn from it.