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

  • How can I log user name when SQL Server database on different server than web server?
  • sql server : create indexes on foreign keys where necessary
  • Order by Field in non-alphabetical order in SQL Server 2000
  • How to allow window background service to access SQL Server database?
  • How can i get the list of all database name with their username and roles in t-sql?
  • Is it a good practise to have circular reference in two DB tables
  • DB Designer in Visual Studio 2010
  • Stored Procedure slower than query in SSMS
  • Authenticating to a SQL Server instance as a Windows User via JDBC
  • Create an index on SQL view with UNION operators? Will it really improve performance?
  • SQL Server HASHBYTES conversion inconsistency?
  • How to set that a table foreign key value must be same in another table
  • 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.