Nop 4.604 SQL Server error 'for extremely complex queries or queries that reference a very large number of tables or partitions'

Hello,
I am starting to implement a new web site with 100k of records (products), nop 4.604
In a page of category with 100k of products (but with not more than 30 sub-category) we receive follow SQL error:
--------------------------
Microsoft.Data.SqlClient.SqlException (0x80131904): The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at LinqToDB.Data.DataConnection.ExecuteReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteDataReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteDataReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.QueryRunner.ExecuteReaderAsync(CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.ExecuteQueryAsync[T](Query query, IDataContext dataContext, Mapper`1 mapper, Expression expression, Object[] ps, Object[] preambles, Int32 queryNumber, Func`2 func, TakeSkipDelegate skipAction, TakeSkipDelegate takeAction, CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.ExecuteQueryAsync[T](Query query, IDataContext dataContext, Mapper`1 mapper, Expression expression, Object[] ps, Object[] preambles, Int32 queryNumber, Func`2 func, TakeSkipDelegate skipAction, TakeSkipDelegate takeAction, CancellationToken cancellationToken)
   at LinqToDB.Linq.ExpressionQuery`1.GetForEachAsync(Action`1 action, CancellationToken cancellationToken)
   at LinqToDB.AsyncExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken token)
   at Nop.Data.EntityRepository`1.<>c_DisplayClass10_0.<<GetByIdsAsync>g_getByIdsAsync|0>d.MoveNext()
--- End of stack trace from previous location ---
   at Nop.Core.Caching.MemoryCacheManager.GetAsync[T](CacheKey key, Func`1 acquire)
   at Nop.Data.EntityRepository`1.GetByIdsAsync(IList`1 ids, Func`2 getCacheKey, Boolean includeDeleted)
   at Nop.Services.Catalog.ProductService.GetManufacturerFeaturedProductsAsync(Int32 manufacturerId, Int32 storeId)
   at Nop.Web.Factories.CatalogModelFactory.PrepareManufacturerModelAsync(Manufacturer manufacturer, CatalogProductsCommand command)
   at Nop.Web.Controllers.CatalogController.Manufacturer(Int32 manufacturerId, CatalogProductsCommand command)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Nop.Services.Authentication.AuthenticationMiddleware.InvokeAsync(HttpContext context)
   at StackExchange.Profiling.MiniProfilerMiddleware.Invoke(HttpContext context) in C:\projects\dotnet\src\MiniProfiler.AspNetCore\MiniProfilerMiddleware.cs:line 121
   at WebMarkupMin.AspNetCore7.WebMarkupMinMiddleware.InvokeCore(HttpContext context, Boolean useMinification, Boolean useCompression)
   at WebMarkupMin.AspNetCore7.WebMarkupMinMiddleware.InvokeCore(HttpContext context, Boolean useMinification, Boolean useCompression)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Nop.Services.Installation.InstallUrlMiddleware.InvokeAsync(HttpContext context, IWebHelper webHelper)
   at Nop.Services.Common.KeepAliveMiddleware.InvokeAsync(HttpContext context, IWebHelper webHelper)
   at Microsoft.AspNetCore.ResponseCompression.ResponseCompressionMiddleware.InvokeCore(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddlewareImpl.<Invoke>g__Awaited|8_0(ExceptionHandlerMiddlewareImpl middleware, HttpContext context, Task task)
ClientConnectionId:4061aeb9-33e4-40e0-ad72-7adf644e3769
Error Number:8623,State:1,Class:16
--------------------------------
Any idea? I have another web site, nop 4.3 based, with 4k of products without any type of problem.

Thanks in advance

BR
Massimo

..one more detail: the wrong page is the manufacturer page inside nopcommerce. It contains around 500 of product grouped.

In your Catalog Settings page, select both of these and then see if the problem goes away
Ignore ACL rules (sitewide)
Ignore "limit per store" rules (sitewide)

Hi New York I AM a zrmax college. I have checked what you have written  and it is already like that

Are you using SQL Server?
It would be best if you could troubleshoot the query...
You can set up Extended Events sessions to capture SQL queries and save the events to a file or table.

Extended Event session on SQL Server? Never used..
Thanks

Yes.  E.g., you'll want to get the SQL of the long running query
https://www.sqlshack.com/using-sql-server-extended-events-to-monitor-query-performance/

Then you can investigate, and tune it (e.g., create recommended indexes)
https://www.sqlshack.com/tracing-and-tuning-queries-using-sql-server-indexes/