Shopping cart takes forever to load

[krnl]

Depending on the number of items in a user's shopping cart, it can take anywhere from 30 seconds to 2 minutes for the shopping cart to be displayed. I would like to optimize this a bit ("a bit" heh...2 minute page load is ridiculous!).

 

One thing that would cut the load time in half is an "IF" check in column_right.php that checks to see if the requested page is "shopping_cart.php" and if so, do not display the shopping_cart box in the right column. That will cut the shopping cart queries in half. how can I go about doing this?

 

Also, are there some common settings that I should check in order to make this run a little smoother? I have "use cache" set to yes, I'm storing my sessions in mysql...I have turned stock_check off in an attempt to speed things up, but that didn't help..

 

Please help me optimize my setup so I can speed up my page loads (specifically the shopping cart).

 

You can view my site at http://all-in-general.com if you want to experience how it feels to give up on a purchase because the website you're on is too slow.

 

Any assistance would be greatly appreciated.

[realhotstuff]

Depending on the number of items in a user's shopping cart, it can take anywhere from 30 seconds to 2 minutes for the shopping cart to be displayed. I would like to optimize this a bit ("a bit" heh...2 minute page load is ridiculous!).

 

One thing that would cut the load time in half is an "IF" check in column_right.php that checks to see if the requested page is "shopping_cart.php" and if so, do not display the shopping_cart box in the right column. That will cut the shopping cart queries in half. how can I go about doing this?

 

Also, are there some common settings that I should check in order to make this run a little smoother? I have "use cache" set to yes, I'm storing my sessions in mysql...I have turned stock_check off in an attempt to speed things up, but that didn't help..

 

Please help me optimize my setup so I can speed up my page loads (specifically the shopping cart).

 

You can view my site at http://all-in-general.com if you want to experience how it feels to give up on a purchase because the website you're on is too slow.

 

Any assistance would be greatly appreciated.

 

I would backup the database by using PHPMySQL, delete the database, re-create the database with the same database name and user, then restore the data by using the backup.

 

Sometimes, the database get corrupted, if you are on a shared hosting, it might not be you. I had one hosting that happened to me and I ended up moving to a different hosting because they could not find a fix.

[kgt]

Also keep in mind that slow DNS resolution for the mysql server can make initially connecting to the database a problem. If you're on a shared host, the database server may be reaching it's limits. If the problem is slow queries, your host might be willing to switch you to a mysql server with smaller demand.

 

Your host may not have the mysql server properly configured, and the database server may be struggling with the load. How many products do you have?

[krnl]

Also keep in mind that slow DNS resolution for the mysql server can make initially connecting to the database a problem. If you're on a shared host, the database server may be reaching it's limits. If the problem is slow queries, your host might be willing to switch you to a mysql server with smaller demand.

 

Your host may not have the mysql server properly configured, and the database server may be struggling with the load. How many products do you have?

 

I have almost 11,000 active products and about 18,000 products total. I am running on a shared hosting plan and the server that my site is on is a 4 CPU linux setup. When I run 'top', mysql is eating 95%+ of one of the CPUs when my pages are trying to load. This can't be good :-/ I have thought about switching hosting providers, but would much rather not mess with it since I have 4 domains on these guys...ugh.

 

I don't think it's a DNS issue, because the server is on localhost, so it's a loopback tcp connection.All I know at this point is that this problem is causing me to lose business...

 

Someone told me to disable category counts..which helped a bit on normal page loading time, but does not affect the loading of the shopping cart. Is there anything else I can do in the configuration to try to correct this issue?

 

I suppose recreating and repopulating the database wouldn't be too difficult, but what would that really do since I would be putting the exact same database back into place?

 

Thanks for the suggestions so far...I would love to hear more.

[krnl]

I realized how to suppress the shopping_cart box if the user is on shopping_cart.php ...

 

Just change column_right.php from this:

 

  require(DIR_WS_BOXES . 'shopping_cart.php');

 

to this:

 

if($PHP_SELF != '/shopping_cart.php')
 require(DIR_WS_BOXES . 'shopping_cart.php');

 

This certainly helped the situation...shopping_cart.php load time is cut in half since it only has to load once.

[Guest]

the latency I believe its not due to the database. Looks like acessing images from external sites. Try creating a test page to see what's going on. Comment out the left/right columns first see if you still having the long delay. Then comment out the footer and check again. If it's really the categories box for instance you could find the code for the particular box and optimize it.

[ozcsys]

You may also want to check out the thread found HERE

[krnl]

You may also want to check out the thread found HERE

 

Good information there. I installed the Query Logging and the Optimized tep_get_tax_rate contribs. The tax thing saved me about a half second...but an optimization, nonetheless.

 

The query logging indicates that my shopping cart product queries are taking between 2 and 6 seconds to parse!

 

After digging around a bit, it looks like the query that's contained in the loadProduct() function in .../includes/PriceFormatter.php.

 

Here is the query itself:

 

