Hello Gentlemen,
I would like to share the Small discovery of the SQL Server Profiler,
during the performing of one page load of nopcommerce website.
website: http://www.koptenko.com.ua
NopCommerce version 3.1
Overal Categories amount: ~ 350
Overal Products amount: ~ 2000
Product-item customization (which can influence on performance):
- added manufacture to the product-item in a list;
- shown cut fullDescription on product-item;
- shown 2 first product-spec on product-item;
local PC when testing:
quad CPU 2.33 GHz, RAM-4Gb, win-8.
so, not the worst
I'm performing the not cached page load of one of category, which has:
25 child categories,
around 250 products.
Browser: Firefox v23.
Server response(part of page load) time measurement:
On Pagesize-20 took: 10 sec (with profiling 30 sec)
On Pagesize-100 took: 24 sec (with profiling 44 sec)
Unfortunatelly the Server response time is not reasonable at all...
To find out what the problem root, I've run the Sql Server Profiler,
and got next results:
On Pagesize-20:
DB events: 670
On Pagesize-100:
DB events: 1140
1 - Request for categories: http://screencast.com/t/W9uXWCj2Q
Together with login/logout in sql server this scope of operation can take 3-8 sec.
- request sproc for products of particular categories;
- request subcategories for each found categories;
- request sproc for products of all child categories;
Note:
I think the cause of this is settings to show products from child categories,
but it looks like the performance impact is too big,
it might be better to include parent category to the products instead.
2 - ProductLoadAllPaged
provides list of products which to be presented on the list of products.
performance time for 20 items 2 - 4 sec, for 100 items 4 - 7 sec.
the result contains full scope of the fields with data.
Question:
Why this sproc takes so long?
Why full bunch of fields with data are needed on Product list?
Proposition:
Make a settings for presenting only relevant fields of the product in the list result;
In fields like FullDescription, ShortDescription it can be big text, which is also not relevant for the list item. Cut it on Sql server level up to 500 chars or so.
MetaData is not relevant for the list item.
3 - Queries after Products list is received:
Each of Product in a list after performing a stored procedure has to call additionally 6 queries to get:
http://screencast.com/t/2KvedcgdfbBP
- request for picture
- request for slug in two languages: http://screencast.com/t/pKHhLzSd
- request for localvalues for each field which has it: http://screencast.com/t/sjbTIjDWHuLv
100*5 = 500 additional queries.
Summary:
The amount of queries sent to SQL Server is too big,
and sprocs for products list retrieving is not optimized enough.
Unfortunately,
the client side optimization is trifles comparing to server response time on at least thousand of products.
Proposition:
- Make a setting in Admin to use optimal settings to get best performance.
- Write best practices to configure site with best performance.
- Minimize amount of SQL server calls.
- Optimize sproc;
- Retreive localized data from only one used language, and include in sproc call;
- Avoid retrieving empty queries which take time;
...
All comments are welcome,
hope this can help for nopcommerce team to consider "real" performance optimization work in coming releases.
If you have any other advises about how to speedup the website, please comment.
Thank you