MSSQL Provider V2. Performance Optimization.

Plugin to speed up website performance. Performance Optimization.

How does it work?
Plugin replaces standard mechanism for retrieving data Entity Framework Linq working on MS SQL Stored Procedures. You can download and try this plugin. Unregistered version is fully operational. It has only one limitation – trial period 30 days. After the expiration, plugin will be disabled.

How many times the speed increases?
Increase depends on data size.
For example, we have chosen a method GetProductVariantsByProductIds.

Benchmarks.
240 products in the same category. Each product has 20 subproducts (ProductVariant). 60 products per page. User opens this category.

Built-in mechanism.
First launch
CatalogController: Category.duration = 00:00:01.1357422
Second launch
CatalogController: Category.duration = 00:00:00.5986328

After plugin installation (via Stored Procedures)
First launch
CatalogController:Category. duration=00:00:00.6396484
Second launch
CatalogController:Category. duration=00:00:00.2910157

Plugin works ONLY on MS SQL Server 2005/2008/2008R2/2012+ Azure.

Compatible for NopCommerce 3.2/3.3/3.4

Direct link for download http://www.foxnetsoft.com/content/files/FoxNetSoft.MSSQLProvider.zip

Link to our site
http://www.foxnetsoft.com/nopcommerce-mssqlprovider

Do you have any measures of this optimization?
Does it complicate nopCommerce's maintenance?

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.

Answer 2.
NopCommerce are using Autofac (.NET IoC container)
We create new classes from these interfaces.

ProductService interface IProductService
CategoryService interface ICategoryService
ProductTagService interface IProductTagService
ManufacturerService interface IManufacturerService
LocalizedEntityService interface ILocalizedEntityService

NopCommerce and plugins get classes using IoC container.

Add support version 3.0.

How much performance improvement?

have you tested the performance? Can you share some details?

We shell plan to write tests next Wednesday.

P.S.
We'll write a script for create 15000 demo products.

Thank you for your explanations.

Script for creating many products and categories in your store.

https://www.nopcommerce.com/boards/t/23916/script-for-creating-many-products-in-your-store.aspx

MSSQL Script for creating many products in your Store.
Script scans your tables and create new categories and products (copy pictures and attributes)
Script doesn't create new attributes.
You can change selected values
We wrote this script for version 3.00. For version 2.80 you must delete some lines, read comments in the script.

exec us_FoxNetSoft_Create_DemoData  
  @kol_category_root=10,   -- count new root categories
  @kol_category=200,          -- count new categories
  @kol_product=30000         -- count new products