Answer.
One our client wanted to increase speed their sait. We run SQL Server Profiler and scan for heavy sql commands.
Sometimes Entity Framework generate draft SQL code and SQL Server Engine must full scan all tables from SQL code.
Sometimes Entity Framework can not generate SQL code and developers must to cut queries (get all data to their C# classes and must filter then).
The results depend on the amount of data.We rewrited classes
ProductService interface IProductService
CategoryService interface ICategoryService
ProductTagService interface IProductTagService
ManufacturerService interface IManufacturerService
LocalizedEntityService interface ILocalizedEntityService
Example.
Method from ICategoryService.
public override IPagedList<Category> GetAllCategories(string categoryName = "",
int pageIndex = 0, int pageSize = int.MaxValue, bool showHidden = false)Original code get all data.
var query = _categoryRepository.Table;
if (!showHidden)
query = query.Where(c => c.Published);
if (!String.IsNullOrWhiteSpace(categoryName))
query = query.Where(c => c.Name.Contains(categoryName));
query = query.Where(c => !c.Deleted);
query = query.OrderBy(c => c.ParentCategoryId).ThenBy(c => c.DisplayOrder);
//ACL (access control list)
if (!showHidden)
{
var allowedCustomerRolesIds = _workContext.CurrentCustomer.CustomerRoles
.Where(cr => cr.Active).Select(cr => cr.Id).ToList();
query = from c in query
join acl in _aclRepository.Table on c.Id equals acl.EntityId into c_acl
from acl in c_acl.DefaultIfEmpty()
where !c.SubjectToAcl || (acl.EntityName == "Category" && allowedCustomerRolesIds.Contains(acl.CustomerRoleId))
select c;
//only distinct categories (group by ID)
query = from c in query
group c by c.Id
into cGroup
orderby cGroup.Key
select cGroup.FirstOrDefault();
query = query.OrderBy(c => c.ParentCategoryId).ThenBy(c => c.DisplayOrder);
}
var unsortedCategories = query.ToList();
//sort categories
var sortedCategories = unsortedCategories.SortCategoriesForTree();
//paging
return new PagedList<Category>(sortedCategories, pageIndex, pageSize);
Stored procedure got FAST ONLY rows what you want.
CREATE PROCEDURE [dbo].[FoxNetSoft_Category_GetAllCategories]
(
@categoryName nvarchar(MAX), --Category name
@AllowedCustomerRoleIds nvarchar(MAX) , --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
@PageIndex int , --Page index
@PageSize int , --Page size
@ShowHidden bit , --A value indicating whether to show hidden records
@TotalRecords int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
if @categoryName is null
set @categoryName=''
--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,
[CategoryId] int NOT NULL
)
if @ShowHidden=1
begin
insert into #DisplayOrderTmp (CategoryId)
select C.Id
from Category C
where C.Name like '%'+@categoryName+'%'
and C.Deleted=0
Order by C.ParentCategoryId,C.DisplayOrder
end
else
begin
--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, ',')
insert into #DisplayOrderTmp (CategoryId)
select C.Id
from Category C
where C.Name like '%'+@categoryName+'%'
and C.Published=1
and C.Deleted=0
and (C.SubjectToAcl = 0 OR EXISTS (
SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
WHERE
[fcr].CustomerRoleId IN (
SELECT [acl].CustomerRoleId
FROM [AclRecord] acl
WHERE [acl].EntityId = C.Id AND [acl].EntityName = 'Category'
)
))
Order by C.ParentCategoryId,C.DisplayOrder
drop table #FilteredCustomerRoleIds
end
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[CategoryId] int NOT NULL
)
INSERT INTO #PageIndex ([CategoryId])
SELECT [CategoryId]
FROM #DisplayOrderTmp
GROUP BY [CategoryId]
ORDER BY min([Id])
--total records
SET @TotalRecords = @@rowcount
DROP TABLE #DisplayOrderTmp
--return categories
SELECT TOP (@RowsToReturn)
C.*
FROM
#PageIndex [pi]
INNER JOIN Category C on C.Id = [pi].[CategoryId]
WHERE
[pi].IndexId > @PageLowerBound AND
[pi].IndexId < @PageUpperBound
ORDER BY
[pi].IndexId
DROP TABLE #PageIndex
END
But in another method
public override Category GetCategoryById(int categoryId)speed is equal
You must understand.
MS SQL Server have own builting cache and can recreate
plan of queries. Example, first run 4 second, Second run 0.1 second (for the same queries).
The results depend on the amount of data.MS SQL Server parses queries for using index or full scan all table.
We recommended to run SQL Server Profiler for your Sait.