View performance vs select statement

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

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

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

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.dbrders 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.dbrders 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."

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.dbrders 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.dbrders 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."

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.dbrders 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.dbrders 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."

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