Post by budgetneon » Tue Sep 23, 2014 1:56 am

If you have a site with a fairly large number of either categories, or products...and you haven't disabled the count of products in each category, you've probably noticed slow page load times.

The functionality that counts the number of products in a category is a method inside the product model file (catalog/model/catalog/product.php). The reason it's slow is because it tries very hard to be accurate...accounting for products that are out of stock, products that have not yet reached their availability date, admin settings to whether to roll up subcategory products into the parent category, etc.

Assuming you have the category module widget on every page, opencart fires off one fairly heavyweight query for each category...on every page load. So, caching the results of the getTotalProducts() call speeds things up in a pretty big way. Once any page with the widget is viewed, the cache is used when other pages get loaded, for as long as the cache is valid (opencart's default cache lifetime is one hour).



Caching the results of the getTotalProducts() call using opencart's built in caching is pretty straightforward. Edit the catalog/model/catalog/product.php file, and go down the bottom of the getTotalProducts() method. For recent versions of OpenCart, you should see something like this:

Code: Select all

public function getTotalProducts($data = array()) {
    if ($this->customer->isLogged()) {
   // .... many lines of code...skip all this and go the bottom of the method:
   // the 2 lines below are what will be changed
   $query = $this->db->query($sql);

    return $query->row['total'];
}
Comment out those two lines of code, and add the 8 lines of code shown:

Code: Select all

public function getTotalProducts($data = array()) {
    // .... many lines of code...skip all this and go the bottom of the method:
   // note the 2 original lines of code commented out below.
   //$query = $this->db->query($sql);
   //return $query->row['total'];
   // add these 8 lines before the closing brace of the method.
   $cacheid='product.gettotalproducts.'.md5($sql).(int)$customer_group_id;
   $total=$this->cache->get($cacheid);
   if ($total === null ) {
       $query = $this->db->query($sql);
       $total = $query->row['total'];
       $this->cache->set($cacheid,$total);
   }
   return $total;
}
This can make a huge difference on sites with many categories and/or products. We did a full write up, with more info, and demo sites with and without this change so you can compare:

http://octurbo.com/caching-opencarts-category-counts/

If you're using chrome, compare the two demo sites (stock, and with this change) using the chrome developer tools-> network tab. Compare the load time of the html files. Note that you may have to load the site with this change twice...once to prime the cache, a second time to see the cached response time.

New member

Posts

Joined
Sat Sep 20, 2014 11:32 pm


Post by IP_CAM » Wed Sep 24, 2014 9:04 am

But make sure, not to leave it like this:

Code: Select all

   }
return $total;
}
}
BUT LIKE THIS:

Code: Select all

   }
return $total;
}
or you'll get a page error.
I screwed up first, not counting the lines...

Ernie
openshop.li/cart/

other than that: GREAT STUFF!! Probably the first approach to solve the counting problem, keeping Counting, not disabling it. Just updated my product range to 10'000 again to test it out.

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by budgetneon » Wed Sep 24, 2014 10:21 am

But make sure, not to leave it like this:
Ah, yes. I could have been more clear there. Thanks for the posted help hint.
Probably the first approach to solve the counting problem
I can't claim that. There are a couple of existing extensions that do something similar. Most are commerical/paid though, so I'm not sure exactly what approach they are taking. There's also at least one that attempts to streamline the query, here.

New member

Posts

Joined
Sat Sep 20, 2014 11:32 pm


Post by IP_CAM » Thu Sep 25, 2014 7:28 am

Update:

Actually, it's really the best thing what can happen to any OC-based Shop.
I found it originally here:

http://octurbo.com/caching-opencarts-category-counts/

and since then, I could not live without it. Congratulations to this fine Job!

Combined with a sub.domain.com - Image Setup, there is not much more really required to have an efficient Shop.
Just disabling the Product Count is just a to bad idea, especially for smaller places.

Thanks a lot!
Ernie
biogmax.ch/shop/
Last edited by IP_CAM on Sat Feb 28, 2015 10:24 am, edited 3 times in total.

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by BigSoft » Fri Feb 13, 2015 6:41 pm

For Opencart 2+ customer_group_id is not defined in that model and total may be false but not null.
Accounting for the above your code could go like this:

Code: Select all

$cacheid='product.gettotalproducts.'.md5($sql); //.(int)$customer_group_id;
$total=$this->cache->get($cacheid);
if ($total === false ) {
       $query = $this->db->query($sql);
       $total = $query->row['total'];
       $this->cache->set($cacheid,$total);
}
return $total;

Bigsoft.Gr - Greece Europe based Worldwide Opencart Development


Newbie

Posts

Joined
Fri Feb 13, 2015 6:23 pm

Post by kamran78 » Wed Mar 04, 2015 4:57 pm

Thanks for such a helpful post. I have fasten the frontend of my website however now I want to speed up backend (admin) product list all function, its taking more than 8 secs to load a list. I have few thousands products and I wish if I can speed up that function too? I am using OC 1.5.5.1 and it is heavily customized. no VQ

Any help for me!

Newbie

Posts

Joined
Tue Feb 24, 2015 6:13 pm

Post by IP_CAM » Wed Mar 04, 2015 11:26 pm

You cannot cache the Administration Section, you need actual data of all, not any cached results.
I don't think, that there is much 'surplus air' to remove...
Ernie
bigmax.ch

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by OSWorX » Wed Mar 04, 2015 11:41 pm

IP_CAM wrote:You cannot cache the Administration Section, you need actual data of all, not any cached results.
Not true at all.
Of course all can be cached - also at the backend.
New files/datas are only needed when something has changed (tasks add, remove, edit) - then the cache has to be build new..

Full Stack Web Developer :: Dedicated OpenCart Development & Support DACH Region
Contact for Custom Work / Fast Support.


User avatar
Guru Member

Posts

Joined
Mon Jan 11, 2010 10:52 pm
Location - Austria

Post by IP_CAM » Thu Mar 05, 2015 3:18 am

OSWorX wrote:New files/datas are only needed when something has changed (tasks add, remove, edit) - then the cache has to be build new..
Sorry, wasn't aware of this..., figured, it always takes a long while to reload my Product list, so, my Guess...
Ernie

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: No registered users and 18 guests