Thread: Fine tuning DB

Hello everyone,

I am not that experienced in SQL. I need to finetune StoredProcedure in order to boost site performance as much as possible. In the manuals I have seen this article:
http://manual.aspdotnetstorefront.co...rge-sites.aspx

In it there is following text:

These optimizations are best done by a skilled DBA

aspdnsf_GetProducts
The aspdnsf_GetProducts stored procedure is used in all entity packages and implements every filter option that the store supports. This can make this procedure very expensive.

The first step is to remove the filter tables that are not needed. Below is the FROM clause from one of the queries in the procedure. If you are not using some of these table you can remove them from the query and remove the associated WHERE criteria.

FROM
product p with (nolock)
join #displayorder do on p.ProductID = do.ProductID
left join ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
left join productcategory pc with (nolock) on p.ProductID = pc.ProductID
left join productsection ps with (nolock) on p.ProductID = ps.ProductID
left join ProductManufacturer pm with (nolock) on p.ProductID = pm.ProductID
left join ProductDistributor pd with (nolock) on p.ProductID = pd.ProductID
left join ProductGenre px with (nolock) on p.ProductID = px.ProductID
left join ProductVector px2 with (nolock) on p.ProductID = px2.ProductID
left join ProductLocaleSetting pl with (nolock) on p.ProductID = pl.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
left join ProductAffiliate pa with (nolock) on p.ProductID = pa.ProductID
join #inventoryfilter i on pv.VariantID = i.VariantID

You can also remove other queries related to tables that you are not using. There are other WHERE criteria that could be removed also. If you’re not using start and stop dates you can remove this criteria

