I have this simple recursive CTE for a hierarchy of folders and their paths:
WITH paths AS (
SELECT Id, ParentId, Name AS [Path] FROM Folders
WHERE ParentId IS NULL
UNION ALL
SELECT f.Id, f.ParentId, [Path] + '/' + f.Name FROM Folders f
JOIN paths on f.ParentId = paths.Id
)
SELECT Id, [Path] from paths
WHERE Id = @FolderId
On my local SQL Server express, it runs in 35 ms no problem. On my Azure SQL database, it occasionally takes around 400 ms. The Azure SQL table only has around 2000 rows, and other simple queries only take around 80ms. I have indexes on Id and ParentId. My app can make this query very frequently and it's annoying to have to wait half a second or more every time. Here are screenshots of the two separate plans, no idea why they are different:
https://www.brentozar.com/pastetheplan/?id=Hky4zASAI
https://www.brentozar.com/pastetheplan/?id=H17LfRHAU
Is Azure SQL just slower?
EDIT: this has nothing to do with Azure SQL, the table there was a lot bigger than my test.
Best Answer
I checked your query again and it seems you can rewrite it this way: