Can you improve performance with sqlserver views?

Question

I have a sql function which looks up the parent of a certain node in a left right tree. This takes a lot of time. If I create a view that stores for each node its parent, will that speed up things? (I would then ask the view to get the parent node).

Answer 1

Yes, that should speed things up. Have the view return the child id and the parent details.

This way you can join from your table to the view using the child id.

Answer 2

The view (and the underlying function) will be reevaluated each time it is accessed, so creating a view will not improve anything.

In fact, you will most probably speed up things by removing the UDF and just using this to find your parent node:

SELECT  mpp.id
FROM    mytable mc
CROSS APPLY
        (
        SELECT  TOP 1 id
        FROM    mytable mp
        WHERE   mp.lft BETWEEN m.lft AND m.rgt
        ORDER BY
                mp.lft DESC
        ) mpp

, especially if you have an index on lft which also covers rgt:

CREATE INDEX ON mytable (lft) INCLUDE (rgt)

Nested sets model is not very efficient for SQL Server. It was designed for legacy system which did not allow recursive queries, but for most modern database systems adjacency list is much more efficient.

See this article in my blog for details:

• Adjacency list vs. nested sets: SQL Server