mtlaffaires wrote:I figure out an out of the box Solution for Nop speed,
I was a bit angry because everyone i talk to where talking about profiler, query optimisation , weeks of work....
Finally i add an idea to use xml instead of cache and it took 2 day work.
Step 1- I put a trigger that detect change in product db
Step 2- I made a script trigered by step 1 that create one xml for each
category that contains all info: productid, thumbnail (also a bottleneck),
tier prices name english and french etc.. Also the script make 1 xml for
all the categories structures.
yes it mean for my site 100 xml of about 400k each and a few of 2 meg... but for a 2s response time what is an extra 200 megs??? (finally it take 40 meg!)
Step 3- I write in productlist.cshtml and change it to load the roles and
the specify category id xml and populate the result respecting the roles and
business rules. Since it dont request the db for each product anymore I
improove from 2 min to 7s
Step 4- I rewrite the category menu control to load the xml and populate it
without any request to db except one time to get the roles.
Now any product page load in 2-3 second maximum
Step 5 I implement a button in admin area to run the script anytime I feel
i dont want to wait the trigger.
So the plugin for the script is easy, but I cannot make a general
productlist that would take anytype of business rules like we have. But
still it was a 2 day work and I had a huge improvement.
Another advantage, it is multi-lingual, site, multi-tier price
So i wish it can help.
Just a note that we have tried exact same thing few months back, it works fine during development but when we made it live and run a load testing on the site we realized that it just improves the site performance for certain small number of visitors.. once you start to reach the limit with more visitors / concurrent users the site performance will degrade drastically.. even worse than it was before. Earlier it was SQL server which was taking most resources... after this kind of implementation SQL server was free most of the time, but worker process started to take the most of CPU. We tried with XML and even storing the data in binary format too, which further improved performance.. but is certainly not reliable. With more visitors, you will severely hit with high IO as well.
I recommend you to test it thoroughly with load testing and especially apply some specification and attribute filters on the product list and use it during load testing...and you will soon find the bottlenecks of this implementation. However, this works very well for small sites with low traffic, but not good for busy sites with serious e-commerce business looking for reliable and faster e-commerce site.
If you want quick & reliable solution, then I request you to check our
demo store which has
more than 80,000 products. Most of the pages are generated and loaded well within a second.. actually it took only
150-250 ms to generate them even if the Solr Core is hosted on a remote server (on our Cloud Solr Server)! So if you host it on your server, then you can save another 100ms!