Sql-server – Why can’t unused columns and tables be ignored in a recursive CTE

cterecursivesql server

Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and joined tables are queried, even if not necessary. Why is that? Can I help SQL Server to ignore unused columns/tables here?

Example:

;WITH cte AS
(
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    WHERE cn.ParentId IS NULL
    UNION ALL
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    JOIN cte ON cte.CatalogNodeId = cn.ParentId
)
SELECT CatalogNodeId FROM cte

This is a simple recursive CTE resolving a hierarchical parent-child structure, adding a localized name to each node.

Even though not necessary, the execution plan shows that column CatalogNodeType is retrieved and dbo.LocalizedCatalogNodes is joined for each iteration.

Now comment out the UNION ALL part making it a non-recursive CTE, and the execution plan suddenly consists of just two steps, not containing column CatalogNodeType or table dbo.LocalizedCatalogNodes.

Best Answer

It's just a matter of order of operations, which is dependent on what the query optimizer thinks is going to be the most efficient execution plan. Yes, we can obviously see that in the end, the final SELECT list only consists of the CatalogNodeId column, and therefore the other columns of the CTE don't matter, so Microsoft probably could've implemented recursive CTEs to be a little bit better with their available execution plans in this regard. But even easier it is for the developer to recognize the lack of necessity for those columns in the recursive CTE, and therefore can be removed from the query, which is generally best practice anyway.

Related Solutions

PostgreSQL tree structure and recursive CTE optimization

If you really have to modify these data rarely, then you can simply store the result of the CTE in a table, and run queries against this table. You can define indexes based on your typical queries.
Then TRUNCATE and repopulate (and ANALYZE) as necessary.

On the other hand, if you can put the CTE in separate stored procedures rather than a view, you can easily put your conditions in the CTE part rather then the final SELECT (which is basically what you do querying against tree_view_1), so that much less rows will be involved in the recursion. From the query plan it looks like that PostgreSQL estimates row numbers based on some far-from-true assumptions, probably producing suboptimal plans - this effect can be reduced somewhat with the SP solution.

EDIT I may miss something, but just noticed that in the non-recursive term you don't filter the rows. Possibly you want to include only root nodes there (WHERE parent_id IS NULL) - I'd expect much less rows and recursions this way.

EDIT 2 AS it slowly became clear for me from the comments, I misthought the recursion in the original question going the other way. Here I mean starting from the root nodes and going deeper in the recursion.

Query for hierarchical RBAC scenario

Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user.

CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
    SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
    FROM (
        WITH RECURSIVE
          deep_roles(user_id, role_id, parent_id, depth) AS (
            SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
            FROM role_roles rr
            INNER JOIN role_users ru ON rr.role_id = ru.role_id
            UNION
            SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
            FROM deep_roles dr, role_roles rr
            WHERE rr.role_id=dr.parent_id
            AND dr.depth < 10
          )
        SELECT user_id, role_id, parent_id FROM deep_roles
        WHERE depth > 0
        UNION
        SELECT user_id, role_id, null parent_id
        FROM role_users
    ) AS urd
    INNER JOIN permission_roles pr
        ON pr.role_id = urd.role_id
        OR pr.role_id = urd.parent_id
    UNION
    SELECT user_id, permission_id, null role_id, null parent_id
    FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;

I found I had to keep the depth check otherwise the loop would never exit if there was a cycle in the role inheritance chain.

If anyone has any suggestions on how this could be improved, please let me know!

Best Answer

Related Solutions

PostgreSQL tree structure and recursive CTE optimization

Query for hierarchical RBAC scenario

Related Question