My SQL error

What causes this?

If I go to our home page:  http://www.epicgamingpcs.com and click on any of our products, I get the following (discountasp.net said this isn't their problem, and our database must be 'optimized' by the developers)


Server Error in '/' Application.

The query has been canceled because the estimated cost of this query (2308) exceeds the configured threshold of 2000. Contact the system administrator.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The query has been canceled because the estimated cost of this query (2308) 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 (2308) 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(Nullable`1 categoryID, Nullable`1 manufacturerID, Nullable`1 productTagID, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, Nullable`1 relatedToProductID, String keywords, Nullable`1 searchDescriptions, Nullable`1 showHidden, Nullable`1 pageIndex, Nullable`1 pageSize, String filteredSpecs, Nullable`1 languageID, Nullable`1 orderBy, ObjectParameter totalRecords) +3395
   NopSolutions.NopCommerce.BusinessLogic.Products.ProductService.GetAllProducts(Int32 categoryId, Int32 manufacturerId, Int32 productTagId, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, Int32 relatedToProductId, String keywords, Boolean searchDescriptions, Int32 pageSize, Int32 pageIndex, List`1 filteredSpecs, Int32 languageId, ProductSortingEnum orderBy, Int32& totalRecords) +1048
   NopSolutions.NopCommerce.BusinessLogic.Products.ProductService.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) +140
   NopSolutions.NopCommerce.BusinessLogic.Products.ProductService.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) +183
   NopSolutions.NopCommerce.BusinessLogic.Products.ProductService.GetAllProducts(Int32 categoryId, Int32 manufacturerId, Int32 productTagId, Nullable`1 featuredProducts, Int32 pageSize, Int32 pageIndex, Int32& totalRecords) +225
   NopSolutions.NopCommerce.BusinessLogic.Categories.Category.get_FeaturedProducts() +115
   NopSolutions.NopCommerce.Web.Templates.Categories.ProductsInLines1.BindData() +480
   NopSolutions.NopCommerce.Web.Templates.Categories.ProductsInLines1.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

Nevermind...  It was when I switched the "Global Options" to allow usernames, and since I used the admin account and changed everything for myself, it didn't have a username and made the discountasp.net server freak out.  

I had to back up and then restore my database to get the connection to close and the query to stop (only way as a hosted customer).

Hi All,

This week I had the same issue identified above, however discountasp.net seem to have increased the threshold to 2500.

After some investigation the offending issue can be derived back to the stored procedure Nop_ProductLoadAllPaged.

I haven't found a good article on how Microsoft query governor determines it's execution cost - as I ran estimated execution plan tool and everything looked fine.

I did some exclusion of query code to determine what was going on and found that the use of a function call "dbo.NOP_getnotnullnotempty(pl.[Name],p.[Name])"  was being treated as the "cost" value to the query.

   INSERT INTO #DisplayOrderTmp ([ProductID])
      SELECT p.ProductID
      ...  
     ORDER BY 
        CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
        THEN pcm.DisplayOrder END ASC,
        ...      
       CASE WHEN @OrderBy = 5
       THEN p.[Name] END ASC,
       --THEN dbo.NOP_getnotnullnotempty(pl.[Name],p.[Name]) END ASC,
      CASE WHEN @OrderBy = 10
      THEN pv.Price END ASC,
      CASE WHEN @OrderBy = 15
      THEN p.CreatedOn END DESC

My site doesn't use Localization so I was able to comment out the line and simply change to using the p.[Name], and site back up and running.

Cheers & hope this helps someone else out

Darren

Thanks Darren, it helped me also.
I just added another ~ 100 new products to the already existing 400 => I had the same issue. Lucky for me: I don't use Localization yet, so I could use your workaround.

For NOP commerce team: please optimize this, otherwise may other users will have the same issue !!

Thanks for this, had the same problem. Thought it might be my hosting buy apparently not!.

Thanks so much for the fix! We ran into the same problem. Our host had the query governor set to 300. Commenting out the localization call took care of it. Good thing we don't have much of an international customer base!

I'm having the same problem with Winhost, they should have moved something in the configuration of the DB Server because the site ran without this problema for 3 months, and I have another one running for 1 year (but witn NOP 1.8).

I modified the SP and reduced the query cost but it still not enough, I even commented the filtering attributes part of the SP but it still gives me the error:

The query has been canceled because the estimated cost of this query (4822) exceeds the configured threshold of 2500. Contact the system administrator.

This is the second issue that I have with Winhost and NopCommerce, if I find another solution I'll post it.

I wonder if this is fixed in 2. I have two clients I wanted to launch on nop but I don't want to run into this problem as soon as they hit more than a couple of products. I agree that a load limit of 300 is pretty low, but good luck getting most shared hosts to change it, know what it is, or care....

The only way I could fix this error was finding another version of the Nop_ProductLoadAllPaged procedure, fortunately someone had already written it with CTEs which is way more efficient and less prone to SQL Server perfomance issues, the code is in the following post:

https://www.nopcommerce.com/boards/t/9831/nop_productloadallpaged-needs-to-be-rewritenoptimized.aspx#46728

Now my site is up and running again, it was down all day, time to explain all this tech issues to the client :-D