Developer forum

Forum » CMS - Standard features » SQL Execution Timeout on Customer Center Cart List

SQL Execution Timeout on Customer Center Cart List

E-mail notifications
Zayar Minn Dynamicweb Employee
Zayar Minn
Reply
Posted on 13/05/2025 08:25:06

Hi,

I'm frequently encountering an "SQL execution timeout expired" error when customers access the Customer Center Cart List.
Could you please advise on how to resolve or optimize this issue?

Thank you!

SQL Server: B8ms
Total EcomOrders: 1,175,358
Total EcomOrderLines: 3,074,842
Total order for User-A: 8,868
Total order lines for User-A: 22,343
Total cart for User-A: 1

2025-05-13 11:31:11.742: DECLARE @DefaultRate float;SET ARITHABORT OFF;  SET @DefaultRate = IsNull((SELECT TOP 1 CurrencyRate FROM EcomCurrencies WHERE CurrencyIsDefault = 1), 100);  DECLARE @Time datetime; SET @Time = @p0;  WITH OrderedOrders AS (  SELECT ROW_NUMBER() OVER(ORDER BY EcomOrders.OrderDate DESC) AS RowNumber,  EcomOrders.OrderId, ShopName, EcomRecurringOrder.*  , AccessUser.AccessUserName AS OrderSecondaryUserName  FROM EcomOrders WITH (NOLOCK)  LEFT JOIN EcomCurrencies ON EcomOrders.OrderCurrencyCode = EcomCurrencies.CurrencyCode AND EcomCurrencies.CurrencyLanguageID = @p1  LEFT JOIN EcomOrderStates ON EcomOrders.OrderStateID = EcomOrderStates.OrderStateID  LEFT JOIN EcomShops ON EcomOrders.OrderShopID = EcomShops.ShopID  LEFT JOIN EcomRmas ON EcomOrders.OrderID = EcomRmas.RmaReplacementOrderId  LEFT JOIN AccessUser ON EcomOrders.OrderSecondaryUserID = AccessUser.AccessUserID  LEFT JOIN EcomRecurringOrder ON EcomOrders.OrderRecurringOrderId = EcomRecurringOrder.RecurringOrderID  LEFT JOIN EcomTrackAndTrace ON EcomOrders.OrderTrackAndTraceId = EcomTrackAndTrace.TrackAndTraceId  WHERE EcomOrders.OrderDeleted = 0  AND EcomOrders.OrderComplete = 0  AND (EcomOrders.OrderIsQuote = 0 OR EcomOrders.OrderComplete = 1)  AND EcomOrders.OrderIsLedgerEntry = @p2  AND EcomOrders.OrderCart = 1  AND (EcomOrders.OrderIsRecurringOrderTemplate = 0 or OrderIsRecurringOrderTemplate is NULL)  AND (EcomOrders.OrderCustomerAccessUserID = @p3 OR EcomOrders.OrderSecondaryUserId = @p3)  ),  OrdersCount as  (  SELECT COUNT(*) AS TotalOrdersCount 

 FROM OrderedOrders  )  SELECT * FROM OrdersCount, OrderedOrders WITH (NOLOCK)  WHERE RowNumber BETWEEN @p4 AND @p5  ORDER BY RowNumber. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. 
