Developer forum

Forum » Ecommerce - Standard features » Error when defining Discounts with excluded products or Queries

Error when defining Discounts with excluded products or Queries

E-mail notifications
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply
Posted on 20/09/2022 20:48:15

Hi guys,

I am using DW 9.12.10 with a custom Rapido.

I have received a request from our customer to set up a discount that would apply to all products except for a few product numbers.

I have tried first to set it up leaving "Included products" empty and adding the exceptions to the "Excluded products" section.

The moment I did that, I got the error below:

Server Error in '/' Application.

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

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: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

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): Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3317868
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +736
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4061
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +90
   System.Data.SqlClient.SqlDataReader.get_MetaData() +99
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +604
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +3303
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +667
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +83
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +301
   Dynamicweb.Data.Database.CreateDataReader(IDbCommand command, CommandBehavior behavior) +557
   Dynamicweb.Data.Database.CreateDataReader(String sql, IDbConnection connection, IDbTransaction transaction, CommandBehavior behavior, Int32 commandTimeout, Dictionary`2 sqlParams) +150
   Dynamicweb.Data.Database.CreateDataReader(String sql, IDbConnection connection, IDbTransaction transaction, Int32 commandTimeout, Dictionary`2 sqlParams) +212
   Dynamicweb.Ecommerce.Prices.PriceDataBaseDependecy.GetPrices(String ids) +407
   Dynamicweb.Ecommerce.Prices.Price.LoadPricesOnProducts(IEnumerable`1 products) +385
   Dynamicweb.Ecommerce.Products.ProductRepository.GetProductsBySql(CommandBuilder query, Boolean doRefactoring, Boolean bulkFill, Boolean useAssortments) +238
   Dynamicweb.Ecommerce.Products.ProductRepository.GetAllProducts(String productLanguageId, Boolean useAssortments) +227
   Dynamicweb.Ecommerce.Orders.Discounts.Discount.ProcessProductSelections(HashSet`1& productKeys, HashSet`1& includedQueries, HashSet`1& excludedQueries) +869
   Dynamicweb.Ecommerce.Orders.Discounts.Discount.GetRelevantProductKeys() +348
   Dynamicweb.Ecommerce.Orders.Discounts.DiscountService.StatelessSetLookup(Discount discount, ConcurrentDictionary`2 lookup) +98
   Dynamicweb.Ecommerce.Orders.Discounts.DiscountService.SetLookup(Discount discount) +116
   Dynamicweb.Ecommerce.Orders.Discounts.DiscountService.SetCache(Discount discount) +44
   Dynamicweb.Ecommerce.Orders.Discounts.DiscountService.Save(Discount discount) +79
   Dynamicweb.Admin.eComBackend.EcomOrderDiscount_Edit.SaveDiscount(Discount discount) +7116
   Dynamicweb.Admin.eComBackend.EcomOrderDiscount_Edit.Page_Load(Object sender, EventArgs e) +437
   System.Web.UI.Control.OnLoad(EventArgs e) +106
   System.Web.UI.Control.LoadRecursive() +68
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3785

 

I then thought about using a Query for this purpose as it would be more suitable.

I have defined a Query including all products and assigned it the the "Included products" section.

The behavior was correct but the performance degraded up to a point where the site was not usable anymore (a cart with a single product would load in more than 120 seconds).

What would be the official solution for handling an apparent simple discount like I have described?

Thank you,
Adrian

 


Replies

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 20/09/2022 21:16:29
Reply

Hi Adrian,

 

We experienced that in the past. The queries are not a good way to handle Included/Excluded products in discounts. What we end up doing for that particular customer, was assiging the products to a dummy ecom group.

 

Best Regards,

Nuno Aguiar

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Posted on 20/09/2022 21:25:20
Reply

Hi Nuno,

Thank you very much for the feedback.

Using groups could be a solution but in our case that would mean assigning a couple of thousand of products to dummy groups. 

It would still be doable and surely a good workaround but not very usable for the customer.

Thank you,
Adrian

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 20/09/2022 21:27:16
Reply

Hi Adrian,

 

Understood. You can try to look into https://doc.dynamicweb.com/documentation-9/pim/other/auto-assign-products-to-groups to help with that too

 

Best Regards,

Nuno Aguiar

 
Nicolai Pedersen
Posted on 21/09/2022 10:29:29
Reply

Hi Adrian

You cannot use the queries with many products - it will greatly slow down your performance. Also creating discounts on a product level is not recommended unless it is a few products <10 - otherwise you will have to use a group like Nuno suggests.

 
Nicolai Pedersen
Posted on 21/09/2022 10:31:37
Reply

>>except for a few product numbers

How many was that?

 

You must be logged in to post in the forum