Performance of this Stored Procedure

Hi Guys,

This question is to elicit performance opinion on the following stored proc. I have spent most of today tuning it according to various articles and wanted to learn if any more can be done. Under light load (5 users) the SP executes in about 60-70ms. This increases rapidly until 50 user concurrent load produces execution durations of 5000ms!

Not being extremely experienced at SQL Server performance, I wanted to know whether this is about right. Some extra information:

- Runs on server with dual 1Ghz Pentium 3 processors and 512MB RAM
- Runs on same server as IIS and Tomcat 5 which is also consuming CPU cycles/RAM
- The various user defined functions used in the SP are mostly simple count(1) selects. Substituting the selects for static numbers reveals not much difference in performance.

Your advice appreciated.

--------------------------------------------------------------------------------
-- Navigate
-- Navigation interface. Retrieves search results based on path and
-- additional criteria.
--
-- @param s_UserName the user
-- @param s_Path the path
-- @param s_OrderBy column to order by
-- @param s_OrderDir direction ASC or DESC
-- @param i_Page the page to retrieve
-- @param i_Size the number of results to retrieve
-- @param s_Criteria additional WHERE restriction
-- @return results and total row count
--------------------------------------------------------------------------------

CREATE PROCEDURE sp_Navigate (
  @i_TotalRows INT OUTPUT,
  @s_Username VARCHAR(20),
  @s_Path VARCHAR(1024),
  @s_OrderBy VARCHAR(20),
  @s_OrderDir VARCHAR(4),
  @i_Page INT,
  @i_Size INT,
  @s_Criteria VARCHAR(1024)
)
AS
BEGIN

  DECLARE @i_Start INT, @i_End INT;
  DECLARE @s_SQL AS NVARCHAR(4000);
  SET @i_Start = (((@i_Page - 1) * @i_Size) + 1);
  SET @i_End = (@i_Start + @i_Size - 1);

  --
  -- retrieve ids for row restrict (with security mask)
  --
  CREATE TABLE #TMP (
    row INT IDENTITY(1, 1) PRIMARY KEY,
    dID INT
  );

  SET @s_SQL = 'INSERT INTO #TMP (dID)
    SELECT r.dID
    FROM stellent.dbo.Revisions r INNER JOIN stellent.dbo.DocMeta m ON r.dID = m.dID
    WHERE
      stellent.dbo.userCanRead(@s_Username, r.dSecurityGroup) > 0 AND
      (m.xPath = @s_Path) AND
      (r.dStatus = ''RELEASED'') AND
      (r.dReleaseState = ''Y'')
    ORDER BY ' + @s_OrderBy + ' ' + @s_OrderDir;

  EXECUTE sp_executesql
    @s_SQL,
    N'@s_Username VARCHAR(20), @s_Path VARCHAR(1024)',
    @s_Username, @s_Path;

  SET @i_TotalRows = @@ROWCOUNT;

  --
  -- get full information
  --
  SET @s_SQL = '
    SELECT r.dID, r.dSecurityGroup, r.dDocType, r.dDocName, r.dDocAuthor, r.dDocTitle, r.dInDate, r.dOutDate,
      m.xLastUpdated, m.xAbstract, m.xAuthoredBy, m.xPath, m.xCountryAudience, r.dWebExtension, m.xUsage, m.xGenericDocType, r.dPublishType,
      db_iQ.dbo.getContentTotalRatings(r.dDocName) AS totalRatings,
      db_iQ.dbo.getContentTotalRatingsHelpful(r.dDocName) AS totalRatingsHelpful,
      db_iQ.dbo.getContentRatingAvg(r.dDocName) AS avgRating,
      db_iQ.dbo.getContentLastRatedByUser(@s_Username, r.dDocName) AS userLastRated
    FROM stellent.dbo.Revisions r INNER JOIN stellent.dbo.DocMeta m ON r.dID = m.dID INNER JOIN #TMP tmp ON m.dID = tmp.dID
    WHERE
      tmp.row >= @i_Start AND tmp.row <= @i_End ' + @s_Criteria  + '
    ORDER BY ' + @s_OrderBy + ' ' + @s_OrderDir;

  EXECUTE sp_executesql
    @s_SQL,
    N'@s_Username VARCHAR(20), @s_Path VARCHAR(1024), @i_Start INT, @i_End INT',
    @s_Username, @s_Path, @i_Start, @i_End;

  --
  -- clean
  --
  DROP TABLE #TMP;

END
GO