What the SP CategoryLoadAllPaged does?

I'm looking why the page takes so long to load.
I've been looking through the code after adding more than 115,000 categories to the Category table and found that SP CategoryLoadAllPaged slow down a lot the start of the page, what i cannot found is the relation this SP have when the page starts.

After I commented the SP code the page loads a lot faster.

Please can you help me telling me if commenting this SP affects in some way other NOP functionality.

Thanks.

The SP is:

ALTER PROCEDURE [dbo].[CategoryLoadAllPaged]
(
    @ShowHidden         BIT = 0,
    @Name               NVARCHAR(MAX) = NULL,
    @StoreId            INT = 0,
    @CustomerRoleIds  NVARCHAR(MAX) = NULL,
    @PageIndex      INT = 0,
  @PageSize      INT = 2147483644,
    @TotalRecords    INT = NULL OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON

    --filter by customer role IDs (access control list)
  SET @CustomerRoleIds = ISNULL(@CustomerRoleIds, '')
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId INT NOT NULL
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data AS INT) FROM [nop_splitstring_to_table](@CustomerRoleIds, ',')
  DECLARE @FilteredCustomerRoleIdsCount INT = (SELECT COUNT(1) FROM #FilteredCustomerRoleIds)

    --ordered categories
    CREATE TABLE #OrderedCategoryIds
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [CategoryId] int NOT NULL
  )
    
    --get max length of DisplayOrder and Id columns (used for padding Order column)
    DECLARE @lengthId INT = (SELECT LEN(MAX(Id)) FROM [Category])
    DECLARE @lengthOrder INT = (SELECT LEN(MAX(DisplayOrder)) FROM [Category])

    --get category tree
    ;WITH [CategoryTree]
    AS (SELECT [Category].[Id] AS [Id],
    (select RIGHT(REPLICATE('0', @lengthOrder)+ RTRIM(CAST([Category].[DisplayOrder] AS NVARCHAR(MAX))), @lengthOrder)) + '-' + (select RIGHT(REPLICATE('0', @lengthId)+ RTRIM(CAST([Category].[Id] AS NVARCHAR(MAX))), @lengthId))  AS [Order]
        FROM [Category] WHERE [Category].[ParentCategoryId] = 0
        UNION ALL
        SELECT [Category].[Id] AS [Id],
    [CategoryTree].[Order] + '|' + (select RIGHT(REPLICATE('0', @lengthOrder)+ RTRIM(CAST([Category].[DisplayOrder] AS NVARCHAR(MAX))), @lengthOrder)) + '-' + (select RIGHT(REPLICATE('0', @lengthId)+ RTRIM(CAST([Category].[Id] AS NVARCHAR(MAX))), @lengthId))  AS [Order]
        FROM [Category]
        INNER JOIN [CategoryTree] ON [CategoryTree].[Id] = [Category].[ParentCategoryId])
    INSERT INTO #OrderedCategoryIds ([CategoryId])
    SELECT [Category].[Id]
    FROM [CategoryTree]
    RIGHT JOIN [Category] ON [CategoryTree].[Id] = [Category].[Id]

    --filter results
    WHERE [Category].[Deleted] = 0
    AND (@ShowHidden = 1 OR [Category].[Published] = 1)
    AND (@Name IS NULL OR @Name = '' OR [Category].[Name] LIKE ('%' + @Name + '%'))
    AND (@ShowHidden = 1 OR @FilteredCustomerRoleIdsCount  = 0 OR [Category].[SubjectToAcl] = 0
        OR EXISTS (SELECT 1 FROM #FilteredCustomerRoleIds [roles] WHERE [roles].[CustomerRoleId] IN
            (SELECT [acl].[CustomerRoleId] FROM [AclRecord] acl WITH (NOLOCK) WHERE [acl].[EntityId] = [Category].[Id] AND [acl].[EntityName] = 'Category')
        )
    )
    AND (@StoreId = 0 OR [Category].[LimitedToStores] = 0
        OR EXISTS (SELECT 1 FROM [StoreMapping] sm WITH (NOLOCK)
      WHERE [sm].[EntityId] = [Category].[Id] AND [sm].[EntityName] = 'Category' AND [sm].[StoreId] = @StoreId
    )
    )
    ORDER BY ISNULL([CategoryTree].[Order], 1)

    --total records
    SET @TotalRecords = @@ROWCOUNT

    --paging
    SELECT [Category].* FROM #OrderedCategoryIds AS [Result] INNER JOIN [Category] ON [Result].[CategoryId] = [Category].[Id]
    WHERE ([Result].[Id] > @PageSize * @PageIndex AND [Result].[Id] <= @PageSize * (@PageIndex + 1))
    ORDER BY [Result].[Id]

    DROP TABLE #FilteredCustomerRoleIds
    DROP TABLE #OrderedCategoryIds
