Nopcommerce website performance Profiling

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

If there are any answer from the NopCommerce team related to this issue?

Thanks a lot for suggestions. I've tested nopCommerce on my dev machine. It works fine, much faster than 8 seconds

1. Please clarify

2. It takes less than one second on my dev machine. It also takes about 1-2 seconds even for 1M records. Of course, we can remove these fields from ShortDescription, FullDescription from result. But... short description is used when loading products on catalog pages. And what if some developer wants FullDescription to be loaded? It'll take hours for him to find out why it happens. Of course, we can add some parameter to this stored procedure indicating whether this field should be loaded. But it'll just add some level of complexity. I think it should be left for custom solutions (simply comment these field only in your application).

3. Of course. But how else? Please note that all of these properties are cached once requested

P.S. The only good and real performance optimization we can get only when Entity Framework starts supporting two-level cache

I have three sites on nopCommerce.

MeinHund.ru - opens very slowly.

in the code have not changed anything.

Thank you for reply.

Actually much faster is how much?

I think that first loading server response 1-2 seconds is fast enough.
3+ seconds is bad result.

after server response there are some client scripts, css, images, and many other ajax or resources loads.

but having empty page with just list from 20 products to load with 5-8 seconds is not competitive in internet world.


1 - When you check checkbox:  http://screencast.com/t/lCx6CratUl
we have new call of same sproc for each child category to retrieve the products.

2 - In any case, stored procedure which performs 4-6 seconds on normal environment is not the case.
- research Execution plan;
- adding indexes for the fields which used for search;
- provide only relevant fields, and give a settings "include/exclude" to rarely used fields;
- maybe introduce the functions and viwes, which can gather the data and called by sproc.

I think it's matter of skilled DB developer to speedup sproc to get it performing with 0.5 - 1 sec instead.
I agree with you about complexity, but the problem is very serious, which cut huge amount of potential user of nopcommerce system.
And leave only the users which need nopcommerce for presenting couple of tens of hundred products.

3 - There are two things:
1) slug retrieving for each of product and category in separate request to DB.
It has to be part of sproc, and avoid that superfluous request;
2) Localized values.
It would be nice to have settings use/not use localization.
then this calls should not be done at all when "not use" localization;
and set a language parameter to Stored Procedure which language to retrieve.

For stored procedure to handle this two cases, it's definitely has to be re-factored and spited, to avoid complexity.

The caching is a good thing if used smartly, but when user starts navigating first time around the categories it becomes very painful and many refuses happens.

I hope this info is helpful, and there might be the performance reconsideration planed in coming releases.

Thank you.

First of all, thanks a lot for your feedback and suggestions

sheretovev wrote:
1-2 seconds. Test our demo site here


sheretovev wrote:
Not true. Only one database request is made for all subcategories. All category identifiers (including subcategories) are sent to stored procedure to load products


sheretovev wrote:
Absolutely agree. But it's already 0.5-1 sec. Again test our demo site."Include/exclude" some properties won't help (less than 15-20 products on a page on 99% of sites, it just doesn't matter). There should be something else...  If you have any other certain suggestions, please share.




sheretovev wrote:
This work item already exists. Please vote here

sheretovev wrote:
We cannot return localized values from stored procedure. Otherwise "Product" entity will have non standard values in properties. Then EF will try saving this localized properties as standard ones. It's unacceptable. Furthermore, when you have only one language, then localizable properties won't be loaded. Of course, you can always disable property localization even if you have two or more language by very simple customization of "GetLocalized" method of LocalizationExtentions

I am new here so need some support.

My site is taking 20 - 30 seconds to load and I totally have no idea whats going on. This all started when I upload the products through import button. I just have 100 thumbnails and 4200 + products. Please suggest what should I do.

My serve is VPS with CPU 2 cores and RAM 3500 MB with OS Windows Server 2008 Standard 32-bit.

site url is www.shop.call131.com

Also my admin area is working too slow.

dharmsurya wrote:

What version of nopCommerce are you running? Are the logs saying anything? This is a table in your database (maybe called log) if you can't access the admin site.

Hi,

Thanks for reply.

I am using 3.30. As I wont install the developer version but the no source code version So I cannot change any thing in codes.

Unfortunately I cleared the log by following another post so cannot tell anything from that. But I enable mini profiler so you can see wht is taking time to load. I am not a developer so could not understand every thing on first view.

Hope this information helps.

dharmsurya wrote:

Can you easily upgrade to nopCommerce 3.4? Many performance enhancements have been added that may solve your problem.

Upgrade guide:
https://www.nopcommerce.com/docs/80/upgrading-nopcommerce.aspx