Optimization of tep_get_tax_rate()

[Guest]

This topic is for support of the Optimize tep_get_tax_rate() contribution.

 

Problem:

Each time a price is displayed on osCommerce the tax rate for that product is queried. Even if the user has set to not display taxes with price the tax rate is queried.

 

Solution:

Query for each rate one time as it is needed and then store it in session cache.

 

What this contribution does:

This code change will modify the tep_get_tax_rate() method so the first time a tax rate is requested it stores it in session. On every tax rate query it is first checked to verify if it's already in sesssion and if not will query / store in cache. If it is already in session it will return that value and save the database lookup.

 

Result:

Reduction in per page query count and faster execution times. Query reduction has been demonstrated to be around 75% savings on some pages.

 

Tested on:

8 setups with differing server configurations and osC setup. This change should not affect any other contributions.

 

Install time:

As fast as you can open a file, make one change, and upload.

 

Enjoy!

[Guest]

BTW, the contribution is located here: http://www.oscommerce.com/community/contributions,2417

[Stephan Gebbers]

Hi Chemo,

 

With your changes my shop does show the price without taxes included now.

Faster then normal but the prices are without taxes.

 

Cheerz.

Stephan

Edited September 5, 2004 by Stephan Gebbers

[Guest]

That is because by default the contribution calculates tax at 0% for guests that are not logged in.

 

How do you know that the customer is located in your store tax zone?

 

IMO, I would rather show the tax only when you have enough information from the customer to calculate it properly!

[Stephan Gebbers]

That is because by default the contribution calculates tax at 0% for guests that are not logged in.

 

How do you know that the customer is located in your store tax zone?

 

IMO, I would rather show the tax only when you have enough information from the customer to calculate it properly!

Hi Chemo,

 

in my shop every customer gets the same tax rate, no matter where (s)he comes from.

Is there a way to do it like that with your contribution?

 

Stephan

[Guest]

That is because by default the contribution calculates tax at 0% for guests that are not logged in.

 

How do you know that the customer is located in your store tax zone?

 

IMO, I would rather show the tax only when you have enough information from the customer to calculate it properly!

Hi Chemo,

 

in my shop every customer gets the same tax rate, no matter where (s)he comes from.

Is there a way to do it like that with your contribution?

 

Stephan

Sure there is...but now the values are in session so you'll have to unregister them to reset the values.

 

Change the code to this:

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') || (DISPLAY_PRICE_WITH_TAX == 'true')) { //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; 
	 }
 }

 

Also, try this handy little tool to do some quick debugging:

 

Paste this code in application bottom just above the call to close the session:

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

Activate it by setting the switch in the URL like this: mydomain.com/?output=1

Deactivate it like this: mydomain.com/?output=0

 

This will output the contents of the global $_COOKIE and $_SESSION.

 

Also, you'll have to unregister the classes from session before they will reset. Let me know if you need help doing this...

[SwissChris]

Thank your for your last post. I have installed it and it seems to run ok, but I am not all clear on where exactly it does speed up my site. I will trace it a bit further but I would like to know from you - the contributor - were exactly it has an effect on page load speed?

[Guest]

Thank your for your last post. I have installed it and it seems to run ok, but I am not all clear on where exactly it does speed up my site. I will trace it a bit further but I would like to know from you - the contributor - were exactly it has an effect on page load speed?

