In Valid Logic

Endlessly expanding technology

Fix for Gallery scaling issue in Community Server v1.0

With the release of the nGallery to CS converter, we finally got people with good sized nGallery installs running on Community Server.  Prior to this, it was most myself and others running fair small, frequently rebuilt installations, or my own website, which only has a moderate amount of pictures.

So we instantly started getting people with thousands of pictures in their Gallery and some bad performance issues started showing up.  With some debugging, we tracked it down to the cs_PostCategories_Get procedure timing out, causing an exception, and breaking a bunch of things.

The cause of the problem?  The procedure was using some inefficient SQL in order to get the total number of posts and the most recent post date in it and a total of all of its subcategories.  Namely, it was using a couple of subqueries (meaning query within a query within a query), the IN clause, and a “select top 1 … order by postdate desc” in a few of the subqueries.  On one database with ~95 categories and ~4000 pictures, this command could take almost a minute to execute.

The solution?  We updated the sproc to use max(PostDate) instead of the top/order by.  This brought the execution time down to ~1 second.  We also changed all the subsubqueries and IN clauses to be inner joins.  Doing this brought the execution time down to <1 second.  On my own site, which has about 50 categories and around 900 pictures, execution time went from 4 seconds to <1 second.

To update the cs_PostCategories_Get procedure on your own site, run the following script:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_PostCategories_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_PostCategories_Get]
GO

CREATE PROCEDURE dbo.cs_PostCategories_Get
 @SectionID int=0,
 @ApplicationKey nvarchar(256)=null,
 @CategoryType smallint
AS

if @ApplicationKey Is Not Null
begin
 SELECT C.*,
  TotalThreads = (SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = C.CategoryID),
  TotalSubThreads = (SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = C.CategoryID OR jC.Path LIKE C.Path + convert(nvarchar, C.CategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID),
  MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = C.CategoryID),
  MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = C.CategoryID OR jC.Path LIKE C.Path + convert(nvarchar, C.CategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID)
 FROM cs_Post_Categories C
 LEFT JOIN cs_Sections F ON F.SectionID = C.SectionID
 WHERE F.ApplicationKey = @ApplicationKey
  AND C.CategoryType = @CategoryType
end
else
begin
 SELECT C.*,
  TotalThreads = (SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = C.CategoryID),
  TotalSubThreads = (SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = C.CategoryID OR jC.Path LIKE C.Path + convert(nvarchar, C.CategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID),
  MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = C.CategoryID),
  MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = C.CategoryID OR jC.Path LIKE C.Path + convert(nvarchar, C.CategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID)
 FROM cs_Post_Categories C
 WHERE C.SectionID = @SectionID
  AND C.CategoryType = @CategoryType
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[cs_PostCategories_Get]  TO [public]
GO

I’d also recommend that if you are running the nGallery to CS::Gallery converter, that afterwards you rebuild the indexes on all of the CS tables.  You could do this by running the script on this site.

Wednesday, March 09, 2005

 
blog comments powered by Disqus