System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at 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)
at 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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Dynamicweb.Data.Database.CreateDataReader(IDbCommand command, CommandBehavior behavior) ClientConnectionId:ec7766fb-d5d8-48a7-9706-e6ad1d073ad5 Error Number:-2,State:0,Class:11
2025-05-13 11:31:11.817: Request headers: [   "Connection",   "Accept",   "Accept-Encoding",   "Accept-Language",   "Cookie",   "Host",   "Referer",   "User-Agent",   "X-FORWARDED-PROTO",   "X-FORWARDED-PORT",   "X-Forwarded-For",   "X-Original-URL",   "X-AppGW-Trace-Id",   "X-ORIGINAL-HOST",   "sec-ch-ua",   "sec-ch-ua-mobile",   "sec-ch-ua-platform",   "Upgrade-Insecure-Requests",   "Sec-Fetch-Site",   "Sec-Fetch-Mode",   "Sec-Fetch-User",   "Sec-Fetch-Dest" ]
2025-05-13 11:31:11.817: Request item keys: [   "DynamicwebLiveIntegrationIsIntegrationActive",   "owin.Environment",   "EcomCart:CustomCartContext",   "PagePermissionWithInheritanceFrontend8204",   {},   "MS_HttpRequestMessage",   "AspSession",   "PermissionContextStack",   "DynamicwebLiveIntegrationEnabledAndActive",   "Dynamicweb.OMC.VisitContext",   "AspSessionIDManagerInitializeRequestCalled",   "PagePermissionWithInheritanceFrontend7825",   "alternateUrlKey",   "ExecutionTableMapper",   "DWPAGEVIEW",   "Dynamicweb.Environment.Web",   "Dynamicweb.Tracking.Tracker",   "UserManagement.GetUserById.18278" ]
2025-05-13 11:31:11.817: Request form: []


Replies

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Posted on 13/05/2025 11:33:16
Reply

Hi Zayar

Can you try to add this index to your table:

CREATE NONCLUSTERED INDEX IX_EcomOrders_ByCustomerCart
  ON EcomOrders
    (
      OrderCustomerAccessUserID,
      OrderDeleted,
      OrderComplete,
      OrderIsQuote,
      OrderIsLedgerEntry,
      OrderCart,
      OrderIsRecurringOrderTemplate,
      OrderDate DESC
    )
  INCLUDE
    (
      OrderID,
      OrderShopID,
      OrderRecurringOrderId,
      OrderSecondaryUserID,
      OrderTrackAndTraceId,
      OrderCurrencyCode,
      OrderStateID
    );

It seems like you have the customer center setup to show orders of "My carts and carts from users I can impersonate". Try to change that to "My carts" only as that should drasticly increase performance with the above index.

BR Nicolai

 

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Posted on 13/05/2025 11:34:38
Reply

You can also try to add this index - almost the same as the first, but better change the listing of carts to not take impersonation into consideration:

CREATE NONCLUSTERED INDEX IX_EcomOrders_BySecondaryCart
  ON EcomOrders
    (
      OrderSecondaryUserID,
      OrderDeleted,
      OrderComplete,
      OrderIsQuote,
      OrderIsLedgerEntry,
      OrderCart,
      OrderIsRecurringOrderTemplate,
      OrderDate DESC
    )
  INCLUDE
    (
      OrderID,
      OrderShopID,
      OrderRecurringOrderId,
      OrderCustomerAccessUserID,
      OrderTrackAndTraceId,
      OrderCurrencyCode,
      OrderStateID
    );
 
Zayar Minn Dynamicweb Employee
Zayar Minn
Posted on 13/05/2025 11:56:12
Reply

Hi Nicolai,

Thanks for your suggestion.
Here below are the setting in Customer Center module and not using "My carts and carts from users I can impersonate". I will try with the new index. Then get back to you.

Best Reagrds,
Zayar Minn


 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Posted on 13/05/2025 12:28:49
Reply

Hi Zayar

I can see you use the "Customer center". There is a new version called Customer Experience Center that can do the same - the only difference is the template that is viewmodel based instead of tag based.

That new one has these 4 options - you want to go with option 1. That will also give you a performance boost on this page.

  • Own orders <- This one
  • Own orders and orders from users with same customer number
  • Own orders and orders made by users that current user can impersonate
  • Own orders and orders made while impersonating
 
Zayar Minn Dynamicweb Employee
Zayar Minn
Posted on 13/05/2025 13:07:24
Reply

Hi Nicolai,

Thank you for your suggestion. I will try it out and get back to you with the results.

Best regards,
Zayar Minn

 

You must be logged in to post in the forum