Each time a price is displayed...and I mean everytime, everywhere...it calls $currencies->display_price($someprice, tep_get_tax_rate($sometax_id'])). Notice the method tep_get_tax_rate() as a parameter. The method queries the database for the tax rate each time a price is called. What this contribution does is cache the tax rate and if it is already stored just returns the value thereby ELIMINATING THE QUERY. Some people mistakenly believe tht if you choose to turn display tax with price off that it also saves a query...this is false. It still queries the database and the switch to display simply does not output the tax. However, it still queries the database no matter what.

 

This contribution will have the most impact for stores that display a lot of prices on a page. For instance, if you have specials and featured products on the index page it will save the number of queries equal to the number of products shown. When in a category and there are "X" number of products per page it will save "X" number of queries.

 

In terms of speed it will finish constructing the page faster since it uses less queries. In terms of server load it is heavenly. Some people that are virtually hosted may not care how much of a server load it presents. For those that have a dedicated server it will decrease the MySQL load by as much as 75%. This means for high volume shops, like my client who receives a few thousand customers daily, that they can support 400% more simultaneous users and maintain the same load.

 

If you are concerned about query count this contribution is for you. If you could care less about server load this contribution is NOT for you.

 

Make sense?

[PopTheTop]

---

Ok, need a bit of help here on the debug thing. I installed it, activated the debug MOD too.

 

Now, all I get is this and I have no idea what it is telling me here. This was from a page loaded by a guest...

COOKIE INFO:
Array ( [osCsid] => 6050850a55c1ae89af9ed3378844a07d [popthetop_data] => a:2:{s:11:\"autologinid\";s:32:\"385822e359afa26d52b5b286226f2cea\";s:6:\"userid\";i:2;} [popthetop_sid] => dc86379a1c9278e1976b6ebf5e107720 [popthetop_t] => a:3:{i:220;i:1099282269;i:222;i:1099283226;i:223;i:1099282295;} ) 


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

SESSION INFO:
Array ( [referer_url] => http://www.popthetop.com/catalog/admin/backup.php? [cart] => shoppingcart Object ( [contents] => Array ( [53] => Array ( [qty] => 1 ) [59] => Array ( [qty] => 1 ) ) [total] => 5.48 [weight] => 3.4 [cartID] => 30193 [content_type] => [shiptotal] => 0 ) [language] => english [languages_id] => 1 [currency] => USD [navigation] => navigationhistory Object ( [path] => Array ( [0] => Array ( 

---

 => index.php [mode] => NONSSL [get] => Array ( [output] => 1 ) [post] => Array ( ) ) ) [snapshot] => Array ( 

---

 => checkout_shipping.php [mode] => SSL [get] => Array ( ) [post] => Array ( ) ) ) [autologon_executed] => true [SESSION_SSL_ID] => [output] => 1 )

 

This is from a page loaded by a customer who was logged in...

COOKIE INFO:
Array ( [osCsid] => 589e2edaafb1e8fce6c75932f67d6bc8 [popthetop_data] => a:2:{s:11:\"autologinid\";s:32:\"385822e359afa26d52b5b286226f2cea\";s:6:\"userid\";i:2;} [popthetop_sid] => dc86379a1c9278e1976b6ebf5e107720 [popthetop_t] => a:3:{i:220;i:1099282269;i:222;i:1099283226;i:223;i:1099282295;} ) 


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

SESSION INFO:
Array ( [referer_url] => http://www.popthetop.com/catalog/admin/backup.php? [cart] => shoppingcart Object ( [contents] => Array ( [53] => Array ( [qty] => 1 ) [59] => Array ( [qty] => 1 ) ) [total] => 5.48 [weight] => 3.4 [cartID] => [content_type] => physical [shiptotal] => 0 ) [language] => english [languages_id] => 1 [currency] => USD [navigation] => navigationhistory Object ( [path] => Array ( [0] => Array ( 

---

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

---

 => login.php [mode] => SSL [get] => Array ( [action] => process ) [post] => Array ( [email_address] => [email protected] [new_customer] => N [password] => XXXXXXX [remember_me] => on [x] => 31 [y] => 12 ) ) [2] => Array ( 

---

 => checkout_shipping.php [mode] => SSL [get] => Array ( ) [post] => Array ( ) ) [3] => Array ( 

---

 => shopping_cart.php [mode] => NONSSL [get] => Array ( ) [post] => Array ( ) ) [4] => Array ( 

---

 => product_info.php [mode] => NONSSL [get] => Array ( [products_id] => 59 ) [post] => Array ( ) ) ) [snapshot] => Array ( ) ) [autologon_executed] => true [SESSION_SSL_ID] => [output] => 1 [customer_id] => 1 [customer_default_address_id] => 1 [customer_first_name] => Ginny [customer_country_id] => 223 [customer_zone_id] => 36 [sendto] => 1 [tax_1_customer] => 7.1 [cartID] => [tax_0_customer] => 0 )

 

 

Can you tell by looking at it if it is working or not.

Edited November 1, 2004 by PopTheTop

[Guest]

Did you install the latest version?

 

The first version set the tax rate via session. The second version uses the MS3 tax class modified for MS2 codebase. It is MUCH better and should be very easy to install.

 

I would HIGHLY recommend you install the latest version!

[♥bruyndoncx]

I second that, installing MS3 tax class is very easy.

My fastest index.php load is 102ms thus far ... getting close :D (though categories pages are still a bit slower, evidently)

[Guest]

bruyndoncx,

 

Install the Queries and Parse time contribution (so you can have some numbers to judge the increase) and then get some baseline numbers.

 

Next, install the page cache and check the numbers again...I bet you'll be down to about 10 queries and sub .1 second load times.

 

Give me some feedback and tell me how it works for you...

 

BTW, the links are in my sig.

[♥bruyndoncx]

Install the Queries and Parse time contribution (so you can have some numbers to judge the increase) and then get some baseline numbers.

yep, have gotten that one enabled since a few weeks

 

Next, install the page cache and check the numbers again...I bet you'll be down to about 10 queries and sub .1 second load times.

downloaded it already, trying to keep up with you :D

Give me some feedback and tell me how it works for you...

Will do

[PopTheTop]

I installed the MS3 tax class. It appears to be loading a lot faster. Thanks!

[Guest]

I installed the MS3 tax class. It appears to be loading a lot faster. Thanks!

<{POST_SNAPBACK}>

The question is how much faster? :)

 

Install the debug tool and find out!

[Ddarek]

