Slow Page Loads

Andy

Bg Payne has 12,000 products and 30,000 variants. If you have a lot less then this it doesn't matter.

Webwiz are good, but a little expensive, as a long time One and One customer I'm evaluatiing the 1 & 1 Cloud server which isn't really a cloud, its Hyper V like Webwiz at half the price. Difference is, Webwiz have fantastic support, one and one are helpful but only really answer simple questions, they don't help you do anything.

Hope this helps you.

Regards

Phil

Before looking at your providor. Does it run fast on a developer machine?

Indexes I found important to the performance side of things when reading product related information:

CREATE NONCLUSTERED INDEX [IX_Nop_ProductVariant_ProductID] ON [dbo].[Nop_ProductVariant] ([ProductID])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_ProductVariant_Name] ON [dbo].[Nop_ProductVariant] ([Name])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_ProductVariant_SKU] ON [dbo].[Nop_ProductVariant] ([SKU])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_ProductVariant_DisplayOrder] ON [dbo].[Nop_ProductVariant] ([DisplayOrder])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_Product_Category_Mapping_Category] ON [dbo].[Nop_Product_Category_Mapping] ([CategoryID])
GO

CREATE NONCLUSTERED INDEX [_dta_index_Nop_Product_Category_Mapping_8_1682105033__K2] ON [dbo].[Nop_Product_Category_Mapping] ([ProductID])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_Product_Manufacturer_Mapping_ManufacturerID] ON [dbo].[Nop_Product_Manufacturer_Mapping] ([ManufacturerID])
GO

CREATE NONCLUSTERED INDEX [_dta_index_Nop_Product_Manufacturer_Mapping_8_1938105945__K2] ON [dbo].[Nop_Product_Manufacturer_Mapping] ([ProductID])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_ProductPicture_PictureID] ON [dbo].[Nop_ProductPicture] ([PictureID])
GO

CREATE NONCLUSTERED INDEX [_dta_index_Nop_ProductPicture_8_1906105831__K2_K3_K4] ON [dbo].[Nop_ProductPicture] ([ProductID], [PictureID], [DisplayOrder])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_ProductTag_Product_Mapping_ProductID] ON [dbo].[Nop_ProductTag_Product_Mapping] ([ProductID])
GO

CREATE NONCLUSTERED INDEX [IX_Nop_RelatedProduct_ProductID2] ON [dbo].[Nop_RelatedProduct] ([ProductID2])
GO


Basically anything that has a constraint on it also needs an index if more than a few dozen records are affected.

I was going into SQL / Server 2008 and doing the following:

1. Find the longest running queries using some SQL similar to:

-- DBCC FREEPROCCACHE (this is commented out as I only want to run it occassionally - it clears the cache out for the queries)
SELECT DISTINCT TOP 100
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE t.text Not Like '%CREATE%'
ORDER BY
s.execution_count DESC
GO

2. Copy and paste what I find into a new query window

3. At the top of the window have

SET STATISTICS XML ON

4. Look at the XML Showplan (below the results) and click on the link it gives.

5. Right click and choose "Show Execution Plan XML"

6. If the XML contains the word Missing (use Control F to find), it may be relating to a missing index.

OR

7. You could paste the SQL into the SQL/Server database tuning advisor.

I do this out of interest, but I don't always follow the exact recommendations from the SQL database tuning advisor, because it suggests having copies of the selected data in the index for easy reference. I am a bit old school and keep the indexes and the data seperate, which is slower, but uses a lot less space.

Without these indexes it was absolutely killing the server just to load one web page. Although the missing indexes for me are 30% of the problem, the other 70% is that LoadAllPaged stored procedure, which I have replaced in the instances that matter - please see earlier posts for details on these changes.

I do intend to do more work to improve in the future but for now I have a happy client and a happy webserver, so what more can I ask?

Hope this helps somebody.

Regards


Philip Johnson

Its far faster on my development system because I have a good setup.. well better than the webservers anyway.

I have a Gigabit LAN with Windows 2008 64 bit Foundation R2 with SQL/Server 2008 R2 (developer edition) with RAID 10 (4 disks) and 4GB of RAM doing SQL and File serving.

Development machine running VS2010 is Windows 7 64 bit Ultimate N with RAID 10 (4 disks) and 8GB of RAM. Both have fast QUAD Core Intel CPU's. So yes, I don't notice problems with this environment, our test system is a little bit less well spec'd and it was running slow in that environment, but still faster than the webserver.

To be honest my view is that it shouldn'bt be using 30% CPU on something with 2 CPU's for between 5 and 20 seconds or more on a SINGLE page load to load a single product on the webserver (i.e. when clicking on a product in one of the category views).

