Alternative to recursive Common Table Expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Alternative to recursive Common Table Expression

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2015-02-15 : 16:20:00

I have the following stored procedure being called by an application and MS SQL Server Profiler confirms it as running 180,000 reads on the database which is way too many.
Can anyone suggest an alternative script/stored procedure that would do the same job but more efficiently and with less reads?
I'm happy to create a table to dump the initial data in but what syntax to use to do that?


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetSourceInvestigations](@ID int)
AS
BEGIN

WITH Results (ID, SourceID, OriginalID, TransferReason, [Level])
AS
(
      SELECT ID, NULL, ID, TRANSFER_REASON, 0 AS [Level]
      FROM tblREFERRALS
      WHERE TRANSFER_REASON = 1 AND SOURCE_ID IS NULL
      UNION ALL
      SELECT ID, SOURCE_ID, Results.OriginalID, TransferReason, [Level] + 1
      FROM tblMAIN_REFERRALS
      JOIN Results ON Results.ID = tblREFERRALS.SOURCE_ID
)
SELECT *
FROM Results 
JOIN vwIMAGING_SUMMARY_TRANSFERS ist 
ON Results.ID = ist.ID
LEFT JOIN tblREFERRALS mr
ON Results.ID = mr.ID
WHERE OriginalID = (SELECT OriginalID FROM Results WHERE ID = @ID) AND
ID < @ID
ORDER BY [Date Performed] DESC
	
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-15 : 16:37:16

If you know the max depth of the recursion you can rewrite it as a series of self-joins. Most business situations have a practical maximum depth, even though in theory there is no limit. Note that the default for MAXRECURSION is 100, IIRC and you're not overriding that. I'll bet that you will never get to 100! Maybe 5 or 10. Easy to write as a series of self-joins (though not as elegant) and probably will perform better. You can even build in a guard on the last self join to catch a depth-violation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources