2

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 SQL Express

https://www.brentozar.com/pastetheplan/?id=H17LfRHAU Azure SQL

Is Azure SQL just slower?

EDIT: this has nothing to do with Azure SQL, the table there was a lot bigger than my test.

7
  • 1
    could you please share actual execution plans using - brentozar.com/pastetheplan ? Do you have exactly the same table structure and data on local server and in Azure? – NikitaSerbskiy Jun 28 '20 at 8:09
  • 1
    @Nikita I am using datagrip and i'm not sure how to get query plan as XML for this tool. The tables' schema are the same, but i only have a few dozen rows locally – Eric B Jun 28 '20 at 8:14
  • 1
    @Nikita nvm i figured it out and added the links – Eric B Jun 28 '20 at 8:19
  • 1
    did you compare query performance using the same data on both servers? – NikitaSerbskiy Jun 28 '20 at 8:21
  • 1
    no, it's only a few thousand rows on the azure server and the hierarchy is never more than 4 folders deep. I don't think it should make that much difference. – Eric B Jun 28 '20 at 8:28
3

I checked your query again and it seems you can rewrite it this way:

WITH paths AS (
    SELECT Id, ParentId, Name AS [Path] FROM Folders
    WHERE Id = @FolderId

    UNION ALL

    SELECT f.Id, f.ParentId, f.Name  + '/' + [Path] FROM Folders f
    JOIN paths on paths.ParentId = f.Id
    )
SELECT @FolderId AS Id, [Path] from paths
WHERE ParentId IS NULL
2
  • wow! super fast this way! TY!!! 80 ms – Eric B Jun 28 '20 at 9:15
  • 1
    @EricB It works faster because it works in "reverse" way (it starts with required folder and check its parent and parent of the parent and so on). Your original query generates the whole hierarchy - it goes from the root through the whole tree and creates rows with full path for each folder and then apply filter to them to show you the only one row you need. – NikitaSerbskiy Jun 28 '20 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.