Solved

Performance of this Stored Procedure

Posted on 2004-08-19
18
238 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:loopyadc
  • 8
  • 7
  • 2
18 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 11841240
Have you tried using a view or a subselect instead of Temporary tables? Views may not be the best solution here, but I don't think temporary tables are either, especially if you have a large number of users.

You could try to replace the join (with the temporary table) in the second SQLString with the select statement used to create the temporary table instead.
0
 
LVL 11

Expert Comment

by:Otana
ID: 11841431
Try this:

Drop the first @s_SQL and execute, and replace the second with following code. You may have to tweak it a bit, since I can't test it obviously. :-)


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
(SELECT r.dID
    FROM (select dID from stellent.dbo.Revisions where r.dStatus = ''RELEASED'' and r.dReleaseState = ''Y'') r
    INNER JOIN (select dID from stellent.dbo.DocMeta where stellent.dbo.userCanRead(@s_Username, r.dSecurityGroup) > 0 and m.xPath = @s_Path) m ON r.dID = m.dID
    ORDER BY ' + @s_OrderBy + ' ' + @s_OrderDir) + ' tmp ON m.dID = tmp.dID
    WHERE
      tmp.row >= @i_Start AND tmp.row <= @i_End ' + @s_Criteria  + '
    ORDER BY ' + @s_OrderBy + ' ' + @s_OrderDir;

0
 

Author Comment

by:loopyadc
ID: 11841514
I'm afraid I get the following error

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
0
 
LVL 11

Expert Comment

by:Otana
ID: 11841551
You can drop the ORDER BY in the subselect, since the data will be ordered afterwards. Or, you can add TOP x (where x is a very large number, certain to be larger than the number of rows returned) in the subselect.
0
 

Author Comment

by:loopyadc
ID: 11841622
i think you missed the point that the tmp table was so that i can paginate search results .. it used a primary key as a row column ... i don;t think your solution maintains pagination unfortunately.
0
 
LVL 11

Expert Comment

by:Otana
ID: 11842052
Indeed, I missed that one. Ok, let's try something else :-)

Try replacing the first @s_SQL with following code, it may make the INSERT a bit faster.

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

Also, which indexes do you have?
0
 

Author Comment

by:loopyadc
ID: 11842072
i'll plug this in ... indexes are on r.dID and m.xPath

cheers .. brb
0
 

Author Comment

by:loopyadc
ID: 11842108
your query is generating the following ...

The column prefix 'r' does not match with a table name or alias name used in the query...will look at this now but if it is obvious to you .. thought i would mention it
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 11

Expert Comment

by:Otana
ID: 11842157
Yes, sorry, the "(@s_Username, r.dSecurityGroup) > 0" clause should be in the other table's SELECT statement.
0
 

Author Comment

by:loopyadc
ID: 11842159
seems to be that r.securityGroup cannot access r?
0
 
LVL 11

Accepted Solution

by:
Otana earned 350 total points
ID: 11842205
This should do it (I removed prefixes and put all clauses where they belong):

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

Author Comment

by:loopyadc
ID: 11842247
oddly, this has produced

Invalid column name 'dDocTitle'. This goes away by reverting to the original first s_SQL although that statement does not even use dDocTitle which comes later in the 2nd select!!
0
 
LVL 11

Expert Comment

by:Otana
ID: 11842360
Have you also returned to the original second @s_SQL?
0
 

Author Comment

by:loopyadc
ID: 11842426
yeah i reverted to the original 2nd because it has the row stuff in it. I only now have a changed 1st s_SQL with the latest version you sent.
0
 
LVL 4

Assisted Solution

by:davehilditch
davehilditch earned 150 total points
ID: 11881902
Can you try with your original version, adding (nolock) after each table name inside each query (except for your temp tables where it's not needed).

e.g.

SET @s_SQL = 'INSERT INTO #TMP (dID)
    SELECT r.dID
    FROM stellent.dbo.Revisions (nolock) r INNER JOIN stellent.dbo.DocMeta (nolock) m ON r.dID = m.dID
    WHERE

etc and same at final select where you join back in to live tables.

Should speed things up immensely as it sounds like you're having concurrency issues.  Let me know if it does/doesn't help and we should be able to narrow it down.

Dave Hilditch.
0
 

Author Comment

by:loopyadc
ID: 11881951
Hi there, I have added these statements but will only get time tomorrow to run them through profiler. I will make sure I do this and come back to you. Thanks loads, I hope it helps! .. is this 2nd sql correct per your suggestion?

  SET @s_SQL = '
    SELECT r.dID, r.dSecurityGroup, r.dDocType, r.dDocName, r.dDocAuthor, r.dDocTitle,
      CONVERT(CHAR(10), r.dInDate, 103) + '' '' + CAST(DATEPART(hh, r.dInDate) AS CHAR(2))  + '':'' + CAST(DATEPART(mi, r.dInDate) AS CHAR(2)) AS 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 (NOLOCK) 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
    ORDER BY ' + @s_OrderBy + ' ' + @s_OrderDir;
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 11882226
Yeah but you missed the (NOLOCK) on the first table - and actually, sorry, it should be alias (NOLOCK) so that line should read:

 FROM stellent.dbo.Revisions r (NOLOCK) INNER JOIN stellent.dbo.DocMeta m (NOLOCK) ON r.dID = m.dID INNER JOIN #TMP tmp ON m.dID = tmp.dID
   
Also, if it's not this issue, then it's almost definitely to do with your tempdb being overloaded.  This would only really be an issue if you are either inserting loads of data to your temp tables and it's causing disk locking - check that in performance monitor - or it's resizing the database.  When SQL Server resizes it's databases it takes a wee while.  While resizing, everything else will be put on hold, so you might like to try resizing tempdb from Enterprise manager.  Right click tempdb, go to properties, then increase the size of the data file(s).

Good luck!

Dave Hilditch.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now