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:
Is Azure SQL just slower?
EDIT: this has nothing to do with Azure SQL, the table there was a lot bigger than my test.