The stored proc noted earlier in this thread is most definitely the culprit, although putting the indexes on definitely helped. Now its 5% or less for 1/2 second or less now I've replaced it, although the indexes have worked as well as related. Before I made all these changes I was getting random response times, but I was putting this down to the caching and possibly the high amount of system memory that was being consumed along with the processor power. Maybe it was fragmenting the memory because reboots always seemed to improve it for a couple of hours....

I havn't yet made any config changes, just app changes and its fine now. I've been into RDB's since 1985 or so, and I'm still amazed what affect a single index can have if you have a lot of data. Definitely the first place to look and get right...

sometime it's often cluttered that pages are taking long time to load, but there may be several reson behind it, such as huge traffic to the site, or the server is too busy and something like that. So' it's obvious that pages are taking 15-17 seconds for loading, you may not have to worry about it. Hope nopcommerce may soon resolve this issue.



___________________________

1shoppingcart review

No my server was doing absolutely nothing else - only that website on it - and the rest of the pages in the site were fast. I have solved the 17s page loads turning them into less than 1s by the things I have done in this thread. I hope nopCommerce will use some of the ideas I have put into this thread to improve the product, but for me, its so difficult to upgrade to a new version because of the these and other changes I have made, I will be staying with my version for a while yet.

The problem with the stored proc is it was doing a lot of processing / database reads / and also using a lot of memory resulting in the server having to be rebooted every few hours. I've never seen a sproc take a server down like that before, but theres no problem now, I only reboot once a month or less than that.

Can anyone test Nop commerce 1.9 performance replacing Stored procedur Nop_ProductLoadAllPaged  with this script?
It should work only on SqlServer 2005 or 2008

An give me a feedback?  I'm Woking with it now please tell me if you notice this version is missing something comapred to the original Stored Procedure.

Thanks :

USE [NopCommerce_1_9_0]
GO
/****** Object:  StoredProcedure [dbo].[Nop_ProductLoadAllPaged]    Script Date: 12/30/2010 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Nop_ProductLoadAllPaged]
(
  @CategoryID      int = 0,
  @ManufacturerID    int = 0,
  @ProductTagID    int = 0,
  @FeaturedProducts  bit = null,  --0 featured only , 1 not featured only, null - load all products
  @PriceMin      money = null,
  @PriceMax      money = null,
  @RelatedToProductID  int = 0,
  @Keywords      nvarchar(MAX),
  @SearchDescriptions bit = 0,
  @ShowHidden      bit = 0,
  @PageIndex      int = 0,
  @PageSize      int = 2147483644,
  @FilteredSpecs    nvarchar(300) = null,  --filter by attributes (comma-separated list). e.g. 14,15,16
  @LanguageID      int = 0,
  @OrderBy      int = 0, --0 position, 5 - Name, 10 - Price, 15 - creation date
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  
  
  declare @ParmDefinition nvarchar(500)
  declare @tmpval int
  declare @sql nvarchar(4000)
  declare @sqlrowcount nvarchar(4000)
  declare @orderbysql nvarchar(500)
  declare @SearchKeywords bit
  
  --init
  SET @SearchKeywords = 1
  IF (@Keywords IS NULL OR @Keywords = N'')
    SET @SearchKeywords = 0

  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'

  SET @PriceMin = isnull(@PriceMin, 0)
  SET @PriceMax = isnull(@PriceMax, 2147483644)
  
  --filter by attributes
  SET @FilteredSpecs = isnull(@FilteredSpecs, '')
  CREATE TABLE #FilteredSpecs
  (
    SpecificationAttributeOptionID int not null
  )
  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionID)
  SELECT CAST(data as int) FROM dbo.[NOP_splitstring_to_table](@FilteredSpecs, ',');
  
  DECLARE @SpecAttributesCount int  
  SELECT @SpecAttributesCount = COUNT(1) FROM #FilteredSpecs

  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int
  
  SET @RowsToReturn = @PageSize * (@PageIndex + 1)  
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize;
  

  
  



------------- CREATE Temporary Table structure to maintain it also after "sp_executeSQL" ---------------------
SELECT 0 as tot,
  0 as ROWNUMBER,
    p.ProductId  
    into #tmpPage
  FROM Nop_Product p with (NOLOCK)
  LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = @LanguageID
  WHERE 0=1




set @orderbysql = 'ORDER BY'
--raiserror(@orderbysql,16,1)

set @orderbysql =
CASE  when @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
    THEN @orderbysql + ' pcm.DisplayOrder ASC,' else @orderbysql end




select @orderbysql =     
CASE WHEN @OrderBy = 0 AND @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
    THEN @orderbysql + ' pmm.DisplayOrder ASC,' else @orderbysql END



select @orderbysql =     
CASE WHEN @OrderBy = 0 AND @RelatedToProductID IS NOT NULL AND @RelatedToProductID > 0
    THEN @orderbysql + ' rp.DisplayOrder asc,' else @orderbysql END



select @orderbysql =     
CASE WHEN @OrderBy = 0
    THEN @orderbysql + ' isnull(p.name,pl.name) asc,'
else @orderbysql end

--raiserror(@orderbysql,15,1)


select @orderbysql =     
CASE WHEN @OrderBy = 5
    THEN @orderbysql +  ' isnull(p.name,pl.name) asc,' else @orderbysql END
--raiserror(@orderbysql,16,1)


select @orderbysql =     
CASE WHEN @OrderBy = 10
    THEN @orderbysql + ' pv.Price asc,' else @orderbysql END
--raiserror(@orderbysql,16,1)

select @orderbysql =     
CASE WHEN @OrderBy = 15
    THEN @orderbysql + ' p.CreatedOn desc,' else @orderbysql END
--raiserror(@orderbysql,16,1)


--remove ending Comma    
select @orderbysql = LEFT(@orderbysql,LEN(@orderbysql)-1)
    
---------------- Create query that retrieve Products and populate Temporary table with results --------
set @sql =
N'INSERT INTO #tmpPage SELECT res.*  FROM Nop_Product nopp inner join
  (SELECT DISTINCT COUNT(*) OVER() as tot,
  ROW_NUMBER() OVER
    (' + @orderbysql +    
    ') as ROWNUMBER,
    p.ProductId    
      FROM Nop_Product p with (NOLOCK)
  LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
  LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
  LEFT OUTER JOIN Nop_ProductTag_Product_Mapping ptpm with (NOLOCK) ON p.ProductID=ptpm.ProductID
  LEFT OUTER JOIN Nop_RelatedProduct rp with (NOLOCK) ON p.ProductID=rp.ProductID2
  LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
  LEFT OUTER JOIN Nop_ProductVariantLocalized pvl with (NOLOCK) ON pv.ProductVariantID = pvl.ProductVariantID AND pvl.LanguageID = ' + convert(nvarchar,@LanguageID) + '
  LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = ' + convert(nvarchar,@LanguageID) + '
  WHERE
    (
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR p.Published = 1
      )
    AND
      (
        p.Deleted=0
      )
    AND
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR pv.Published = 1
      )
    AND
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR pv.Deleted = 0
      )
    AND (
        ' + convert(nvarchar,@CategoryID) + 'IS NULL OR '  +convert(nvarchar,@CategoryID) + '=0
        OR (pcm.CategoryID='  +convert(nvarchar,@CategoryID) + ' AND ('  +convert(nvarchar,isnull(@FeaturedProducts,0)) + ' IS NULL OR pcm.IsFeaturedProduct='  +convert(nvarchar,isnull(@FeaturedProducts,0)) + '))
      )
    AND (
        '  +convert(nvarchar,@ManufacturerID) + ' IS NULL OR '  +convert(nvarchar,@ManufacturerID) + ' =0
        OR (pmm.ManufacturerID='  +convert(nvarchar,@ManufacturerID) + ' AND ('  +convert(nvarchar,isnull(@FeaturedProducts,0)) + ' IS NULL OR pmm.IsFeaturedProduct='  +convert(nvarchar,isnull(@FeaturedProducts,0)) + '))
      )
    AND (
        '  +convert(nvarchar,@ProductTagID) + ' IS NULL OR '  +convert(nvarchar,@ProductTagID) + '=0
        OR ptpm.ProductTagID='  +convert(nvarchar,@ProductTagID) + '
      )
    AND (
        '  +convert(nvarchar,@RelatedToProductID) + ' IS NULL OR '  +convert(nvarchar,@RelatedToProductID) + '=0
        OR rp.ProductID1='  +convert(nvarchar,@RelatedToProductID) + '
      )
    AND (
        '  +convert(nvarchar,@PriceMin) + ' IS NULL OR ' + convert(nvarchar,@PriceMin) + '=0
        OR pv.Price >  '  +convert(nvarchar,@PriceMin) + '   
      )
    AND (
        '  +convert(nvarchar,@PriceMax) + ' IS NULL OR ' + convert(nvarchar,@PriceMax) + '=2147483644 -- max value
        OR pv.Price < '  +convert(nvarchar,@PriceMax) + '
      )
     AND  (
        -- search standard content
        '  +convert(nvarchar,@SearchKeywords) + ' = 0 or
        (
         patindex('''  +convert(nvarchar,@Keywords) + ''', p.name) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pv.name) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pv.sku) > 0
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', p.ShortDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', p.FullDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pv.Description) > 0)          
        -- search language content
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pl.name) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pvl.name) > 0
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pl.ShortDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pl.FullDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pvl.Description) > 0)
        )
       )
    AND
      (
        '  +convert(nvarchar,@ShowHidden) + ' = 1
        OR
        (getutcdate() between isnull(pv.AvailableStartDateTime, ''1/1/1900'') and isnull(pv.AvailableEndDateTime, ''1/1/2999''))
      )
    AND
      (
        --filter by specs
        '  +convert(nvarchar,@SpecAttributesCount) + ' = 0
        OR
        (
          NOT EXISTS(
            SELECT 1
            FROM #FilteredSpecs [fs]
            WHERE [fs].SpecificationAttributeOptionID NOT IN (
              SELECT psam.SpecificationAttributeOptionID
              FROM dbo.Nop_Product_SpecificationAttribute_Mapping psam
              WHERE psam.AllowFiltering = 1 AND psam.ProductID = p.ProductID
              )
            )
          
        )
      )
    )
  ) res on res.ProductId = nopp.ProductId
  and res.ROWNUMBER >= '  +convert(nvarchar,@PageLowerBound) + '
  AND ROWNUMBER <= '  +convert(nvarchar,@PageUpperBound) + ''
  
  
  print(@sql)
  

  --Populate Temporary Table with Products search result
  execute sp_executesql @sql;
  
  
  --Assign Value to @TotalRecords, stored in column "tot" of every record of the temporary table #tmpPage
  SET @ParmDefinition = N'@totrecords int OUTPUT'
  SET @sql = 'SELECT @totrecords = MAX(tot) FROM #tmpPage'
  execute sp_executesql
  @sql,
  @ParmDefinition,
  @totrecords = @TotalRecords OUTPUT;
  
  SET @TotalRecords = isnull(@TotalRecords,0)
  
  --Perform SELECT to return exact columns that NopCommerce expected from old Stored Procedure
  SELECT DISTINCT tb1.* FROM
  (SELECT  TOP(@TotalRecords) p.ProductId,
    p.name as Name,
    p.ShortDescription,
    p.FullDescription,
    p.AdminComment,
    p.TemplateId,
    p.ShowOnHomePage,
    p.MetaKeywords,
    p.MetaDescription,
    p.MetaTitle,
    p.SEName,
    p.AllowCustomerReviews,
    p.AllowCustomerRatings,
    p.RatingSum,
    p.TotalRatingVotes,
    p.Published,
    p.Deleted,
    p.CreatedOn,
    p.UpdatedOn  
    FROM #tmpPage pc
    LEFT JOIN Nop_Product p on p.ProductId = pc.ProductId
  ORDER BY pc.ROWNUMBER) tb1
  --ORDER BY necessary cause getting from temporary table order is not garanted to be the one used to create ROWNUMBER values


  
      --Can Be Null if no records selected with the parameters passed to the stored procedure
  SET @TotalRecords = isnull(@TotalRecords,0)
  
  
  SET ROWCOUNT 0

  DROP TABLE #FilteredSpecs
  DROP Table #tmpPage
  
  
END

chris_rowtcliff wrote:


Thanks alot this add-on increased performace of my website.

Here is a nice tutorial on how to use it, i think could be helpful to someone :

http://learn.iis.net/page.aspx/688/using-the-iis-application-warm-up-module

Just wanted to share:

I had been plagued with the same problems as others here, general sluggishness as well as unacceptable slowness on first page render.  In addition to the sql script changes above which helped with the general slowness, last night I took a look at all the Application Pools on my server.  I had three different ones all being used by different websites.  I decided to consolidate all my sites under a single app pool and see if that did anything.  Lo and behold, this morning my site popped up immediately, no wait time whatsoever!  I don't know a ton about how app pools behave when left to their own devices, but perhaps when resources are tight, a pool may shut itself down so that other more active pools can perform better.  Consolidating all my sites into one seems to ensure it will always stay up.

I am also getting slow page loads.

I have installed the nopCommerce 1.90 on my GoDaddy hosted website.  Have not added any data (products) or installed the sample data.  Only installed the required data script.

When I go to the website it is very slow loading for the first time.  Took the suggestion on adding indexes per the forum.

No difference.


Any ideas?