Author Topic: Speed!  (Read 1124 times)


  • Contributing Developer
  • Full Member
  • *
  • Posts: 872
    • Squangle ltd
« on: February 22, 2011, 16:23:20 pm »
G'day all,

We have an older site and I have been struggling to make it go faster.
We have 167743 (ish) products in 613 categories.

It was taking about 9 seconds to bring up a product.

We have two modules/plugins/filters/whatever that seem to affect this.
sef_servicemap2 and sh404sef.

Don't use it creates duplicate URLs not good for search engines. It is also dog slow, there is statement in there that tries to return all of our 16743 products in a table, that is just never going to work, it is not scalable.

I have not got rid of it yet, but I am going to write something simple in perl, it is way over complicated for what it does. I also tries to delete everything from the cache table every 15 seconds and recreate the table, not good. I have increased that time to 86400, be careful that every time you go into the admin end it rewrites your robots.txt so even if you remove it as a sitemap it will add itself again.

This is one of the last free versions, nothing really wrong with it and does a lot of useful stuff.
Turn off the URL cache, I think it is broken. With the number of product we have it was slower than reading from the DB, probably a memory issue.
I  Think it too is overly complicated but does seem to be efficient where as sef_servicemap is not.

So turning the cache URL feature of knocked about 3 seconds off the select time.

I managed to get another 3 seconds off by removing a chunk of sql.
There is a very very bad bit of sql in ./administrator/components/com_virtuemart/classes/ps_product.php
Code: [Select]
SELECT `product_name`,`products_per_row`,`category_browsepage`,`category_flypage`,`#__{vm}_category`.`category_id`,
>.>.>.>.`#__{vm}_product`.`product_id`,`product_full_image`,`product_thumb_image`,`product_s_desc`,`product_parent_id`,`product_publish`,`product_in_stock`,`product_sku`, `product_url`,
>.>.>.>.`product_weight`,`product_weight_uom`,`product_length`,`product_width`,`product_height`,`product_lwh_uom`,`product_in_stock`,`product_available_date`,`product_availability`,`#__{vm}_product`.`mdate`, `#__{vm}_product`.`cdate` FROM (`#__{vm}_product`, `#__{vm}_category`, `#__{vm}_product_category_xref`,`#__{vm}_shopper_group`) LEFT JOIN `#__{vm}_product_price` ON `#__{vm}_product`.`product_id` = `#__{vm}_product_price`.`product_id` WHERE `#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`
 AND `#__{vm}_product_category_xref`.`category_id`=`#__{vm}_category`.`category_id`
 AND `#__{vm}_product_category_xref`.`category_id`=235
 AND ((`#__{vm}_product`.`product_id`=`#__{vm}_product_price`.`product_id` AND `#__{vm}_shopper_group`.`shopper_group_id`=`#__{vm}_product_price`.`shopper_group_id`) OR `#__{vm}_product_price`.`product_id` IS NULL)
 AND `#__{vm}_shopper_group`.`default` = 1
 AND ((`product_parent_id`='0') OR (`product_parent_id`=''))
 AND `product_publish`='Y'
 AND `category_publish`='Y'
 ORDER BY `#__{vm}_product`.`product_name` ASC LIMIT 0, 2000

The above bit of code just gets the next and previous products so you can go 'left' or right'. So why does it need to get the product price and all that other stuff? Well, I suspect it has been copied from another bit of code. If you look at the select you will see that it gets product_in_stock twice and there is another bit of sql that does the exact same thing. It also does not work if you have more than 2000 products in a cat. If the product your displaying is >2000 in the list, then it does not display anything at the top of the screen, you don't get an error or anything which is good. Since I don't think many people use it, I just removed it so that the previous and next table entries were blank. It also has an unnecessary check on category_publish.
I want to work on this to see if I can make it faster, I think it only need to use product, product_category_xref and category tables.
Saves about 3 seconds on our site.

So I have now got the all the processing down, I have done some other things as well, to 1-2 seconds.

I have combined css and js were possible removed all the different colour schemes, made use of cloudflare removed the img resize script.
I have added a column to the cat. table with the sef URL so it does not have to be munched every time.

I am going to use css sprites for the dtree menu and move the dtree css to the top, there is a load of work to be done in getting css at the top and js at the bottom were I can. I need to change the code to get the height and width on to the pages and I want to make the menu stuff static, it hardly ever changes so why am I generating it all the time.

I have tackled the big impact bits and now need to hunker down and get rid of the last of the slow bits that will take a bit longer.

Then I need to try and thing about upgrading it, which will mean reapplying all the changes.

There is something that people need to remember? Things that take a long time are better done once and stored so that a customer does not have to wait.
SEF is a classic case, since I am using sh404sef it tried to turn all the non-sef into sef, there are loads on the average J/VM page in our case mainly in the menu so I got rid of that step.

Hope that spreads a bit of light and tells people what I have been working on recently.
Maybe it will help in the future as well

Some of things may have been fixed since this is an early 1.1 version of J/VM

A lot of these thing I can do since we use a dedicated machine if you were on some sort of shared hosting this would be quite difficult to do. I can make use of cron to run other script when I want them to.
614869 products in 747 categories with 15749 products in 1 category.
                                             Document Complete   Fully Loaded
                Load Time First Byte Start Render   Time      Requests      Time      Requests
First View     2.470s     0.635s     1.276s          2.470s       31            2.470s      31
Repeat View  1.064s     0.561s     1.100s          1.064s       4             1.221s       4


  • Global Moderator
  • Super Hero
  • *
  • Posts: 10412
  • VirtueMart Version: 3+
Re: Speed!
« Reply #1 on: February 22, 2011, 19:10:25 pm »
1 thing I have done on my new site redesign that will be launched soon is.

I disabled all the fetchscript files in htmlTools.class
Also disabled the addscripts in theme.php

Then added them to the template.

Then Gzip and combine CSS files with JCH Optimize
Then Gzip and Combine Java files with cssjscompress

This got the site faster. Up to an 83 Yslow score on Vmart pages.

J3.9+ VM 3.4.2
Slowest Page Speed Score (88) (Category)
Fastest Page Speed Score (94-96) (productdetails)