SQL Server Performance

View performance vs select statement

Discussion in 'Performance Tuning for DBAs' started by bmoeinza, Jul 14, 2006.

  1. bmoeinza New Member

    Hi all,

    My question is why sql server is using two different execution plan for running a view then the underlying select statement. Here is my scenario,

    I have the following view.


    CREATE VIEW [dbo].[v_client_service_by_rcpt_id] AS
    SELECT DISTINCT
    t_receipt_master.receipt_id, t_receipt_master.job_name,
    t_receipt_detail.job_number, t_receipt_detail.tracking_num1,
    t_receipt_master.receipt_date, SUM(ISNULL(t_receipt_detail.quantity,0))
    AS total_received, SUM(ISNULL(t_hu_detail.actual_qty,0) - ISNULL(t_hu_detail.f2,0))
    AS qty_on_hand, t_receipt_detail.item_number
    FROM t_receipt_master(nolock), t_receipt_detail(nolock), t_hu_detail(nolock) where
    t_receipt_master.receipt_id = t_receipt_detail.receipt_id
    and t_receipt_detail.pallet_id = t_hu_detail.hu_id
    and t_receipt_detail.item_number = t_hu_detail.item_number
    --and t_hu_detail.wh_id = t_receipt_master.wh_id
    GROUP BY t_receipt_master.receipt_id,t_receipt_detail.item_number,
    t_receipt_master.job_name, t_receipt_detail.job_number,
    t_receipt_detail.tracking_num1, t_receipt_master.receipt_date


    when I run the following statement:

    SELECT * FROM v_client_service_by_rcpt_id WHERE receipt_id LIKE '542220%'

    it takes about 1 miunte and 30 seconds, however when I run the followiong query which is basically the same as view, I get the result in less than a second.


    SELECT DISTINCT
    t_receipt_master.receipt_id, t_receipt_master.job_name,
    t_receipt_detail.job_number, t_receipt_detail.tracking_num1,
    t_receipt_master.receipt_date, SUM(ISNULL(t_receipt_detail.quantity,0))
    AS total_received, SUM(ISNULL(t_hu_detail.actual_qty,0) - ISNULL(t_hu_detail.f2,0))
    AS qty_on_hand, t_receipt_detail.item_number
    FROM t_receipt_master(nolock), t_receipt_detail(nolock), t_hu_detail(nolock) where
    t_receipt_master.receipt_id = t_receipt_detail.receipt_id
    and t_receipt_detail.pallet_id = t_hu_detail.hu_id
    and t_receipt_detail.item_number = t_hu_detail.item_number
    --and t_hu_detail.wh_id = t_receipt_master.wh_id
    and t_receipt_master.receipt_id LIKE '542220%'
    GROUP BY t_receipt_master.receipt_id,t_receipt_detail.item_number,
    t_receipt_master.job_name, t_receipt_detail.job_number,
    t_receipt_detail.tracking_num1, t_receipt_master.receipt_date


    The execution plans are different, the view does an inner join on t_receipt_detail and t_hu_detail and then an inner join on t_receipt_master, but the select statement first merge t_receipt_master and t_receipt_detail and then on t_hu_detail.

    My question is why tye execution plan is different and how can I force the view to use the other execution plan.

    Thanks a lot.
    bmoeinza
  2. spacemonkey New Member

    Your view and select statements are not quite as similar as they may seem. Think of what sql server has to do to run your query.

    In the case of the view, the view select statement is parsed, run, and the result is returned. Then after that result is returned, it goes in and limits the results further depending on your WHERE receipt_id LIKE '542220%' clause.

    For the normal select statement sql server knows all the information up front including your where clause. With it knowing that you only want receipt_id like '542220%' it can generate a faster result because it doesn't process the other possibilities.

    If you want to make the select statement work by using different receipt_id values you can try a stored procedure and pass the receipt id in as a parameter. That way you will get the better run time without sql server having to do all the work necessary for the view and the ability to change the receipt_id values.

    John
  3. bmoeinza New Member

    Thanks John,

    I appreciate your comment, I did not know view first parses and runs and then applies the where clause. this is making sence now.

    Bmoeinza
  4. merrillaldrich New Member

    That's not actually true: I think the query optimizer will merge the view logic into the query, then make an execution plan for the whole thing (unless it's an indexed view). It is not restricted to "first process the view." From BOL:

    "When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view, and resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

    For example, consider the following view:

    USE Northwind
    GO
    CREATE VIEW EmployeeName AS
    SELECT EmployeeID, LastName, FirstName
    FROM Northwind.dbo.Employees
    GO

    Given this view, both of these SQL statements perform the same operations on the base tables and produce the same results:

    /* SELECT referencing the EmployeeName view. */
    SELECT LastName AS EmployeeLastName,
    OrderID, OrderDate
    FROM Northwind.db:confused:rders AS Ord
    JOIN Northwind.dbo.EmployeeName as EmpN
    ON (Ord.EmployeeID = EmpN.EmployeeID)
    WHERE OrderDate > '31 May, 1996'

    /* SELECT referencing the Employees table directly. */
    SELECT LastName AS EmployeeLastName,
    OrderID, OrderDate
    FROM Northwind.db:confused:rders AS Ord
    JOIN Northwind.dbo.Employees as Emp
    ON (Ord.EmployeeID = Emp.EmployeeID)
    WHERE OrderDate > '31 May, 1996'

    The SQL Query Analyzer showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements."

  5. bmoeinza New Member

    quote:Originally posted by merrillaldrich

    That's not actually true: I think the query optimizer will merge the view logic into the query, then make an execution plan for the whole thing (unless it's an indexed view). It is not restricted to "first process the view." From BOL:

    "When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view, and resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

    For example, consider the following view:

    USE Northwind
    GO
    CREATE VIEW EmployeeName AS
    SELECT EmployeeID, LastName, FirstName
    FROM Northwind.dbo.Employees
    GO

    Given this view, both of these SQL statements perform the same operations on the base tables and produce the same results:

    /* SELECT referencing the EmployeeName view. */
    SELECT LastName AS EmployeeLastName,
    OrderID, OrderDate
    FROM Northwind.db:confused:rders AS Ord
    JOIN Northwind.dbo.EmployeeName as EmpN
    ON (Ord.EmployeeID = EmpN.EmployeeID)
    WHERE OrderDate > '31 May, 1996'

    /* SELECT referencing the Employees table directly. */
    SELECT LastName AS EmployeeLastName,
    OrderID, OrderDate
    FROM Northwind.db:confused:rders AS Ord
    JOIN Northwind.dbo.Employees as Emp
    ON (Ord.EmployeeID = Emp.EmployeeID)
    WHERE OrderDate > '31 May, 1996'

    The SQL Query Analyzer showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements."


  6. bmoeinza New Member

    Then, how can you explain the difference that I am seeing between the view's execution plan and the select statement, view is running way less efficient that select.

    Bmoeinza



    quote:Originally posted by merrillaldrich

    That's not actually true: I think the query optimizer will merge the view logic into the query, then make an execution plan for the whole thing (unless it's an indexed view). It is not restricted to "first process the view." From BOL:

    "When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view, and resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

    For example, consider the following view:

    USE Northwind
    GO
    CREATE VIEW EmployeeName AS
    SELECT EmployeeID, LastName, FirstName
    FROM Northwind.dbo.Employees
    GO

    Given this view, both of these SQL statements perform the same operations on the base tables and produce the same results:

    /* SELECT referencing the EmployeeName view. */
    SELECT LastName AS EmployeeLastName,
    OrderID, OrderDate
    FROM Northwind.db:confused:rders AS Ord
    JOIN Northwind.dbo.EmployeeName as EmpN
    ON (Ord.EmployeeID = EmpN.EmployeeID)
    WHERE OrderDate > '31 May, 1996'

    /* SELECT referencing the Employees table directly. */
    SELECT LastName AS EmployeeLastName,
    OrderID, OrderDate
    FROM Northwind.db:confused:rders AS Ord
    JOIN Northwind.dbo.Employees as Emp
    ON (Ord.EmployeeID = Emp.EmployeeID)
    WHERE OrderDate > '31 May, 1996'

    The SQL Query Analyzer showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements."


  7. joechang New Member

    i have noticed a difference in the way S2K handles views versus the full SQL
    also had problems with UNION

    i think you should bug MS about this, if you must use the view

Share This Page