END

-- delete unused functions
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[nop_getnotnullnotempty]') AND type = N'FN')
BEGIN
  DROP FUNCTION [nop_getnotnullnotempty]
END

If your store not multistore and you're not using category customer role wise then disable below settings from catalog settings

Ignore ACL rules (sitewide)  
Ignore "limit per store" rules (sitewide)  

Thanks for the fast response!

Yes I already do that and works! It load faster.

But then another error appeared, when i try to enter a product throws the next error:

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose)
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady)
System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
System.Data.SqlClient.SqlDataReader.get_MetaData()
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, string resetOptionsString)
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, bool async, int timeout, out Task task, bool asyncWrite, SqlDataReader ds)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+Enumerator.BufferlessMoveNext(DbContext _, bool buffer)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute<TState, TResult>(TState state, Func<DbContext, TState, TResult> operation, Func<DbContext, TState, ExecutionResult<TResult>> verifySucceeded)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+Enumerator.MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities<TOut, TIn>(IEnumerable<TOut> results, QueryContext queryContext, IList<EntityTrackingInfo> entityTrackingInfos, IList<Func<TIn, object>> entityAccessors)+MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor<T>+EnumeratorExceptionInterceptor.MoveNext()
System.Collections.Generic.List<T>.AddEnumerable(IEnumerable<T> enumerable)
System.Linq.Enumerable.ToList<TSource>(IEnumerable<TSource> source)
Nop.Services.Catalog.ProductService.SearchProducts(out IList<int> filterableSpecificationAttributeOptionIds, bool loadFilterableSpecificationAttributeOptionIds, int pageIndex, int pageSize, IList<int> categoryIds, int manufacturerId, int storeId, int vendorId, int warehouseId, Nullable<ProductType> productType, bool visibleIndividuallyOnly, bool markedAsNewOnly, Nullable<bool> featuredProducts, Nullable<decimal> priceMin, Nullable<decimal> priceMax, int productTagId, string keywords, bool searchDescriptions, bool searchManufacturerPartNumber, bool searchSku, bool searchProductTags, int languageId, IList<int> filteredSpecs, ProductSortingEnum orderBy, bool showHidden, Nullable<bool> overridePublished) in ProductService.cs
+
            var products = _dbContext.EntityFromSql<Product>("ProductLoadAllPaged",
Nop.Web.Factories.CatalogModelFactory.PrepareCategoryModel(Category category, CatalogPagingFilteringModel command) in CatalogModelFactory.cs
+
            var products = _productService.SearchProducts(out IList<int> filterableSpecificationAttributeOptionIds,
Nop.Web.Controllers.CatalogController.Category(int categoryId, CatalogPagingFilteringModel command) in CatalogController.cs
+
            var model = _catalogModelFactory.PrepareCategoryModel(category, command);
lambda_method(Closure , object , object[] )
Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor+SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
WebMarkupMin.AspNetCore2.WebMarkupMinMiddleware.Invoke(HttpContext context)
StackExchange.Profiling.MiniProfilerMiddleware.Invoke(HttpContext context) in MiniProfilerMiddleware.cs
Nop.Services.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) in AuthenticationMiddleware.cs
+
            await _next(context);
Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
Nop.Services.Installation.InstallUrlMiddleware.Invoke(HttpContext context, IWebHelper webHelper) in InstallUrlMiddleware.cs
+
            await _next(context);
Nop.Services.Common.KeepAliveMiddleware.Invoke(HttpContext context, IWebHelper webHelper) in KeepAliveMiddleware.cs
+
            await _next(context);
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.ResponseCompression.ResponseCompressionMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)
Nop.Web.Framework.Infrastructure.Extensions.ApplicationBuilderExtensions+<>c.<UseNopExceptionHandler>b__1_1(HttpContext context) in ApplicationBuilderExtensions.cs
+
                        ExceptionDispatchInfo.Throw(exception);
Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)