((@IncludeAll = 1) or (getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')))

Problem is I can get around the mssql server, but I am not skilled DB.
I do not use anywhere vector, distributor and genre. Logically thinking I should remove those calls... but I am uncertain what and where to do it.

Any help is appreciated.


Thanks

Performance wise honestly using CacheEntityPageHTML appconfig with a few modifications (better handling of things like the SEfields) gives more of performance boost when it comes to entity pages. Due to not having to call the database every time.

As to how to modify aspdnsf_GetProducts. Try expanding the database in Management Studio. Then expand Programmability and then expand stored procedures. Then find aspdnsf_GetProducts in the list. Right click aspdnsf_GetProducts and select Modify. Then do the modifications in the query window the opens up. If that doesn't help can you tell me what version of the store are you using? Also are you using full text/advanced search?

Thanks for your reply.

I am using 9.3 StoreFront. I am using VibeTrib search with FTS enabled.


PS (on side note)
When I use CacheEntityPageHTML it give huge performance boost, however VibeTrib filtering does not work from category and manufacturer pages. It does work from global search. How can I enable it to work also from category and manufacturer pages?
Thanks

Based on your help, this is what I did to that stored procedure, I have commented out genre and vector.

Code:

 USE [ligh52]
GO
/****** Object:  StoredProcedure [dbo].[aspdnsf_GetProducts]    Script Date: 11/10/2012 10:28:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER proc [dbo].[aspdnsf_GetProducts]
    @categoryID      int = null,  
    @sectionID       int = null,  
    @manufacturerID  int = null,  
    @distributorID   int = null,  
    --@genreID         int = null,  
    --@vectorID        int = null,  
    @localeID        int = null,  
    @CustomerLevelID int = null,  
    @affiliateID     int = null,  
    @ProductTypeID   int = null,  
    @ViewType        bit = 1, -- 0 = all variants, 1 = one variant  
    @sortEntity      int = 0, -- 1 = category, 2 = section, 3 = manufacturer, 4 = distributor, 5= genre, 6 = vector  
    @pagenum         int = 1,  
    @pagesize        int = null,  
    @StatsFirst      tinyint = 1,  
    @searchstr       nvarchar(4000) = null,  
    @extSearch       tinyint = 0,  
    @publishedonly   tinyint = 0,  
    @ExcludePacks    tinyint = 0,  
    @ExcludeKits     tinyint = 0,  
    @ExcludeSysProds tinyint = 0,  
    @InventoryFilter int = 0,  --  will only show products with an inventory level GREATER OR EQUAL TO than the number specified in this parameter, set to -1 to disable inventory filtering  
    @sortEntityName  varchar(20) = '', -- usely only when the entity id is provided, allowed values: category, section, manufacturer, distributor, genre, vector  
    @localeName      varchar(20) = '',  
    @OnSaleOnly      tinyint = 0,  
    @IncludeAll      bit = 0, -- Don't filter products that have a start date in the future or a stop date in the past  
	@storeID		 int = 1,
	@filterProduct	 bit = 0,
	@sortby			 varchar(10) = 'default',
	@since			 int = 180  -- best sellers in the last "@since" number of days
	
  
AS  
BEGIN  
  
    SET NOCOUNT ON   
  
    DECLARE @rcount int
    DECLARE @productfiltersort table (rownum int not null identity  primary key, productid int not null, price money null, saleprice money null, displayorder int not null, VariantID int not null, VariantDisplayOrder int not null, ProductName nvarchar(400) null, VariantName nvarchar(400) null)
    DECLARE @productfilter table (rownum int not null identity  primary key, productid int not null, price money null, saleprice money null,  displayorder int not null, VariantID int not null, VariantDisplayOrder int not null, ProductName nvarchar(400) null, VariantName nvarchar(400) null)
	DECLARE @FilterProductsByAffiliate tinyint, @FilterProductsByCustomerLevel tinyint, @HideProductsWithLessThanThisInventoryLevel int  
    CREATE TABLE #displayorder ([name] nvarchar (800), productid int not null primary key, displayorder int not null)  
    CREATE TABLE #inventoryfilter (productid int not null, variantid int not null, InvQty int not null)  
    CREATE CLUSTERED INDEX tmp_inventoryfilter ON #inventoryfilter (productid, variantid)  
  
    DECLARE @custlevelcount int, @sectioncount int, @localecount int, @affiliatecount int, @categorycount int, @CustomerLevelFilteringIsAscending bit, @distributorcount int, @manufacturercount int  
  
	DECLARE @ftsenabled tinyint
	
	SET @ftsenabled = 0
	
	IF ((SELECT DATABASEPROPERTY(db_name(db_id()),'IsFulltextEnabled')) = 1 
		AND EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[KeyWordSearch]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
		AND EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetValidSearchString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')))
	BEGIN
		SET @ftsenabled = 1
	END
  
    SET @FilterProductsByAffiliate = (SELECT TOP 1 case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByAffiliate' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc)
    SET @FilterProductsByCustomerLevel = (SELECT TOP 1 case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByCustomerLevel' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc)
    SET @HideProductsWithLessThanThisInventoryLevel = (SELECT TOP 1 case ConfigValue when -1 then 0 else ConfigValue end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'HideProductsWithLessThanThisInventoryLevel' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc)
    
    IF @InventoryFilter <> -1 and (@HideProductsWithLessThanThisInventoryLevel > @InventoryFilter or @HideProductsWithLessThanThisInventoryLevel  = -1)  
        SET @InventoryFilter  = @HideProductsWithLessThanThisInventoryLevel  
  
    SET @categoryID      = nullif(@categoryID, 0)  
    SET @sectionID       = nullif(@sectionID, 0)  
    SET @manufacturerID  = nullif(@manufacturerID, 0)  
    SET @distributorID   = nullif(@distributorID, 0)  
    --SET @genreID         = nullif(@genreID, 0)  
    --SET @vectorID        = nullif(@vectorID, 0)  
    SET @affiliateID     = nullif(@affiliateID, 0)  
    SET @ProductTypeID   = nullif(@ProductTypeID, 0)  
  
  
    SET @CustomerLevelFilteringIsAscending  = 0  
    SET @CustomerLevelFilteringIsAscending = (SELECT TOP 1 case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterByCustomerLevelIsAscending' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc) 
  
    IF @localeID is null and ltrim(rtrim(@localeName)) <> ''  
        SELECT @localeID = LocaleSettingID FROM dbo.LocaleSetting with (nolock) WHERE Name = ltrim(rtrim(@localeName))  
  
    select @categorycount     = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productcategory') and si.indid < 2 and type = 'u'  
    select @sectioncount      = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productsection') and si.indid < 2 and type = 'u'  
    select @localecount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductLocaleSetting') and si.indid < 2 and type = 'u'  
    select @custlevelcount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductCustomerLevel') and si.indid < 2 and type = 'u'  
    select @affiliatecount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductAffiliate') and si.indid < 2 and type = 'u'  
    select @distributorcount  = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductDistributor') and si.indid < 2 and type = 'u'  
    --select @genrecount        = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductGenre') and si.indid < 2 and type = 'u'  
    --select @vectorcount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductVector') and si.indid < 2 and type = 'u'  
    select @manufacturercount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductManufacturer') and si.indid < 2 and type = 'u'  
  
  
    -- get page size  
    IF @pagesize is null or @pagesize = 0 BEGIN  
        IF @categoryID is not null  
            SELECT @pagesize = PageSize FROM dbo.Category with (nolock) WHERE categoryID = @categoryID  
        ELSE IF @sectionID is not null  
            SELECT @pagesize = PageSize FROM dbo.Section with (nolock) WHERE sectionID = @sectionID  
        ELSE IF @manufacturerID is not null  
            SELECT @pagesize = PageSize FROM dbo.Manufacturer with (nolock) WHERE manufacturerID = @manufacturerID  
        ELSE IF @distributorID is not null  
            SELECT @pagesize = PageSize FROM dbo.Distributor with (nolock) WHERE distributorID = @distributorID  
        --ELSE IF @genreID is not null  
        --    SELECT @pagesize = PageSize FROM dbo.Genre with (nolock) WHERE genreID = @genreID  
        --ELSE IF @vectorID is not null  
        --    SELECT @pagesize = PageSize FROM dbo.Vector with (nolock) WHERE vectorID = @vectorID  
        ELSE   
            SELECT @pagesize = convert(int, ConfigValue) FROM dbo.AppConfig with (nolock) WHERE [Name] = 'Default_CategoryPageSize'  
    END  
  
    IF @pagesize is null or @pagesize = 0  
        SET @pagesize = 20  
  
    -- get sort order  
    IF @sortEntity = 1 or @sortEntityName = 'category' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductCategory a with (nolock) inner join (select distinct a.ProductID from ProductCategory a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b  on a.ProductID = b.ProductID where categoryID = @categoryID 
    END  
    ELSE IF @sortEntity = 2 or @sortEntityName = 'section' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductSection a with (nolock) inner join (select distinct a.ProductID from ProductSection a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where sectionId = @sectionID
    END  
    ELSE IF @sortEntity = 3 or @sortEntityName = 'manufacturer' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductManufacturer a with (nolock) inner join (select distinct a.ProductID from ProductManufacturer a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where ManufacturerID = @manufacturerID
    END  
    ELSE IF @sortEntity = 4 or @sortEntityName = 'distributor' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductDistributor a with (nolock) inner join (select distinct a.ProductID from ProductDistributor a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where DistributorID = @distributorID
    END  
    --ELSE IF @sortEntity = 5 or @sortEntityName = 'genre' BEGIN  
    --    INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductGenre a with (nolock) inner join (select distinct a.ProductID from ProductGenre a with (nolock) 
    --    left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where GenreID = @genreID
    --END  
    --ELSE IF @sortEntity = 6 or @sortEntityName = 'vector' BEGIN  
    --    INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductVector a with (nolock) inner join (select distinct a.ProductID from ProductVector a with (nolock) 
    --    left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where VectorID = @vectorID
    --END  
    ELSE BEGIN  
        INSERT #displayorder select distinct [name], a.productid, 1 from dbo.Product a with (nolock) inner join (select distinct a.ProductID from Product a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID ORDER BY Name  
    END  

	IF (@ftsenabled = 1)
	BEGIN
		IF rtrim(isnull(@searchstr, '')) <> ''
		BEGIN
			DECLARE @tmpsrch nvarchar(4000)
			SET @tmpsrch = dbo.GetValidSearchString(@searchstr) 
			DELETE #displayorder from #displayorder d left join dbo.KeyWordSearch(@tmpsrch) k on d.productid = k.productid where k.productid is null  
		END
	END
	
	SET @searchstr = '%' + rtrim(ltrim(@searchstr)) + '%' 
 
    IF @InventoryFilter <> -1 BEGIN  
        IF @ViewType = 1 BEGIN  
            INSERT #inventoryfilter  
            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty  
            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID  
                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID  and pv.IsDefault = 1  
                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID  
            GROUP BY p.productid, pv.VariantID  
            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter  
        END  
        ELSE  
            INSERT #inventoryfilter  
            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty  
            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID  
                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID  
                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID  
            GROUP BY p.productid, pv.VariantID  
            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter  
  

        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)  
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name  
        FROM   
            product p with (nolock)  
            join #displayorder do on p.ProductID = do.ProductID   
            left join ProductVariant pv        with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType  
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID   
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID   
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID   
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID   
            --left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID   
            --left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID   
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID   
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID   
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID
			   
            join #inventoryfilter i on pv.VariantID = i.VariantID  
        WHERE   
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)  
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0)  
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0)  
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)  
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0)  
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0)  
          --and (px.GenreID = @genreID or @genreID is null or @genrecount = 0)  
          --and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0)  
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)  
          and (case   
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1  
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1   
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1   
                else 0  
               end  = 1  
              )  
          and (@ftsenabled = 1 or
				(@ftsenabled = 0 and
					(@searchstr is null  
					 or patindex(@searchstr, isnull(p.name, '')) > 0  
					 or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0   
					 or patindex(@searchstr, isnull(pv.name, '')) > 0  
					 or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0  
					 or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0  
					 or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0  
					 or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1)  
					 or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1)  
					)
				)
              )  
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly  
          and p.published >= @publishedonly  
          and pv.published >= @publishedonly  
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks  
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits  
          and p.IsSystem <= 1-@ExcludeSysProds  
          and p.Deleted = 0  
          and pv.Deleted = 0  
          and ((@IncludeAll = 1) or (getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')))    
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name  
  
    END   
    ELSE BEGIN  
        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)  
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name  
        FROM   
            product p with (nolock)  
            join #displayorder do on p.ProductID = do.ProductID   
            join ProductVariant pv             with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType  
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID   
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID   
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID   
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID   
            --left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID   
            --left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID   
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID   
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID   
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID   
        WHERE   
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)  
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0)  
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0)  
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)  
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0)  
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0)  
          --and (px.GenreID = @genreID or @genreID is null or @genrecount = 0)  
          --and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0)  
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)  
          and (case   
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1  
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1   
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1   
                else 0  
               end  = 1  
              )  
          and (@ftsenabled = 1 or
				(@ftsenabled = 0 and
					(@searchstr is null  
					or patindex(@searchstr, isnull(p.name, '')) > 0  
					or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0   
					or patindex(@searchstr, isnull(pv.name, '')) > 0  
					or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0  
					or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0  
					or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0  
					or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1)  
					or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1)  
					)
				)
              )  
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly  
          and p.published >= @publishedonly  
          and pv.published >= @publishedonly  
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks  
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits  
          and p.IsSystem <= 1-@ExcludeSysProds  
          and p.Deleted = 0  
          and pv.Deleted = 0  
          and ((@IncludeAll = 1) or (getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')))    
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name  
    END  
	
    SET @rcount = @@rowcount  
    IF @StatsFirst = 1  
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount  
  
  
  --Begin sorting
  	if @sortby = 'bestseller'
		begin
			insert @productfiltersort (productid, price, saleprice, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName) 
			select pf.productid, pf.price, pf.saleprice, pf.displayorder, pf.VariantID, pf.VariantDisplayOrder, pf.ProductName, pf.VariantName
				from @productfilter pf
				left join (
					select ProductID, SUM(Quantity) AS NumSales
					  from dbo.Orders_ShoppingCart sc with (NOLOCK) 
							join [dbo].Orders o with (NOLOCK)  on sc.OrderNumber = o.OrderNumber and o.OrderDate >= dateadd(dy, -@since, getdate())
					  group by ProductID 
				) bsSort on pf.productid = bsSort.ProductID
				order by isnull(bsSort.NumSales, 0) DESC
		end
  	else --default
		begin
			insert @productfiltersort (productid, price, saleprice, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName) 
			select productid, price, saleprice, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName
			from @productfilter order by displayorder, productName, variantDisplayOrder, variantName
		end
    
    SELECT   
        p.ProductID,  
        p.Name,  
        pv.VariantID,  
        pv.Name AS VariantName,  
        p.ProductGUID,  
        p.Summary,  
        p.Description,  
        p.SEKeywords,  
        p.SEDescription,  
        p.SpecTitle,  
        p.MiscText,  
        p.SwatchImageMap,  
        p.IsFeaturedTeaser,  
        p.FroogleDescription,  
        p.SETitle,  
        p.SENoScript,  
        p.SEAltText,  
        p.SizeOptionPrompt,  
        p.ColorOptionPrompt,  
        p.TextOptionPrompt,  
        p.ProductTypeID,  
        p.TaxClassID,  
        p.SKU,  
        p.ManufacturerPartNumber,  
        p.SalesPromptID,  
        p.SpecCall,  
        p.SpecsInline,  
        p.IsFeatured,  
        p.XmlPackage,  
        p.ColWidth,  
        p.Published,  
        p.RequiresRegistration,  
        p.Looks,  
        p.Notes,  
        p.QuantityDiscountID,  
        p.RelatedProducts,  
        p.UpsellProducts,  
        p.UpsellProductDiscountPercentage,  
        p.RelatedDocuments,  
        p.TrackInventoryBySizeAndColor,  
        p.TrackInventoryBySize,  
        p.TrackInventoryByColor,  
        p.IsAKit,  
        p.ShowInProductBrowser,  
        p.IsAPack,  
        p.PackSize,  
        p.ShowBuyButton,  
        p.RequiresProducts,  
        p.HidePriceUntilCart,  
        p.IsCalltoOrder,  
        p.ExcludeFromPriceFeeds,  
        p.RequiresTextOption,  
        p.TextOptionMaxLength,  
        p.SEName,  
        p.Deleted,  
        p.CreatedOn,  
        p.ImageFileNameOverride,  
        pv.VariantGUID,  
        pv.Description AS VariantDescription,  
        pv.SEKeywords AS VariantSEKeywords,  
        pv.SEDescription AS VariantSEDescription,  
        pv.Colors,  
        pv.ColorSKUModifiers,  
        pv.Sizes,  
        pv.SizeSKUModifiers,  
        pv.FroogleDescription AS VariantFroogleDescription,  
        pv.SKUSuffix,  
        pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,  
        pv.Price,  
        pv.CustomerEntersPrice,   
        pv.CustomerEntersPricePrompt,  
        isnull(pv.SalePrice, 0) SalePrice,  
        cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,  
        pv.MSRP,  
        pv.Cost,  
        isnull(pv.Points,0) Points,  
        pv.Dimensions,  
        case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,  
        pv.DisplayOrder as VariantDisplayOrder,  
        pv.Notes AS VariantNotes,  
        pv.IsTaxable,  
        pv.IsShipSeparately,  
        pv.IsDownload,  
        pv.DownloadLocation,  
        pv.Published AS VariantPublished,  
        pv.IsSecureAttachment,  
        pv.IsRecurring,  
        pv.RecurringInterval,  
        pv.RecurringIntervalType,  
        pv.SubscriptionInterval,  
        pv.SEName AS VariantSEName,  
        pv.RestrictedQuantities,  
        pv.MinimumQuantity,  
        pv.Deleted AS VariantDeleted,  
        pv.CreatedOn AS VariantCreatedOn,  
        d.Name AS DistributorName,  
        d.DistributorID,  
        d.SEName AS DistributorSEName,  
        m.ManufacturerID,  
        m.Name AS ManufacturerName,  
        m.SEName AS ManufacturerSEName,  
        s.Name AS SalesPromptName,  
        case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice  
    FROM dbo.Product p with (NOLOCK)   
        left join dbo.ProductVariant       pv  with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType  
        join @productfiltersort            pf                on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID   
        left join dbo.SalesPrompt           s  with (NOLOCK) on p.SalesPromptID = s.SalesPromptID   
        left join dbo.ProductManufacturer  pm  with (NOLOCK) on p.ProductID = pm.ProductID   
        left join dbo.Manufacturer          m  with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID   
        left join dbo.ProductDistributor   pd  with (NOLOCK) on p.ProductID = pd.ProductID  
        left join dbo.Distributor           d  with (NOLOCK) on pd.DistributorID = d.DistributorID  
        left join dbo.ExtendedPrice        ep  with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID  
        left join dbo.ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID  
        left join (select VariantID, sum(quan) quan from dbo.Inventory with (nolock) group by VariantID) i  on pv.VariantID = i.VariantID  
    WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)  
    ORDER BY pf.rownum  
  
    IF @StatsFirst <> 1  
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount  
  
END

Is this going to speed thing a bit? I am at point where even a smallest acceleration will help me.

(After 20 minutes)
This did not accelerate things not a single bit. Main problems that I have:
Store has good hardware and runs on dedicated server. Application itself is very fast, but DB connection is apparently a bottleneck.
1. Calling category, more products in category longer it lasts. Category with 6000 products it takes up to seven seconds to load.
2. Calling manufacturer (on manufacturer page) it takes from 3 seconds up to 10 seconds depending on product count.

What else can I do?

Originally Posted by vedran

Thanks for your reply.

I am using 9.3 StoreFront. I am using VibeTrib search with FTS enabled.


PS (on side note)
When I use CacheEntityPageHTML it give huge performance boost, however VibeTrib filtering does not work from category and manufacturer pages. It does work from global search. How can I enable it to work also from category and manufacturer pages?
Thanks

I don't use VibeTrib myself but given what I can see from the demo. To fix the caching issue with CacheEntityPageHTML is just a matter of editing the CacheName in App_Code\ShowEntityPage.cs to Something like this

C#/VB.NET Code:

CacheName = String.Format("CacheEntityPageHTML|{0}|{1}|{2}|{3}|{4}|{5}|{6}",
                                                      m_EntitySpecs.m_EntityName,
                                                      m_EntityInstanceID.ToString(),
                                                      m_SkinBase.ThisCustomer.CustomerLevelID.ToString(),
                                                      m_SkinBase.ThisCustomer.LocaleSetting,
                                                      CommonLogic.QueryStringUSInt("PageNum").ToString(),                                                      
                                                      m_SkinBase.ThisCustomer.AffiliateID.ToString(),
                                                      CommonLogic.QueryStringCanBeDangerousContent("Filter")
                                ); 


Do note that this code is based off the demo is version and it may be a different query string/s in version 2.

Originally Posted by vedran

Based on your help, this is what I did to that stored procedure, I have commented out genre and vector.



Is this going to speed thing a bit? I am at point where even a smallest acceleration will help me.

(After 20 minutes)
This did not accelerate things not a single bit. Main problems that I have:
Store has good hardware and runs on dedicated server. Application itself is very fast, but DB connection is apparently a bottleneck.
1. Calling category, more products in category longer it lasts. Category with 6000 products it takes up to seven seconds to load.
2. Calling manufacturer (on manufacturer page) it takes from 3 seconds up to 10 seconds depending on product count.

What else can I do?

Just query speed wise adding some index views to use for joins should help. Even if you just the did the Inventory/#inventoryfilter as a index view would help given the amount of products you have.

Thank you so much for your help in this matter, it means a lot.

This is the original code that I had:

Code:

CacheName = String.Format("CacheEntityPageHTML|{0}|{1}|{2}|{3}|{4}",
                                                      m_EntitySpecs.m_EntityName,
                                                      m_EntityInstanceID.ToString(),
                                                      m_SkinBase.ThisCustomer.CustomerLevelID.ToString(),
                                                      m_SkinBase.ThisCustomer.LocaleSetting,
                                                      CommonLogic.QueryStringUSInt("PageNum").ToString(),                                                      
                                                      m_SkinBase.ThisCustomer.AffiliateID.ToString(),

And I have changed that code exactly as you told me:

Code:

if (AppLogic.AppConfigBool("CacheEntityPageHTML"))
                        {
                           // CacheName = String.Format("CacheEntityPageHTML|{0}|{1}|{2}|{3}|{4}",
                            CacheName = String.Format("CacheEntityPageHTML|{0}|{1}|{2}|{3}|{4}|{5}|{6}",
                                                      m_EntitySpecs.m_EntityName,
                                                      m_EntityInstanceID.ToString(),
                                                      m_SkinBase.ThisCustomer.CustomerLevelID.ToString(),
                                                      m_SkinBase.ThisCustomer.LocaleSetting,
                                                      CommonLogic.QueryStringUSInt("PageNum").ToString(),
                                                      m_SkinBase.ThisCustomer.AffiliateID.ToString(),
                                                      CommonLogic.QueryStringCanBeDangerousContent("Filter") 
                                );

It is still not showing any effect. Well, once it did trigger the filter and it showed correctly. But that filter was cashed for other visitors as well and was not removable.

I did reset cache from admin panel and it went away. But after the reset cache, I cant trigger the filter anymore for some reason.

Thanks

going to keep my reply to this thread just to make it easier. Not sure why the change to the cache name isn't working but like I said it is possible that version two has different parameters. Thought it worth suggesting at the very least.

As for the creation the index view. There are two steps you will need to do one create the indexed view and second is to use the view in aspdnsf_GetProducts. For creation of the view try

Code:

IF OBJECT_ID('Inventory_View','V') IS NOT NULL
DROP VIEW [Inventory_View]
GO

CREATE VIEW dbo.[Inventory_View] WITH SCHEMABINDING AS

SELECT pv.productid, pv.VariantID, sum(  isnull(i.quan, 0) ) invqty, COUNT_BIG(*) AS frequency  
                FROM dbo.ProductVariant pv 
                join dbo.Inventory i on pv.VariantID = i.VariantID  
            GROUP BY pv.productid, pv.VariantID
GO

CREATE UNIQUE CLUSTERED INDEX IDX_Inventory_View_VariantID ON [Inventory_View] (VariantID);
GO

As for using the view in GetProducts try:

Code:

SET QUOTED_IDENTIFIER ON
GO


ALTER proc [dbo].[aspdnsf_GetProducts]
    @categoryID      int = null,  
    @sectionID       int = null,  
    @manufacturerID  int = null,  
    @distributorID   int = null,  
    --@genreID         int = null,  
    --@vectorID        int = null,  
    @localeID        int = null,  
    @CustomerLevelID int = null,  
    @affiliateID     int = null,  
    @ProductTypeID   int = null,  
    @ViewType        bit = 1, -- 0 = all variants, 1 = one variant  
    @sortEntity      int = 0, -- 1 = category, 2 = section, 3 = manufacturer, 4 = distributor, 5= genre, 6 = vector  
    @pagenum         int = 1,  
    @pagesize        int = null,  
    @StatsFirst      tinyint = 1,  
    @searchstr       nvarchar(4000) = null,  
    @extSearch       tinyint = 0,  
    @publishedonly   tinyint = 0,  
    @ExcludePacks    tinyint = 0,  
    @ExcludeKits     tinyint = 0,  
    @ExcludeSysProds tinyint = 0,  
    @InventoryFilter int = 0,  --  will only show products with an inventory level GREATER OR EQUAL TO than the number specified in this parameter, set to -1 to disable inventory filtering  
    @sortEntityName  varchar(20) = '', -- usely only when the entity id is provided, allowed values: category, section, manufacturer, distributor, genre, vector  
    @localeName      varchar(20) = '',  
    @OnSaleOnly      tinyint = 0,  
    @IncludeAll      bit = 0, -- Don't filter products that have a start date in the future or a stop date in the past  
	@storeID		 int = 1,
	@filterProduct	 bit = 0,
	@sortby			 varchar(10) = 'default',
	@since			 int = 180  -- best sellers in the last "@since" number of days
	
  
AS  
BEGIN  
  
    SET NOCOUNT ON   
  
    DECLARE @rcount int
    DECLARE @productfiltersort table (rownum int not null identity  primary key, productid int not null, price money null, saleprice money null, displayorder int not null, VariantID int not null, VariantDisplayOrder int not null, ProductName nvarchar(400) null, VariantName nvarchar(400) null)
    DECLARE @productfilter table (rownum int not null identity  primary key, productid int not null, price money null, saleprice money null,  displayorder int not null, VariantID int not null, VariantDisplayOrder int not null, ProductName nvarchar(400) null, VariantName nvarchar(400) null)
	DECLARE @FilterProductsByAffiliate tinyint, @FilterProductsByCustomerLevel tinyint, @HideProductsWithLessThanThisInventoryLevel int  
    CREATE TABLE #displayorder ([name] nvarchar (800), productid int not null primary key, displayorder int not null)  
--    CREATE TABLE #inventoryfilter (productid int not null, variantid int not null, InvQty int not null)  
--    CREATE CLUSTERED INDEX tmp_inventoryfilter ON #inventoryfilter (productid, variantid)  
  
    DECLARE @custlevelcount int, @sectioncount int, @localecount int, @affiliatecount int, @categorycount int, @CustomerLevelFilteringIsAscending bit, @distributorcount int, @manufacturercount int  
  
	DECLARE @ftsenabled tinyint
	
	SET @ftsenabled = 0
	
	IF ((SELECT DATABASEPROPERTY(db_name(db_id()),'IsFulltextEnabled')) = 1 
		AND EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[KeyWordSearch]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
		AND EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetValidSearchString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')))
	BEGIN
		SET @ftsenabled = 1
	END
  
    SET @FilterProductsByAffiliate = (SELECT TOP 1 case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByAffiliate' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc)
    SET @FilterProductsByCustomerLevel = (SELECT TOP 1 case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByCustomerLevel' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc)
    SET @HideProductsWithLessThanThisInventoryLevel = (SELECT TOP 1 case ConfigValue when -1 then 0 else ConfigValue end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'HideProductsWithLessThanThisInventoryLevel' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc)
    
    IF @InventoryFilter <> -1 and (@HideProductsWithLessThanThisInventoryLevel > @InventoryFilter or @HideProductsWithLessThanThisInventoryLevel  = -1)  
        SET @InventoryFilter  = @HideProductsWithLessThanThisInventoryLevel  
  
    SET @categoryID      = nullif(@categoryID, 0)  
    SET @sectionID       = nullif(@sectionID, 0)  
    SET @manufacturerID  = nullif(@manufacturerID, 0)  
    SET @distributorID   = nullif(@distributorID, 0)  
    --SET @genreID         = nullif(@genreID, 0)  
    --SET @vectorID        = nullif(@vectorID, 0)  
    SET @affiliateID     = nullif(@affiliateID, 0)  
    SET @ProductTypeID   = nullif(@ProductTypeID, 0)  
  
  
    SET @CustomerLevelFilteringIsAscending  = 0  
    SET @CustomerLevelFilteringIsAscending = (SELECT TOP 1 case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterByCustomerLevelIsAscending' AND (StoreID=@storeID OR StoreID=0) ORDER BY StoreID desc) 
  
    IF @localeID is null and ltrim(rtrim(@localeName)) <> ''  
        SELECT @localeID = LocaleSettingID FROM dbo.LocaleSetting with (nolock) WHERE Name = ltrim(rtrim(@localeName))  
  
    select @categorycount     = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productcategory') and si.indid < 2 and type = 'u'  
    select @sectioncount      = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productsection') and si.indid < 2 and type = 'u'  
    select @localecount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductLocaleSetting') and si.indid < 2 and type = 'u'  
    select @custlevelcount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductCustomerLevel') and si.indid < 2 and type = 'u'  
    select @affiliatecount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductAffiliate') and si.indid < 2 and type = 'u'  
    select @distributorcount  = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductDistributor') and si.indid < 2 and type = 'u'  
    --select @genrecount        = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductGenre') and si.indid < 2 and type = 'u'  
    --select @vectorcount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductVector') and si.indid < 2 and type = 'u'  
    select @manufacturercount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductManufacturer') and si.indid < 2 and type = 'u'  
  
  
    -- get page size  
    IF @pagesize is null or @pagesize = 0 BEGIN  
        IF @categoryID is not null  
            SELECT @pagesize = PageSize FROM dbo.Category with (nolock) WHERE categoryID = @categoryID  
        ELSE IF @sectionID is not null  
            SELECT @pagesize = PageSize FROM dbo.Section with (nolock) WHERE sectionID = @sectionID  
        ELSE IF @manufacturerID is not null  
            SELECT @pagesize = PageSize FROM dbo.Manufacturer with (nolock) WHERE manufacturerID = @manufacturerID  
        ELSE IF @distributorID is not null  
            SELECT @pagesize = PageSize FROM dbo.Distributor with (nolock) WHERE distributorID = @distributorID  
        --ELSE IF @genreID is not null  
        --    SELECT @pagesize = PageSize FROM dbo.Genre with (nolock) WHERE genreID = @genreID  
        --ELSE IF @vectorID is not null  
        --    SELECT @pagesize = PageSize FROM dbo.Vector with (nolock) WHERE vectorID = @vectorID  
        ELSE   
            SELECT @pagesize = convert(int, ConfigValue) FROM dbo.AppConfig with (nolock) WHERE [Name] = 'Default_CategoryPageSize'  
    END  
  
    IF @pagesize is null or @pagesize = 0  
        SET @pagesize = 20  
  
    -- get sort order  
    IF @sortEntity = 1 or @sortEntityName = 'category' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductCategory a with (nolock) inner join (select distinct a.ProductID from ProductCategory a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b  on a.ProductID = b.ProductID where categoryID = @categoryID 
    END  
    ELSE IF @sortEntity = 2 or @sortEntityName = 'section' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductSection a with (nolock) inner join (select distinct a.ProductID from ProductSection a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where sectionId = @sectionID
    END  
    ELSE IF @sortEntity = 3 or @sortEntityName = 'manufacturer' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductManufacturer a with (nolock) inner join (select distinct a.ProductID from ProductManufacturer a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where ManufacturerID = @manufacturerID
    END  
    ELSE IF @sortEntity = 4 or @sortEntityName = 'distributor' BEGIN  
        INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductDistributor a with (nolock) inner join (select distinct a.ProductID from ProductDistributor a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where DistributorID = @distributorID
    END  
    --ELSE IF @sortEntity = 5 or @sortEntityName = 'genre' BEGIN  
    --    INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductGenre a with (nolock) inner join (select distinct a.ProductID from ProductGenre a with (nolock) 
    --    left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where GenreID = @genreID
    --END  
    --ELSE IF @sortEntity = 6 or @sortEntityName = 'vector' BEGIN  
    --    INSERT #displayorder select distinct null as [name], a.productid, displayorder from dbo.ProductVector a with (nolock) inner join (select distinct a.ProductID from ProductVector a with (nolock) 
    --    left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID where VectorID = @vectorID
    --END  
    ELSE BEGIN  
        INSERT #displayorder select distinct [name], a.productid, 1 from dbo.Product a with (nolock) inner join (select distinct a.ProductID from Product a with (nolock) 
        left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) b on a.ProductID = B.ProductID ORDER BY Name  
    END  

	IF (@ftsenabled = 1)
	BEGIN
		IF rtrim(isnull(@searchstr, '')) <> ''
		BEGIN
			DECLARE @tmpsrch nvarchar(4000)
			SET @tmpsrch = dbo.GetValidSearchString(@searchstr) 
			DELETE #displayorder from #displayorder d left join dbo.KeyWordSearch(@tmpsrch) k on d.productid = k.productid where k.productid is null  
		END
	END
	
	SET @searchstr = '%' + rtrim(ltrim(@searchstr)) + '%' 
 
    IF @InventoryFilter <> -1 BEGIN  
--		IF @ViewType = 1 BEGIN  
--            INSERT #inventoryfilter  
--            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty  
--            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID  
--                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID  and pv.IsDefault = 1  
--                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID  
--            GROUP BY p.productid, pv.VariantID  
--            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter  
--        END  
--        ELSE  
--            INSERT #inventoryfilter  
--            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty  
--            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID  
--                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID  
--                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID  
--            GROUP BY p.productid, pv.VariantID  
--            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter  

        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)  
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name  
        FROM   
            product p with (nolock)  
            join #displayorder do on p.ProductID = do.ProductID   
            left join ProductVariant pv        with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType  
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID   
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID   
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID   
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID   
            --left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID   
            --left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID   
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID   
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID   
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID
			   
--            join #inventoryfilter i on pv.VariantID = i.VariantID 
			left join Inventory_View i with (NOLOCK) on pv.VariantID = i.VariantID 
			
        WHERE   
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)  
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0)  
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0)  
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)  
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0)  
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0)  
          --and (px.GenreID = @genreID or @genreID is null or @genrecount = 0)  
          --and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0)  
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)  
		  and ((p.TrackInventoryBySizeAndColor= 1 and i.invqty >= @InventoryFilter ) or (p.TrackInventoryBySizeAndColor= 0 and pv.inventory >= @InventoryFilter )  )
          and (case   
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1  
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1   
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1   
                else 0  
               end  = 1  
              )  
          and (@ftsenabled = 1 or
				(@ftsenabled = 0 and
					(@searchstr is null  
					 or patindex(@searchstr, isnull(p.name, '')) > 0  
					 or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0   
					 or patindex(@searchstr, isnull(pv.name, '')) > 0  
					 or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0  
					 or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0  
					 or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0  
					 or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1)  
					 or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1)  
					)
				)
              )  
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly  
          and p.published >= @publishedonly  
          and pv.published >= @publishedonly  
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks  
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits  
          and p.IsSystem <= 1-@ExcludeSysProds  
          and p.Deleted = 0  
          and pv.Deleted = 0  
          and ((@IncludeAll = 1) or (getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')))    
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name  
  
    END   
    ELSE BEGIN  
        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)  
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name  
        FROM   
            product p with (nolock)  
            join #displayorder do on p.ProductID = do.ProductID   
            join ProductVariant pv             with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType  
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID   
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID   
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID   
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID   
            --left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID   
            --left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID   
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID   
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID   
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID   
        WHERE   
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)  
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0)  
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0)  
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)  
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0)  
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0)  
          --and (px.GenreID = @genreID or @genreID is null or @genrecount = 0)  
          --and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0)  
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)  
          and (case   
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1  
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1   
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1   
                else 0  
               end  = 1  
              )  
          and (@ftsenabled = 1 or
				(@ftsenabled = 0 and
					(@searchstr is null  
					or patindex(@searchstr, isnull(p.name, '')) > 0  
					or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0   
					or patindex(@searchstr, isnull(pv.name, '')) > 0  
					or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0  
					or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0  
					or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0  
					or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1)  
					or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1)  
					)
				)
              )  
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly  
          and p.published >= @publishedonly  
          and pv.published >= @publishedonly  
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks  
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits  
          and p.IsSystem <= 1-@ExcludeSysProds  
          and p.Deleted = 0  
          and pv.Deleted = 0  
          and ((@IncludeAll = 1) or (getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')))    
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name  
    END  
	
    SET @rcount = @@rowcount  
    IF @StatsFirst = 1  
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount  
  
  
  --Begin sorting
  	if @sortby = 'bestseller'
		begin
			insert @productfiltersort (productid, price, saleprice, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName) 
			select pf.productid, pf.price, pf.saleprice, pf.displayorder, pf.VariantID, pf.VariantDisplayOrder, pf.ProductName, pf.VariantName
				from @productfilter pf
				left join (
					select ProductID, SUM(Quantity) AS NumSales
					  from dbo.Orders_ShoppingCart sc with (NOLOCK) 
							join [dbo].Orders o with (NOLOCK)  on sc.OrderNumber = o.OrderNumber and o.OrderDate >= dateadd(dy, -@since, getdate())
					  group by ProductID 
				) bsSort on pf.productid = bsSort.ProductID
				order by isnull(bsSort.NumSales, 0) DESC
		end
  	else --default
		begin
			insert @productfiltersort (productid, price, saleprice, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName) 
			select productid, price, saleprice, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName
			from @productfilter order by displayorder, productName, variantDisplayOrder, variantName
		end
    
    SELECT   
        p.ProductID,  
        p.Name,  
        pv.VariantID,  
        pv.Name AS VariantName,  
        p.ProductGUID,  
        p.Summary,  
        p.Description,  
        p.SEKeywords,  
        p.SEDescription,  
        p.SpecTitle,  
        p.MiscText,  
        p.SwatchImageMap,  
        p.IsFeaturedTeaser,  
        p.FroogleDescription,  
        p.SETitle,  
        p.SENoScript,  
        p.SEAltText,  
        p.SizeOptionPrompt,  
        p.ColorOptionPrompt,  
        p.TextOptionPrompt,  
        p.ProductTypeID,  
        p.TaxClassID,  
        p.SKU,  
        p.ManufacturerPartNumber,  
        p.SalesPromptID,  
        p.SpecCall,  
        p.SpecsInline,  
        p.IsFeatured,  
        p.XmlPackage,  
        p.ColWidth,  
        p.Published,  
        p.RequiresRegistration,  
        p.Looks,  
        p.Notes,  
        p.QuantityDiscountID,  
        p.RelatedProducts,  
        p.UpsellProducts,  
        p.UpsellProductDiscountPercentage,  
        p.RelatedDocuments,  
        p.TrackInventoryBySizeAndColor,  
        p.TrackInventoryBySize,  
        p.TrackInventoryByColor,  
        p.IsAKit,  
        p.ShowInProductBrowser,  
        p.IsAPack,  
        p.PackSize,  
        p.ShowBuyButton,  
        p.RequiresProducts,  
        p.HidePriceUntilCart,  
        p.IsCalltoOrder,  
        p.ExcludeFromPriceFeeds,  
        p.RequiresTextOption,  
        p.TextOptionMaxLength,  
        p.SEName,  
        p.Deleted,  
        p.CreatedOn,  
        p.ImageFileNameOverride,  
        pv.VariantGUID,  
        pv.Description AS VariantDescription,  
        pv.SEKeywords AS VariantSEKeywords,  
        pv.SEDescription AS VariantSEDescription,  
        pv.Colors,  
        pv.ColorSKUModifiers,  
        pv.Sizes,  
        pv.SizeSKUModifiers,  
        pv.FroogleDescription AS VariantFroogleDescription,  
        pv.SKUSuffix,  
        pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,  
        pv.Price,  
        pv.CustomerEntersPrice,   
        pv.CustomerEntersPricePrompt,  
        isnull(pv.SalePrice, 0) SalePrice,  
        cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,  
        pv.MSRP,  
        pv.Cost,  
        isnull(pv.Points,0) Points,  
        pv.Dimensions,  
        case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,  
        pv.DisplayOrder as VariantDisplayOrder,  
        pv.Notes AS VariantNotes,  
        pv.IsTaxable,  
        pv.IsShipSeparately,  
        pv.IsDownload,  
        pv.DownloadLocation,  
        pv.Published AS VariantPublished,  
        pv.IsSecureAttachment,  
        pv.IsRecurring,  
        pv.RecurringInterval,  
        pv.RecurringIntervalType,  
        pv.SubscriptionInterval,  
        pv.SEName AS VariantSEName,  
        pv.RestrictedQuantities,  
        pv.MinimumQuantity,  
        pv.Deleted AS VariantDeleted,  
        pv.CreatedOn AS VariantCreatedOn,  
        d.Name AS DistributorName,  
        d.DistributorID,  
        d.SEName AS DistributorSEName,  
        m.ManufacturerID,  
        m.Name AS ManufacturerName,  
        m.SEName AS ManufacturerSEName,  
        s.Name AS SalesPromptName,  
        case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice  
    FROM dbo.Product p with (NOLOCK)   
        left join dbo.ProductVariant       pv  with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType  
        join @productfiltersort            pf                on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID   
        left join dbo.SalesPrompt           s  with (NOLOCK) on p.SalesPromptID = s.SalesPromptID   
        left join dbo.ProductManufacturer  pm  with (NOLOCK) on p.ProductID = pm.ProductID   
        left join dbo.Manufacturer          m  with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID   
        left join dbo.ProductDistributor   pd  with (NOLOCK) on p.ProductID = pd.ProductID  
        left join dbo.Distributor           d  with (NOLOCK) on pd.DistributorID = d.DistributorID  
        left join dbo.ExtendedPrice        ep  with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID  
        left join dbo.ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID  
        left join (select VariantID, sum(quan) quan from dbo.Inventory with (nolock) group by VariantID) i  on pv.VariantID = i.VariantID  
    WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)  
    ORDER BY pf.rownum  
  
    IF @StatsFirst <> 1  
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount  
  
END

Try that and see where the results are. If more tweaks are needed after finding 3rd party dev to work directly with you and your database is highly suggested. Reason being is the more indexes created the longer updates to table/s can take.

Thank you very much for your time!!!

I will start playing with it immediately.