Hello,
Our client ask us to create shop using nopcommerce.
On short look I found performance issues.
To debug the problem I install MiniProfilerEF6. https://www.nuget.org/packages/MiniProfiler.EF6
Problem:
On first initialization is 64 database query's with 30! duplicate query's
On category page is 30 database query's with 6 duplicate query's.
On product page is 30 database query's with 10 duplicate query's.
Solution
1. Please remove duplicate query's.
2. Please try to reduce number of query's using different db layout and Repository pattern for cache. If its not possible please save all config info as file (using protobuf-net) and load it from file system (on any change delete the file).
3. Please consider to create in database cache for category and product pages. Save action result as text and retunt Content() it. Instead of 30 query's you can do only one. You can look at Durpal CMS as example for "in database cache".
If you have question or need help with this please feel free to contact me.
Log: http://www.libra.co.il.anemone.arvixe.com/1.txt
Screenshot: http://www.libra.co.il.anemone.arvixe.com/1.jpg
1. nopCommerce version - 3.3
2. Any private modifications you made to your nopCommerce - Install MiniProfiler.EF6
3. The version of MS SQL database you're running nopCommerce with - mssql 2008R2
Thanks,
Andrey.
Duplicate SQL query's
Team Member
- 16611
- 155790
- 10/22/2008
- Armenia
Hi Andrey,
1. There is no duplicate queries. Your text log misses database parameters which are distinct. See parameter values passed and you'll see that there's no duplicates.
2. There are really a lot of performance and memory enhancements coming in the next version (for example, described here). Furthermore, once some page (e.g. category or product) is loaded there are a lot parts of content are cached.
3. It's just not possible. Page could depend on some complex values values which depend on certain customer request. For example, complex discount rules for product prices, themes, localization, etc. By the way, we have a special setting to cache prices in the upcoming 3.40
1. There is no duplicate queries. Your text log misses database parameters which are distinct. See parameter values passed and you'll see that there's no duplicates.
2. There are really a lot of performance and memory enhancements coming in the next version (for example, described here). Furthermore, once some page (e.g. category or product) is loaded there are a lot parts of content are cached.
3. It's just not possible. Page could depend on some complex values values which depend on certain customer request. For example, complex discount rules for product prices, themes, localization, etc. By the way, we have a special setting to cache prices in the upcoming 3.40
- 8
- 906
- 2/21/2012
- Israel
Hello,
About query's,
Lets say we have settings table A and inside the table we need to load 3 rows.
We can load all the rows to repository (memory or disk).
This way we will do 1 query and not 3.
if we have 20 tables with settings we can also load all rows from tables a and save it as file (using protobuf-net).
Next system start load all data from file.
About page caching,
Lets say we have different user groups and we need to show page in different way and we have discount rolls and ....
We need cache table with 2 rows: key and data.
We can save this page using key = "Pagename_usergrop_discountrool..."
Its not something new, its work in other CMS like DURPAL and more.
Andrey.
About query's,
Lets say we have settings table A and inside the table we need to load 3 rows.
We can load all the rows to repository (memory or disk).
This way we will do 1 query and not 3.
if we have 20 tables with settings we can also load all rows from tables a and save it as file (using protobuf-net).
Next system start load all data from file.
About page caching,
Lets say we have different user groups and we need to show page in different way and we have discount rolls and ....
We need cache table with 2 rows: key and data.
We can save this page using key = "Pagename_usergrop_discountrool..."
Its not something new, its work in other CMS like DURPAL and more.
Andrey.
Team Member
- 16611
- 155790
- 10/22/2008
- Armenia
uco wrote:
Actually [Setting] table is already loaded using one SQL queryand then cached, the same is about locale resources. We already cache almost everything that it's possible. If you mean some other tables, then please provide certain suggestion. What table(s) do you mean? Also note that Entity Framework does not support 2-level cache yet.
About query's,
Lets say we have settings table A and inside the table we need to load 3 rows.
We can load all the rows to repository (memory or disk).
This way we will do 1 query and not 3.
if we have 20 tables with settings we can also load all rows from tables a and save it as file (using protobuf-net).
Next system start load all data from file
Lets say we have settings table A and inside the table we need to load 3 rows.
We can load all the rows to repository (memory or disk).
This way we will do 1 query and not 3.
if we have 20 tables with settings we can also load all rows from tables a and save it as file (using protobuf-net).
Next system start load all data from file
Actually [Setting] table is already loaded using one SQL queryand then cached, the same is about locale resources. We already cache almost everything that it's possible. If you mean some other tables, then please provide certain suggestion. What table(s) do you mean? Also note that Entity Framework does not support 2-level cache yet.
- 174
- 1361
- 4/14/2011
- Ukraine
a.m. wrote:
I estimate 95% of the page loads we serve (view products in a category) are coming from unlogined guests (with no roles, no discounts, all default) A shortcut to serve only these requests from cache would also help in most of the time. BTW this is also the way the google crawler bots are seeing our sites, faster page loads would contribute to better ratings.
3. It's just not possible. Page could depend on some complex values values which depend on certain customer request...
I estimate 95% of the page loads we serve (view products in a category) are coming from unlogined guests (with no roles, no discounts, all default) A shortcut to serve only these requests from cache would also help in most of the time. BTW this is also the way the google crawler bots are seeing our sites, faster page loads would contribute to better ratings.
- 8
- 906
- 2/21/2012
- Israel
a.m. wrote:
Actually [Setting] table is already loaded using one SQL queryand then cached, the same is about locale resources. We already cache almost everything that it's possible. If you mean some other tables, then please provide certain suggestion..
Hi
I didn't have much time for test but this is what I found in 20 min.
If you have question or need help with this please feel free to contact me.
All aplication (in every page)
(4 sql) - 4 query's for current user. Write custom membership and group provider. Don't use default provider. Use 1 table for user data and roles "|Role1|Role2|Role3|"
1. Home page - first load.
Don't load all config info form DB. Save objects with all settings as file (using protobuf-net).
I mean not only to setting table. All tables application query on load (menus, routing ...).
WidgetsNivoSliderController.PublicInfo GetPictureById - running 2 times. Application bug
CatalogController.TopMenu (10+ sql) - try some other db layout or create menu table on category edit.
2. Home page - second load
CatalogController.HomepageCategories (1 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
CatalogController.HomepageProducts (4 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
CatalogController.RecentlyViewedProductsBlock (1 sql) - Save i cookie and not in db
CommonController.HeaderLinks (1 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
3. Category page
CommonController.HeaderLinks (1 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
CatalogController.Category GetAllCurrencies get_WorkingCurrency FormatPrice - add to session
CatalogController.Category GetAttributesForEntity GetAttribute get_WorkingCurrency - add to session
CatalogController.Category GetAllLanguages get_WorkingLanguage SetWorkingCulture - add to session
CatalogController.Category GetAttributesForEntity GetAttribute get_WorkingLanguage SetWorkingCulture - add to session
CatalogController.Category GetCategoryById GetCategoryBreadCrumb Category - save category list in application cache
4. Product
CatalogController.CategoryNavigation GetProductCategoriesByProductId CategoryNavigation -
CatalogController.Product get_ProductAttribute PrepareProductDetailsPageModel, GetProductVariantAttributeValues PrepareProductDetailsPageModel - if you have 5 attributes don't make 5 query's. try to load all atrebutes in 1 query.
Actually [Setting] table is already loaded using one SQL queryand then cached, the same is about locale resources. We already cache almost everything that it's possible. If you mean some other tables, then please provide certain suggestion..
Hi
I didn't have much time for test but this is what I found in 20 min.
If you have question or need help with this please feel free to contact me.
All aplication (in every page)
(4 sql) - 4 query's for current user. Write custom membership and group provider. Don't use default provider. Use 1 table for user data and roles "|Role1|Role2|Role3|"
1. Home page - first load.
Don't load all config info form DB. Save objects with all settings as file (using protobuf-net).
I mean not only to setting table. All tables application query on load (menus, routing ...).
WidgetsNivoSliderController.PublicInfo GetPictureById - running 2 times. Application bug
CatalogController.TopMenu (10+ sql) - try some other db layout or create menu table on category edit.
2. Home page - second load
CatalogController.HomepageCategories (1 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
CatalogController.HomepageProducts (4 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
CatalogController.RecentlyViewedProductsBlock (1 sql) - Save i cookie and not in db
CommonController.HeaderLinks (1 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
3. Category page
CommonController.HeaderLinks (1 sql) - Add [OutputCache(Duration = 3600, VaryByCustom = "LangCodeAndCleanCacheGuid")]
CatalogController.Category GetAllCurrencies get_WorkingCurrency FormatPrice - add to session
CatalogController.Category GetAttributesForEntity GetAttribute get_WorkingCurrency - add to session
CatalogController.Category GetAllLanguages get_WorkingLanguage SetWorkingCulture - add to session
CatalogController.Category GetAttributesForEntity GetAttribute get_WorkingLanguage SetWorkingCulture - add to session
CatalogController.Category GetCategoryById GetCategoryBreadCrumb Category - save category list in application cache
4. Product
CatalogController.CategoryNavigation GetProductCategoriesByProductId CategoryNavigation -
CatalogController.Product get_ProductAttribute PrepareProductDetailsPageModel, GetProductVariantAttributeValues PrepareProductDetailsPageModel - if you have 5 attributes don't make 5 query's. try to load all atrebutes in 1 query.