Thread: How can this SP be optimized

This is SP for category display from VibeTrib:

Code:

IF @EntityName = 'Category' BEGIN
        --SELECT Entity.CategoryID EntityID, Entity.CategoryGUID EntityGuid, Name,ColWidth,Description,SEName,SEKeywords,SEDescription,SETitle,SENoScript,SEAltText,ParentCategoryID ParentEntityID,DisplayOrder,SortByLooks,XmlPackage,Published,ContentsBGColor,PageBGColor,GraphicsColor
		SELECT Entity.CategoryID EntityID, Entity.CategoryGUID EntityGuid, Name,ColWidth,Description,SEName,SEKeywords,SEDescription,SETitle,SENoScript,SEAltText,ParentCategoryID ParentEntityID,DisplayOrder,XmlPackage,Published
		
			   ,isnull(NumProducts, 0) NumObjects, PageSize,  Summary, SkinID, TemplateName
        FROM dbo.Category Entity with (NOLOCK)
          left join (SELECT pc.CategoryID, COUNT(pc.ProductID) AS NumProducts
                     FROM  dbo.ProductCategory pc with (nolock)
                         join [dbo].Product p with (nolock) on pc.ProductID = p.ProductID  and p.deleted=0 and p.published=1
                     GROUP BY pc.CategoryID
                    ) a on Entity.CategoryID = a.CategoryID
        WHERE Published >= @PublishedOnly and Deleted=0
        AND Entity.CategoryID = @EntityID
        ORDER BY ParentCategoryID,DisplayOrder,Name
    END

I have removed what I could, and there are some improvements already. How can it be more optimized?

Some tweaks that gave me some marginal improvement on a small number of rows (avg run of the original code over 10 runs was 254ms and the avg for this code was 172ms). The larger the category and product tables are of course the more such small improvements will help.

1. Moved the filter for the ProductID to a HAVING clause in the inner query. This gets the data down to a fewer number of rows before the outer query is joined to it since there's a GROUP BY on this value already.

2. Moved the Product.Deleted and Product.Published filters from the INNER JOIN into the HAVING clause after the filter for the CategoryID. Now the filtering of these non-granular values comes after the category has been identified. In other words, the query finds the CategoryID and then looks at that row only instead of having to scan through what will mostly be Zeros or Ones. I considered and tested using a WHERE clause instead and leaving the Deleted and Published filters out of the GROUP BY clause, but even though the query plans looked identical, there seemed to be a marginal reduction in overall execution time.

3. Since the CategoryID has been filtered down to a specific CategoryID in the inner query, I changed the relationship of the outer query to an INNER JOIN. If there are no Products related to the requested Category then I assume nothing should be returned so an OUTER JOIN may result in unnecessary table or index scans.

4. Changed the @PublishedOnly parameter to be an equality. Not sure if this can ever be anything other than 0/1 and if so an inequality check doesn't make sense.

Code:

DECLARE @EntityID INT
SET @EntityID = 33

DECLARE @PublishedOnly TINYINT
SET @PublishedOnly = 1

SELECT
    Entity.CategoryID EntityID
   ,Entity.CategoryGUID EntityGuid
   ,Name
   ,ColWidth
   ,Description
   ,SEName
   ,SEKeywords
   ,SEDescription
   ,SETitle
   ,SENoScript
   ,SEAltText
   ,ParentCategoryID ParentEntityID
   ,DisplayOrder
   ,XmlPackage
   ,Published
   ,ISNULL(NumProducts,0) NumObjects
   ,PageSize
   ,Summary
   ,SkinID
   ,TemplateName
FROM
    dbo.Category Entity WITH (NOLOCK)
INNER JOIN (
            SELECT
                pc.CategoryID
               ,COUNT(pc.ProductID) AS NumProducts
            FROM
                dbo.ProductCategory pc WITH (NOLOCK)
            INNER JOIN 
		dbo.Product p WITH (NOLOCK)
                ON pc.ProductID = p.ProductID
            GROUP BY
                pc.CategoryID
               ,p.deleted 
               ,p.published 
            HAVING
                pc.CategoryID = @EntityID
                AND p.deleted = 0
                AND p.published = 1 
           ) a
    ON Entity.CategoryID = a.CategoryID
WHERE
    Published = @PublishedOnly
    AND Deleted = 0
ORDER BY
    ParentCategoryID
   ,DisplayOrder
   ,Name

These performance stats also assume the existence of the following indexes:

Code:

CREATE NONCLUSTERED INDEX [IX_Category_CategoryID] ON [dbo].[ProductCategory] 
(
	[CategoryID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Product_Published_Deleted] ON [dbo].[Product] 
(
	[Published] ASC,
	[Deleted] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Thank you for your kind reply. I will give it a test and place the results here.