$sql="select pd.products_name, p.products_model, p.products_image, p.products_id," .
	" p.manufacturers_id, p.products_price, p.products_weight," .
	" p.products_price1,p.products_price2,p.products_price3,p.products_price4," .
	" p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri
ce4_qty," .
	" p.products_qty_blocks," .
	" 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 " . TABLE_PRODUCTS_DESCRIPTION . " pd," .
	" " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id," .
	" " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .
	" where p.products_status = '1'" .
	" and p.products_id = '" . (int)$product_id . "'" .
	" and pd.products_id = '" . (int)$product_id . "'" .
	" and pd.language_id = '". (int)$language_id ."'";

 

There are 6 items in my cart...It seems that for each of those items, there are three seperate calls to the PriceFormatter for each item, all of which are taking 2+ seconds to complete. This equates to EIGHTEEN two+ second queries! Every query is *exactly the same* (except, of course, for the product_id for each product).

 

- - -

in file .../includes/classes/shopping_cart.php

in function calculate()

if ($product = $pf->loadProduct($products_id, $languages_id)){

 

in function getProducts()

if ($products = $pf->loadProduct($products_id, $languages_id)) {

 

- - -

and in .../shopping_cart.php

$pf_obj->loadProduct($products_got[$i]['id']);

 

First off, does everyone have 2 second+ query times when loading the shopping cart? To test it, just add 5 or 6 items to your cart and see if it takes upwards of a minute for your cart to render when you visit shopping_cart.php

 

Second...can these three calls to the pf->loadProducts() function be optimized in a way that only one query is needed?

[krnl]

Since there are three of these queries being executed for each product in a user's shopping cart, how could the results of the first query for each product be cached to a file and force the second and third queries to read from the cache file instead of re-querying? and would this increase the speed at which the cart loads?

 

Also...is this an osCommerce problem, or do I possibly have corruption somewhere in my DB that is causing these lengthy queries?

 

Query times (in seconds) for the queries listed below. Note that two products_id's result in 6 queries that collectively take over 18 seconds...just to list two products in a shopping cart.

 

[8] => 4.021766 products_id 1108

[9] => 3.532945 products_id 588

[35] => 2.885252 products_id 1108

[36] => 2.664723 products_id 588

[37] => 2.713130 products_id 1108

[38] => 2.651155 products_id 588

 

[8] => select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_qty_blocks, 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 = '1108' and pd.products_id = '1108' and pd.language_id = '1'

[9] => select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_qty_blocks, 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 = '588' and pd.products_id = '588' and pd.language_id = '1'

[35] => select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_qty_blocks, 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 = '1108' and pd.products_id = '1108' and pd.language_id = '1'

[36] => select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_qty_blocks, 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 = '588' and pd.products_id = '588' and pd.language_id = '1'

[37] => select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_qty_blocks, 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 = '1108' and pd.products_id = '1108' and pd.language_id = '1'

[38] => select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_qty_blocks, 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 = '588' and pd.products_id = '588' and pd.language_id = '1'

[kgt]

Whoa, take a look at that query!

 

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_qty_blocks, 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 = '1108' and pd.products_id = '1108' and pd.language_id = '1'

 

 

This query never specifies a join condition for the products_to_categories join! That means mysql is joining records for the entire products_to_categories table. I bet if you run this query on your mysql command line client, you'll get a result set that is the same size as your products_to_categories table.

[krnl]

Yeah, I thought something looked a bit funky there. I'm decent with mysql, but my join'ing leaves much to be desired. How can this be fixed? I see no reason for this query to even need to look at the products_to_categories table....

 

What needs to be removed in order to get rid of the p2c part and leave the rest of the query syntactically correct?

[kgt]

Remove the comma before products_to_categories, and remove "products_to_categories p2c".

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.products_price, p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_qty_blocks, 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 left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = '1108' and pd.products_id = '1108' and pd.language_id = '1'

[krnl]

YEP!! I just did that before coming back to the post and it works like a charm! It's blazing fast now!! Thank you so much kgt!

 

How did that p2c stuff get into that query? Is that how it is in your .../catalog/includes/classes/PriceFormatter.php ???

 

or

 

am I the victim of file corruption?

 

The corrected query is as follows:

 

$sql="select pd.products_name, p.products_model, p.products_image, p.products_id," .

" p.manufacturers_id, p.products_price, p.products_weight, p.products_price1,p.products_price2,p.products_price3,p.products_price4," .

" p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty," .

" p.products_qty_blocks, 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 " . TABLE_PRODUCTS_DESCRIPTION . " pd," .

" " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id" .

" left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .

" where p.products_status = '1'" .

" and p.products_id = '" . (int)$product_id . "'" .

" and pd.products_id = '" . (int)$product_id . "'" .

" and pd.language_id = '1'";

[kilroy13]

I hope this will fix the problem I have had on my site. It was taking FOREVER to load the shopping cart or to do a checkout, and it came down to the priceformatter contribution. We were using the priceformatter to deal with quantity discounts. I pulled that code out and it was working great, but we would really like quantity breaks fast. So I'm gonna try this tonight.

 

Thanks for info, hope it works for me too!