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