Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 8 of 8

Thread: Fine tuning DB

  1. #1
    vedran is offline Member
    Join Date
    Jun 2012
    Posts
    98

    Default 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

  2. #2
    mmcgeachy is offline Senior Member
    Join Date
    Sep 2008
    Posts
    174

    Default

    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?
    Last edited by mmcgeachy; 11-09-2012 at 03:03 PM.

  3. #3
    vedran is offline Member
    Join Date
    Jun 2012
    Posts
    98

    Default

    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
    Last edited by vedran; 11-10-2012 at 03:38 AM.

  4. #4
    vedran is offline Member
    Join Date
    Jun 2012
    Posts
    98

    Default

    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?
    Last edited by vedran; 11-10-2012 at 08:11 AM.

  5. #5
    mmcgeachy is offline Senior Member
    Join Date
    Sep 2008
    Posts
    174

    Default

    Quote Originally Posted by vedran View Post
    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.

    Quote Originally Posted by vedran View Post
    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.
    Last edited by mmcgeachy; 11-12-2012 at 09:21 AM.

  6. #6
    vedran is offline Member
    Join Date
    Jun 2012
    Posts
    98

    Default

    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

  7. #7
    mmcgeachy is offline Senior Member
    Join Date
    Sep 2008
    Posts
    174

    Default

    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.

  8. #8
    vedran is offline Member
    Join Date
    Jun 2012
    Posts
    98

    Default

    Thank you very much for your time!!!

    I will start playing with it immediately.