Should my view be much slower than my stored proc?
I have a view that I can’t seem to fully tune, so I tried converting it to a proc, moving one of my WHERE conditions that I use when querying into a parameter and using the parameter in my derived tables.
View
CREATE VIEW myView
AS
SELECT i.ItemCode
, s.StoreID
, ISNULL(SUM(s.TotalSales)) AS Sales
FROM Item i
LEFT JOIN Balance b ON i.ItemCode = b.ItemCode
LEFT JOIN (SELECT SUM(Quantity)
, StoreID
, ItemCode
, Date
FROM Sales
GROUP BY StoreID
, ItemCode
, Date) AS s ON i.ItemCode = s.ItemCode
AND s.Data >= COALESCE(b.Date, '01-01-1900)
JOIN
GROUP BY i.ItemCode
, s.StoreID
Stored Proc
CREATE PROCEDURE myProc(@StoreID INT)
AS
SELECT i.ItemCode
, s.StoreID
, ISNULL(SUM(s.TotalSales)) AS Sales
FROM Item i
LEFT JOIN Balance b ON i.ItemCode = b.ItemCode
LEFT JOIN (SELECT SUM(Quantity)
, StoreID
, ItemCode
, Date
FROM Sales
WHERE StoreID = @StoreID
GROUP BY StoreID
, ItemCode
, Date) AS s ON i.ItemCode = s.ItemCode
AND s.Data >= COALESCE(b.Date, '01-01-1900)
JOIN
GROUP BY i.ItemCode
, s.StoreID
EXEC myProc(100) is much faster than SELECT * FROM myView WHERE StoreID = 100.
Should this be the case?
Note: I know this code may not make perfect sense or run — I’ve tried to simplify it by removing some other JOINs. The only substantial difference in the real code is moving the WHERE into the derived table, which I’ve done here.
Shouldn’t the view take my WHERE into account when performing the derived table queries and be just as fast as the proc?
Thanks for any help!
One Solution collect form web for “Should my view be much slower than my stored proc?”
The two queries are quite different. The difference is very subtle, but this is the line:
WHERE StoreID = @StoreID
The view calculates all the data for all the stores. It then filters the results for a particular store.
The stored procedure version is only looking at the particular store; it can even make efficient use of an index on StoreId if there is one.