Sometimes cte takes lone time

I have a recursive routing that normally takes less than a second but
sometimes takes around 7 seconds and there is only 3 records in the table.

Why would that be? I can't seem to make it happen by clearing the cache and
bufferes but it does happen occasionally.

****************************************************
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

declare @baseDocumentID int
Set @baseDocumentID = 5

;WITH PrrCTE(nDocumentID,nPrerequisiteFor, Level, baseDocumentID,
basePreReq )
AS
(
-- Anchor Member
SELECT nDocumentID, nPrerequisiteFor, 0, nPrerequisiteFor as
baseDocumentID, nDocumentID as basePreReq
FROM tblPrerequisite
WHERE nPrerequisiteFor = @baseDocumentID -- Start original document

UNION ALL

-- Recursive Member
SELECT
e.nDocumentID, e.nPrerequisiteFor, m.Level+1, m.baseDocumentID,
m.basePreReq
FROM
tblPrerequisite AS e
INNER JOIN PrrCTE m ON e.nPrerequisiteFor = m.nDocumentID
)
-- Using the CTE
SELECT nDocumentID, nPrerequisiteFor,Level, baseDocumentID, basePreReq
FROM PrrCTE
Order by basePreReq,Level
********************************************************

Am I missing something? The query works fine but am confused by the lag
time it occasionally takes.

Thanks,

Tom

tshad (tfs@dslextreme.com) writes:
> I have a recursive routing that normally takes less than a second but
> sometimes takes around 7 seconds and there is only 3 records in the table.

With that amount of data, the reasonable explanation I can think of
is blocking. Or your machine is about to cave in.

--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx