Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

nopCommerce 3.9 on a pretty heavyweight server solution. This error is filling up the log at a rapid rate. Goes on all day long. We do not see the error when browsing the site, it only appears in the log. Would love to know how to solve this. Here is the full error message from the log:

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.SqlInternalConnection.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) 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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Internal.InternalContext.<>c__DisplayClass14`1.<ExecuteSqlQuery>b__13() at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Nop.Data.NopObjectContext.ExecuteStoredProcedureList[TEntity](String commandText, Object[] parameters) in E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Libraries\Nop.Data\NopObjectContext.cs:line 128 at Nop.Services.Catalog.ProductService.SearchProducts(IList`1& filterableSpecificationAttributeOptionIds, Boolean loadFilterableSpecificationAttributeOptionIds, Int32 pageIndex, Int32 pageSize, IList`1 categoryIds, Int32 manufacturerId, Int32 storeId, Int32 vendorId, Int32 warehouseId, Nullable`1 productType, Boolean visibleIndividuallyOnly, Boolean markedAsNewOnly, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, Int32 productTagId, String keywords, Boolean searchDescriptions, Boolean searchManufacturerPartNumber, Boolean searchSku, Boolean searchProductTags, Int32 languageId, IList`1 filteredSpecs, ProductSortingEnum orderBy, Boolean showHidden, Nullable`1 overridePublished) in E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Libraries\Nop.Services\Catalog\ProductService.cs:line 709 at Nop.Web.Factories.CatalogModelFactory.PrepareCategoryModel(Category category, CatalogPagingFilteringModel command) in E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Presentation\Nop.Web\Factories\CatalogModelFactory.cs:line 507 at Nop.Web.Controllers.CatalogController.Category(Int32 categoryId, CatalogPagingFilteringModel command) in E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Presentation\Nop.Web\Controllers\CatalogController.cs:line 128 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) at System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End() at System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) at System.Web.Mvc.Controller.<BeginExecute>b__15(IAsyncResult asyncResult, Controller controller) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End() at System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) at System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) at System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End() at System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) at System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) ClientConnectionId:020a01c5-c71b-4fa8-b334-80b88f25f69e Error Number:-2,State:0,Class:11

Did you do any customization to the source before deploying it?
I ask because I'm seeing references to the source code within the error:
E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Libraries\Nop.Data\NopObjectContext.cs:line 128
E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Libraries\Nop.Services\Catalog\ProductService.cs:line 709
E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Presentation\Nop.Web\Factories\CatalogModelFactory.cs:line 507 E:\CDSNOP3.9_Production\nopCommerce_3.90_Source\Presentation\Nop.Web\Controllers\CatalogController.cs:line 128

That's not normal unless you are viewing the site from within the Visual Studio browser....??

I've had similar errors in my log, and it was because our website was temporarily on a different server(in another region) than our database, and there was occasionally a great enough loss of packets that I'd see this error:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. --->

Once we dealt with the latency issues we were golden.

Extensive updates but not to the files referenced. I saw that too and wondered about that. That unc is the E drive on my development system.

I do not know where the database server is relative to the web server. The error only shows up in the error log unless some of the performance issues are a result of that.

I've chased this back to the execution of this stored procedure: ProductLoadAllPaged in NopObjectContext @ line 128 in the method ExecuteStoredProcedure. In the comments on the line after is a reference to this forum entry: http://www.nopcommerce.com/boards/t/25483/fix-very-important-speed-improvement.aspx

Evidently this has been an issue for a while. So I am wondering if the authors of the original forum post would care to chime in on this?

The unc references are because I have compiled with debug and copied the project to the web server.

Have you executed the sp manually/directly (e.g. in SSMS) to see if it performs OK?

Yup, I looked at the procedure via ssms and tried to run it but there are three variables that I did not know the answers to. Tried running it passing nulls, but hmm, unpredictable results. It is a fairly big and extensive query as well. I tried to get an execution plan for it, but no luck with that either.
This is the method from NopObjectContext: public IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters) where TEntity : BaseEntity, new() which calls this method: var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList(); ExecuteStoredProcedureList is passed to the method along with 31 variables.

