Can you improve performance with sqlserver views?
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).
2 Solutions collect form web for “Can you improve performance with sqlserver views?”
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
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
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.