Sometimes cte takes lone time

[tshad]

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

[Erland Sommarskog]

tshad (t...@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, esq...@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

[tshad]

Erland Sommarskog wrote:
> tshad (t...@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. :-)

There is no blocking - but the caving would be a possible explanation :)

Thanks,

Tom

[Erland Sommarskog]

Yet a possibility if the table has a lot larger is that it still is
very big and lot of space, and therefore takes a long time to scan. What
does sp_spaceused report for the table?