nopCommerce 1.7 cache not working?! Possible solution to 1.7 slowness?

We have just spent several hours debugging what we thought was an error in our code.

Every time a page was loading, several hundred (270ish) SQL queries were being executed. I thought this was odd as I know nop has a caching mechanism.

After looking in to it by putting a breakpoint at the GetCategoryById, I found that items would not properly being stored in the cache.

After implementing our own cache logic in NopCache class, we managed to get the queries down to 40 from 270. These queries related to items which didn't have cahcing logic.

Nop guys - can you do me a favour and see if this is indeed a bug?

I used SQL profiler to view the queries being executed.

This might go some way to explaining why 1.7 is much slower than 1.6

thought wrote:
It all depends on a site. We tested default nopCommerce installation. As a rule it runs about 30-50 queries which is OK

thought wrote:
Caching mechanism was changed in 1.70. Now almost all entities are cached per request

nopCommerce team | a.m. wrote:

yeh, after doing further digging i noticed the nopstaticcache was not being used anymore.

is there a reason why you moved to this method? it seems to me to be much slower - obviously on a standard nop site there maybe isn't as much going on as say www.wholesalelighting.co.uk

think i'll have to write some chaching logic - maybe SQL cache dependency and put it on Outputcahce?

nopCommerce team | a.m. wrote:

Thinking about it, 30-50 queries is quite a lot. Think about a busy site - there would be 1000's of queries each second.

Can you explain why you went away from the Static caching methods? I don't want to spend time re implementing this if there was a good reason for stopping this method.

OK, here are my benchmarks with http://www.wholesalelighting.co.uk/

Nop 1.7 caching : Average page load 2-3 seconds
Nop 1.6 cache mode: Average page load 0.9-1.4 seconds

Speed is king - please explain this decision

thought wrote:
1. There were some issues with Entity Framework when entites were cached between requests. It caused exceptions.
2. And it's not recommended to cache all entities between requests in web application (except ofter used entites - such as Settings, LocaleStringResource). What if a store has 3,000 product and 300 categories? It'll not be a good practice to cache all of them.
That's why  some of this data cached for a long period of time, but some data is cached per request

thought wrote:
I don't think so. Almost each e-commerce site has approx 40-60 queries

BTW I think solution pre-generating views will significantly improve performance of Entity Framwork. You can find more info here (http://blogs.msdn.com/b/adonet/archive/2008/02/04/exploring-the-performance-of-the-ado-net-entity-framework-part-1.aspx) and here.

"However, the downside of this solution is the need to keep the generated views synchronized with changes to the model" - that's why we'll not follow this solution into official releases

nopCommerce team | a.m. wrote:

Some stores we run have 1000 categories and 15,000 products - why would it be a problem if this was in memory - it only equals about 50-100mb of RAM? Also remember that IIS will kill cache if there isn't enough memory so I cant see how this is a problem. The only problem with this method is if the system is on a web farm, you then get stale cache.

What were the exceptions caused between requests? I thought if something is in memory then it got returned, otherwise it would go back to the DB. Therefore EF would be doing anything once the object is in cache.

Thanks for the pre-generated views idea, I'll take a look.

thought wrote:
I don't remeber exactly. It was related to usage of distinct object contexts (NopObjectContext) for the same entity (NopObjectContext is created per request)

I am sorry , I love nopcommerce, but I have to step into the conversation.
Something is wrong with the cashing in version 1.70 .
You should check again and test with a site that contains 3 languages or more.
I had to move back to version 1.60 and everything works like a charme and
also version 1.60 responds MUCH faster than version 1.70.
I think you guys should check again the cashing with the entity framework, you where
too quick to implement the use of entity framework without checking it in regards
to cashing.
I just used version 1.70 for a site with 3 languages , with 41 products and
5 main categories and 5 sub categories.
I used the profiler to see what is going on and there are way too many queries that require
more than a second to complete , something in the cashing is wrong.
The site crashed all of the times once the user clicked
some category or subcategory with the following exception :

: System.Data.SqlClient.SqlException: The query has been canceled because the estimated cost of this query (2163) exceeds the configured threshold of 2000. Contact the system administrator.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): The query has been canceled because the estimated cost of this query (2163) exceeds the configured threshold of 2000. Contact the system administrator.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +86
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479
System.Data.Objects.ObjectContext.CreateFunctionObjectResult(EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption) +182
System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, MergeOption mergeOption, ObjectParameter[] parameters) +218
System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, ObjectParameter[] parameters) +53
NopSolutions.NopCommerce.BusinessLogic.Data.NopObjectContext.Sp_ProductLoadAllPaged(Int32 categoryId, Int32 manufacturerId, Int32 productTagId, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, String keywords, Boolean searchDescriptions, Int32 pageSize, Int32 pageIndex, List`1 filteredSpecs, Int32 languageId, Int32 orderBy, Boolean showHidden, Int32& totalRecords) +2428
NopSolutions.NopCommerce.BusinessLogic.Products.ProductManager.GetAllProducts(Int32 categoryId, Int32 manufacturerId, Int32 productTagId, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, String keywords, Boolean searchDescriptions, Int32 pageSize, Int32 pageIndex, List`1 filteredSpecs, Int32 languageId, ProductSortingEnum orderBy, Int32& totalRecords) +317
NopSolutions.NopCommerce.BusinessLogic.Products.ProductManager.GetAllProducts(Int32 categoryId, Int32 manufacturerId, Int32 productTagId, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, String keywords, Boolean searchDescriptions, Int32 pageSize, Int32 pageIndex, List`1 filteredSpecs, ProductSortingEnum orderBy, Int32& totalRecords) +219
NopSolutions.NopCommerce.BusinessLogic.Products.ProductManager.GetAllProducts(Int32 categoryId, Int32 manufacturerId, Int32 productTagId, Nullable`1 featuredProducts, Int32 pageSize, Int32 pageIndex, Int32& totalRecords) +223
NopSolutions.NopCommerce.BusinessLogic.Categories.Category.get_FeaturedProducts() +104
NopSolutions.NopCommerce.Web.Templates.Categories.ProductsInGrid.BindData() +442
NopSolutions.NopCommerce.Web.Templates.Categories.ProductsInGrid.Page_Load(Object sender, EventArgs e) +93
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +91
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Control.LoadRecursive() +146
System.Web.UI.Control.LoadRecursive() +146
System.Web.UI.Control.LoadRecursive() +146
System.Web.UI.Control.LoadRecursive() +146
System.Web.UI.Control.LoadRecursive() +146
System.Web.UI.Control.LoadRecursive() +146
System.Web.UI.Control.LoadRecursive() +146
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

its getting worse and worse for us on busy sites.

we are having to put back caching to the way it used to be.

i think this is a serious flaw, might start a new codeplex account for this.