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).

  • What can I do to set the value of a column in a new row in such a way that it have the same value of the auto increment primary key of the table?
  • Refreshing a database or table with management tools
  • Equivalence of brackets and punctuation in Unicode and English characters
  • Limiting results in SQL Server Compact
  • Join characters using SET BASED APPROACH (Sql Server 2005)
  • SQL Select Permissions
  • get output of last Process on SSAS cube
  • Why is SQL server throwing this error: Cannot insert the value NULL into column 'id'?
  • saving the FOR XML AUTO results to variable in SQL
  • How to combine a linq query dynamically?
  • create index using openquery
  • How to Change from Sql Server Windows mode to Mixed Mode *Using SQL* (SQL Server 2008)
  • 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 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

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.