Hello Chemo

For those who have 1 tax , no matter from,how etc etc., and no chsance for 0 tax. Is your latest version of tep_get_tax is suiteable for that?

As above for your previous version you modified your method and set

(DISPLAY_PRICE_WITH_TAX == 'true')

Can you do that for this one ?

[Guest]

Hello Chemo

For those who have 1 tax , no matter from,how etc etc., and no chsance for 0 tax. Is your latest version of tep_get_tax is suiteable for that?

As above for your previous version you modified your method and set

Can you do that for this one ?

<{POST_SNAPBACK}>

Yes...

 

Let's say there is only one tax. Every time a price is displayed it queries the database and returns that one tax rate. This is in case a product has a different tax or when a customer is logged in the zones can be appied.

 

What the tax class does is query for the rate and store it in an array. On each request for the tax rate it FIRST checks to see if it is already stored. If so it returns the value thereby skipping the query. If it is NOT stored it will query then store it in the array.

[MikeMike]

I am using an older snapshot from 10/2002.

 

Can anyone tell me how I make the changes in functions/general.php?

 

From install instructions:

STEP 3 - Edit includes/functions/general.php

REPLACE tep_get_tax_rate() it with this code:

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

global $customer_zone_id, $customer_country_id, $osC_Tax;

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

}

 

REPLACE tep_get_tax_description() it with this code:

function tep_get_tax_description($class_id, $country_id, $zone_id) {

global $osC_Tax;

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

}

 

My general.php:

 

////
// Returns the tax rate for a zone / class
// TABLES: tax_rates, zones_to_geo_zones
 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;
     }
   }

   $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 = '" . $country_id . "') AND (za.zone_id IS NULL OR za.zone_id = '0' OR za.zone_id = '" . $zone_id . "') AND tr.tax_class_id = '" . $class_id . "' GROUP BY tr.tax_priority");
   if (tep_db_num_rows($tax_query)) {
     $tax_multiplier = 0;
     while ($tax = tep_db_fetch_array($tax_query)) {
       $tax_multiplier += $tax['tax_rate'];
     }
     return $tax_multiplier;
   } else {
     return 0;
   }
 }

////
// Return the tax description for a zone / class
// TABLES: tax_rates;
 function tep_get_tax_description($zone_id, $class_id) {
   $tax_query = tep_db_query("select tax_description from " . TABLE_TAX_RATES . " where tax_zone_id = '" . $zone_id . "' and tax_class_id = '" . $class_id . "'");
   if (tep_db_num_rows($tax_query)) {
     $tax = tep_db_fetch_array($tax_query);
     return $tax['tax_description'];
   } else {
     return TEXT_UNKNOWN_TAX_RATE;
   }
 }

////

Edited November 7, 2004 by MikeMike

[Guest]

Delete both of those functions and replace with the revised functions.

 

Make sure that the class is initiated in application_top.php

 

There is no difference in install between the snapshots.

[richhoward]

It seems that all of my urls for my products are going to http://yourdomain.com/product_info.php/id/ and i have looked at each file and cant find why it's doing this. I guess I feel stupid, but somebody please help!

[Guest]

Turn off SEF URL's in the admin control panel configuration:

 

Admin Control Panel -> Configuration -> SEF URL's -> false

[clarocque]

Has anyone installed this with CCGV installed?

 

If so are you still able to checkout using just a GV for an order that has Tax?

 

I am being forced to choose a payment option even though there is enough money in account, but only when there is tax??????

[Guest]

The tax class replaces the stock osC functions and will not affect CCGV.

 

Have you posted in the CCGV support thread?

 

Bobby

[clarocque]

Yes, have not heard back - just wasn't sure if it could affect it... thanks

[Guest]

Hello ... this sounds like a nice contribution ... I'm trying to install it right now but I have a quick question on step 3 and replacing tep_get_tax_rate() and tep_get_tax_description() ... I'm pretty sure its not just the lines with those on it but I wanted to make sure I'm understanding right and that I replace all of the following code ...

 

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

? ?$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);
? ? ?}
? ? ?return ($tax_multiplier - 1.0) * 100;
? ?} else {
? ? ?return 0;
? ?}
?}

... and ...

 

 ?function tep_get_tax_description($class_id, $country_id, $zone_id) {
? ?$tax_query = tep_db_query("select tax_description 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 . "' order by tr.tax_priority");
? ?if (tep_db_num_rows($tax_query)) {
? ? ?$tax_description = '';
? ? ?while ($tax = tep_db_fetch_array($tax_query)) {
? ? ? ?$tax_description .= $tax['tax_description'] . ' + ';
? ? ?}
? ? ?$tax_description = substr($tax_description, 0, -3);

? ? ?return $tax_description;
? ?} else {
? ? ?return TEXT_UNKNOWN_TAX_RATE;
? ?}
?}

Thanks for reading this,

Danny

Edited January 17, 2005 by FIDesigns