There was some discussion between two of the developers for this code regarding performance issues in the forum. I'm hoping that they can offer some insight.

I am getting 4 - 6 entries in the log every minute or so. So it makes perusing the log for other errors very difficult.

I wonder if there is a way to either disable this code, since it apparently isn't working anyway, or just get it to stop writing to the log?

Here is the sp:

ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
(
  @CategoryIds    nvarchar(MAX) = null,  --a list of category IDs (comma-separated list). e.g. 1,2,3
  @ManufacturerId    int = 0,
  @StoreId      int = 0,
  @VendorId      int = 0,
  @WarehouseId    int = 0,
  @ProductTypeId    int = null, --product type identifier, null - load all products
  @VisibleIndividuallyOnly bit = 0,   --0 - load all products , 1 - "visible indivially" only
  @MarkedAsNewOnly  bit = 0,   --0 - load all products , 1 - "marked as new" only
  @ProductTagId    int = 0,
  @FeaturedProducts  bit = null,  --0 featured only , 1 not featured only, null - load all products
  @PriceMin      decimal(18, 4) = null,
  @PriceMax      decimal(18, 4) = null,
  @Keywords      nvarchar(4000) = null,
  @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
  @SearchManufacturerPartNumber bit = 0, -- a value indicating whether to search by a specified "keyword" in manufacturer part number
  @SearchSku      bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
  @SearchProductTags  bit = 0, --a value indicating whether to search by a specified "keyword" in product tags
  @UseFullTextSearch  bit = 0,
  @FullTextMode    int = 0, --0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
  @FilteredSpecs    nvarchar(MAX) = null,  --filter by specification attribute options (comma-separated list of IDs). e.g. 14,15,16
  @LanguageId      int = 0,
  @OrderBy      int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
  @AllowedCustomerRoleIds  nvarchar(MAX) = null,  --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
  @PageIndex      int = 0,
  @PageSize      int = 2147483644,
  @ShowHidden      bit = 0,
  @OverridePublished  bit = null, --null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products
  @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
  @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  /* Products that filtered by keywords */
  CREATE TABLE #KeywordProducts
  (
    [ProductId] int NOT NULL
  )

  DECLARE
    @SearchKeywords bit,
    @OriginalKeywords nvarchar(4000),
    @sql nvarchar(max),
    @sql_orderby nvarchar(max)

  SET NOCOUNT ON
  
  --filter by keywords
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = rtrim(ltrim(@Keywords))
  SET @OriginalKeywords = @Keywords
  IF ISNULL(@Keywords, '') != ''
  BEGIN
    SET @SearchKeywords = 1
    
    IF @UseFullTextSearch = 1
    BEGIN
      --remove wrong chars (' ")
      SET @Keywords = REPLACE(@Keywords, '''', '')
      SET @Keywords = REPLACE(@Keywords, '"', '')
      
      --full-text search
      IF @FullTextMode = 0
      BEGIN
        --0 - using CONTAINS with <prefix_term>
        SET @Keywords = ' "' + @Keywords + '*" '
      END
      ELSE
      BEGIN
        --5 - using CONTAINS and OR with <prefix_term>
        --10 - using CONTAINS and AND with <prefix_term>

        --clean multiple spaces
        WHILE CHARINDEX('  ', @Keywords) > 0
          SET @Keywords = REPLACE(@Keywords, '  ', ' ')

        DECLARE @concat_term nvarchar(100)        
        IF @FullTextMode = 5 --5 - using CONTAINS and OR with <prefix_term>
        BEGIN
          SET @concat_term = 'OR'
        END
        IF @FullTextMode = 10 --10 - using CONTAINS and AND with <prefix_term>
        BEGIN
          SET @concat_term = 'AND'
        END

        --now let's build search string
        declare @fulltext_keywords nvarchar(4000)
        set @fulltext_keywords = N''
        declare @index int    
    
        set @index = CHARINDEX(' ', @Keywords, 0)

        -- if index = 0, then only one field was passed
        IF(@index = 0)
          set @fulltext_keywords = ' "' + @Keywords + '*" '
        ELSE
        BEGIN    
          DECLARE @first BIT
          SET  @first = 1      
          WHILE @index > 0
          BEGIN
            IF (@first = 0)
              SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' '
            ELSE
              SET @first = 0

            SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'          
            SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index)            
            SET @index = CHARINDEX(' ', @Keywords, 0)
          end
          
          -- add the last field
          IF LEN(@fulltext_keywords) > 0
            SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"'  
        END
        SET @Keywords = @fulltext_keywords
      END
    END
    ELSE
    BEGIN
      --usual search by PATINDEX
      SET @Keywords = '%' + @Keywords + '%'
    END
    --PRINT @Keywords

    --product name
    SET @sql = '
    INSERT INTO #KeywordProducts ([ProductId])
    SELECT p.Id
    FROM Product p with (NOLOCK)
    WHERE '
    IF @UseFullTextSearch = 1
      SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) '
    ELSE
      SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '


    --localized product name
    SET @sql = @sql + '
    UNION
    SELECT lp.EntityId
    FROM LocalizedProperty lp with (NOLOCK)
    WHERE
      lp.LocaleKeyGroup = N''Product''
      AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
      AND lp.LocaleKey = N''Name'''
    IF @UseFullTextSearch = 1
      SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
    ELSE
      SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
  

    IF @SearchDescriptions = 1
    BEGIN
      --product short description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '


      --product full description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '



      --localized product short description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''ShortDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
        

      --localized product full description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''FullDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
    END

    --manufacturer part number (exact match)
    IF @SearchManufacturerPartNumber = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE p.[ManufacturerPartNumber] = @OriginalKeywords '
    END

    --SKU (exact match)
    IF @SearchSku = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE p.[Sku] = @OriginalKeywords '
    END

    IF @SearchProductTags = 1
    BEGIN
      --product tags (exact match)
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id
      WHERE pt.[Name] = @OriginalKeywords '

      --localized product tags
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id
      WHERE
        lp.LocaleKeyGroup = N''ProductTag''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''Name''
        AND lp.[LocaleValue] = @OriginalKeywords '
    END

    --PRINT (@sql)
    EXEC sp_executesql @sql, N'@Keywords nvarchar(4000), @OriginalKeywords nvarchar(4000)', @Keywords, @OriginalKeywords

  END
  ELSE
  BEGIN
    SET @SearchKeywords = 0
  END

  --filter by category IDs
  SET @CategoryIds = isnull(@CategoryIds, '')  
  CREATE TABLE #FilteredCategoryIds
  (
    CategoryId int not null
  )
  INSERT INTO #FilteredCategoryIds (CategoryId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@CategoryIds, ',')  
  DECLARE @CategoryIdsCount int  
  SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)

  --filter by customer role IDs (access control list)
  SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '')  
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId int not null
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@AllowedCustomerRoleIds, ',')
  DECLARE @FilteredCustomerRoleIdsCount int  
  SET @FilteredCustomerRoleIdsCount = (SELECT COUNT(1) FROM #FilteredCustomerRoleIds)
  
  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int
  SET @RowsToReturn = @PageSize * (@PageIndex + 1)  
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize + 1
  
  CREATE TABLE #DisplayOrderTmp
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )

  SET @sql = '
  SELECT p.Id
  FROM
    Product p with (NOLOCK)'
  
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
      ON p.Id = pcm.ProductId'
  END
  
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
      ON p.Id = pmm.ProductId'
  END
  
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_ProductTag_Mapping pptm with (NOLOCK)
      ON p.Id = pptm.Product_Id'
  END
  
  --searching by keywords
  IF @SearchKeywords = 1
  BEGIN
    SET @sql = @sql + '
    JOIN #KeywordProducts kp
      ON  p.Id = kp.ProductId'
  END
  
  SET @sql = @sql + '
  WHERE
    p.Deleted = 0'
  
  --filter by category
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)'
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by manufacturer
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max))
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by vendor
  IF @VendorId > 0
  BEGIN
    SET @sql = @sql + '
    AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max))
  END
  
  --filter by warehouse
  IF @WarehouseId > 0
  BEGIN
    --we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1)
    --but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance
    SET @sql = @sql + '
    AND  
      (
        (p.UseMultipleWarehouses = 0 AND
          p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ')
        OR
        (p.UseMultipleWarehouses > 0 AND
          EXISTS (SELECT 1 FROM ProductWarehouseInventory [pwi]
          WHERE [pwi].WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ' AND [pwi].ProductId = p.Id))
      )'
  END
  
  --filter by product type
  IF @ProductTypeId is not null
  BEGIN
    SET @sql = @sql + '
    AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max))
  END
  
  --filter by "visible individually"
  IF @VisibleIndividuallyOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.VisibleIndividually = 1'
  END
  
  --filter by "marked as new"
  IF @MarkedAsNewOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.MarkAsNew = 1
    AND (getutcdate() BETWEEN ISNULL(p.MarkAsNewStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.MarkAsNewEndDateTimeUtc, ''1/1/2999''))'
  END
  
  --filter by product tag
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
  END
  
  --"Published" property
  IF (@OverridePublished is null)
  BEGIN
    --process according to "showHidden"
    IF @ShowHidden = 0
    BEGIN
      SET @sql = @sql + '
      AND p.Published = 1'
    END
  END
  ELSE IF (@OverridePublished = 1)
  BEGIN
    --published only
    SET @sql = @sql + '
    AND p.Published = 1'
  END
  ELSE IF (@OverridePublished = 0)
  BEGIN
    --unpublished only
    SET @sql = @sql + '
    AND p.Published = 0'
  END
  
  --show hidden
  IF @ShowHidden = 0
  BEGIN
    SET @sql = @sql + '
    AND p.Deleted = 0
    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))'
  END
  
  --min price
  IF @PriceMin is not null
  BEGIN
    SET @sql = @sql + '
    AND (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')'
  END
  
  --max price
  IF @PriceMax is not null
  BEGIN
    SET @sql = @sql + '
    AND (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')'
  END
  
  --show hidden and ACL
  IF  @ShowHidden = 0 and @FilteredCustomerRoleIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.SubjectToAcl = 0 OR EXISTS (
      SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
      WHERE
        [fcr].CustomerRoleId IN (
          SELECT [acl].CustomerRoleId
          FROM [AclRecord] acl with (NOLOCK)
          WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product''
        )
      ))'
  END
  
  --filter by store
  IF @StoreId > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
      ))'
  END
  
    --prepare filterable specification attribute option identifier (if requested)
    IF @LoadFilterableSpecificationAttributeOptionIds = 1
  BEGIN    
    CREATE TABLE #FilterableSpecs
    (
      [SpecificationAttributeOptionId] int NOT NULL
    )
        DECLARE @sql_filterableSpecs nvarchar(max)
        SET @sql_filterableSpecs = '
          INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
          SELECT DISTINCT [psam].SpecificationAttributeOptionId
          FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
              WHERE [psam].[AllowFiltering] = 1
              AND [psam].[ProductId] IN (' + @sql + ')'

        EXEC sp_executesql @sql_filterableSpecs

    --build comma separated list of filterable identifiers
    SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
    FROM #FilterableSpecs

    DROP TABLE #FilterableSpecs
  END

  --filter by specification attribution options
  SET @FilteredSpecs = isnull(@FilteredSpecs, '')  
  CREATE TABLE #FilteredSpecs
  (
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@FilteredSpecs, ',')

    CREATE TABLE #FilteredSpecsWithAttributes
  (
        SpecificationAttributeId int not null,
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecsWithAttributes (SpecificationAttributeId, SpecificationAttributeOptionId)
  SELECT sao.SpecificationAttributeId, fs.SpecificationAttributeOptionId
    FROM #FilteredSpecs fs INNER JOIN SpecificationAttributeOption sao ON sao.Id = fs.SpecificationAttributeOptionId
    ORDER BY sao.SpecificationAttributeId

    DECLARE @SpecAttributesCount int  
  SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecsWithAttributes)
  IF @SpecAttributesCount > 0
  BEGIN
    --do it for each specified specification option
    DECLARE @SpecificationAttributeOptionId int
        DECLARE @SpecificationAttributeId int
        DECLARE @LastSpecificationAttributeId int
        SET @LastSpecificationAttributeId = 0
    DECLARE cur_SpecificationAttributeOption CURSOR FOR
    SELECT SpecificationAttributeId, SpecificationAttributeOptionId
    FROM #FilteredSpecsWithAttributes

    OPEN cur_SpecificationAttributeOption
        FOREACH:
            FETCH NEXT FROM cur_SpecificationAttributeOption INTO @SpecificationAttributeId, @SpecificationAttributeOptionId
            IF (@LastSpecificationAttributeId <> 0 AND @SpecificationAttributeId <> @LastSpecificationAttributeId OR @@FETCH_STATUS <> 0)
          SET @sql = @sql + '
        AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) + '))'
            SET @LastSpecificationAttributeId = @SpecificationAttributeId
    IF @@FETCH_STATUS = 0 GOTO FOREACH
    CLOSE cur_SpecificationAttributeOption
    DEALLOCATE cur_SpecificationAttributeOption
  END

  --sorting
  SET @sql_orderby = ''  
  IF @OrderBy = 5 /* Name: A to Z */
    SET @sql_orderby = ' p.[Name] ASC'
  ELSE IF @OrderBy = 6 /* Name: Z to A */
    SET @sql_orderby = ' p.[Name] DESC'
  ELSE IF @OrderBy = 10 /* Price: Low to High */
    SET @sql_orderby = ' p.[Price] ASC'
  ELSE IF @OrderBy = 11 /* Price: High to Low */
    SET @sql_orderby = ' p.[Price] DESC'
  ELSE IF @OrderBy = 15 /* creation date */
    SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
  ELSE /* default sorting, 0 (position) */
  BEGIN
    --category position (display order)
    IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC'
    
    --manufacturer position (display order)
    IF @ManufacturerId > 0
    BEGIN
      IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
      SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC'
    END
    
    --name
    IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
    SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
  END
  
  SET @sql = @sql + '
  ORDER BY' + @sql_orderby
  
    SET @sql = '
    INSERT INTO #DisplayOrderTmp ([ProductId])' + @sql

  --PRINT (@sql)
  EXEC sp_executesql @sql

  DROP TABLE #FilteredCategoryIds
  DROP TABLE #FilteredSpecs
    DROP TABLE #FilteredSpecsWithAttributes
  DROP TABLE #FilteredCustomerRoleIds
  DROP TABLE #KeywordProducts

  CREATE TABLE #PageIndex
  (
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )
  INSERT INTO #PageIndex ([ProductId])
  SELECT ProductId
  FROM #DisplayOrderTmp
  GROUP BY ProductId
  ORDER BY min([Id])

  --total records
  SET @TotalRecords = @@rowcount
  
  DROP TABLE #DisplayOrderTmp

  --return products
  SELECT TOP (@RowsToReturn)
    p.*
  FROM
    #PageIndex [pi]
    INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
  WHERE
    [pi].IndexId > @PageLowerBound AND
    [pi].IndexId < @PageUpperBound
  ORDER BY
    [pi].IndexId
  
  DROP TABLE #PageIndex
END

hemilis wrote:
You should be able to see the input parameters using the SQL Profiler.  I don't recall which event - I think you'll need to capture RPC:Completed events.   (You can Google for best ways to 'SQL Profiler capture parameters')

I quit using the sql sp for this and went back to the linq query. It is working a lot better than sql. The sp ran up a bunch of cpu time on the shared sql server at Everleap and they shut our site down. Linq is a lot faster if you have the web server iron (mostly available memory) to run the linq query without paging operations.