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

  • mysql_real_escape_string alternative for SQL Server
  • What SQL query would give me the percentage(%) of the houses sold?
  • Sql - Bulk crypto hash generation
  • How to create multiple rows from a single row
  • sql server NULL case handing
  • SQL Filter criteria in join criteria or where clause which is more efficient
  • 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!

  • SQL query on multiple databases
  • Calculate SUM by date with week and month trick SQL
  • “Incorrect syntax near 'OFFSET'” modift sql comm 2012 to 2008
  • Datetime equation returns no value. sql server 2008
  • Get sum only if no other related entries in a table
  • Finding a Primary Key Constraint on the fly in SQL Server 2005
  • 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.

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