Hello all,

I am seeking an expert explanation as to when i should use a View, and when should i use a stored procedure.

Lets take for instance i have an intense query that has multiple joins, Is it better to put this query in a view or a stored procedure? how does this compare to just embedding the query within the code? I am using asp.net, with vb.net as my language if that matters at all.

Do you have a rule of thumb? like if it's a select statement use a view, else use a stored procedure?

I need optimal perfomance! Thanks all!!

Flyin

I tend to use both. I will create the view, especially if it is a multiple table join. this makes it easier to debug. Once the view is set up, I will create a SP that the ASP code executes against the view using the command object. that way I can put parms on the SP to filter the view as needed. Just make sure of your indexes, so if your selection criteria changes on the SP, it will use the index.
Use the index tuner to help with performance issues.

One other thing, I try to limit the "embedded sql" in code to as near noone as possible. Put everything in the DB as SP's or whatever, and let it use the predetermined execution plan.

my 2 cents :)

thank you very much for you input, i never thought to use both!

Interesting though, i was using sql query analyser and put one of our common COMPLEX queries in both a view and a stored procedure, and when testing it's time of execution using query analyser, the stored procedure performed about 30 % Better.

I was amazed actually.

can you give me a brief code snippet as to how you use a comand object within a stored procedure? thanks a bunch for your reply! More replies are welcome if anyone wants to put in their two cents!

Superficially, one might think that a view and a stored procedure were equivalent to one another. After all, a view returns a resultset, and a stored procedure can also return one.

But that's about the extent of the similarity.

A view is really nothing more than a virtual table. That is, it looks like a table and behaves like one, but it doesn't actually exist in the database (but see indexed views). Thus in effect the table is instantiated when the view is referenced. You can refer to this virtual table (the view) in any query where you could refer to a 'real' table. There are some restrictions on this, especially as regards updating via the view.

A view cannot take parameters. A view cannot be sorted. (Yes, I know about the TOP 100 PERCENT "trick"; but in my opinion, doing that is stupid, and defeats the whole idea of a view as a virtual table - but that can be a topic for another time ;)

A view is a very handy mechanism for providing a layer hiding complexity in the database. A view can be constructed which hides the details of normalization, for example, allowing the data consumer to be unaware of the technical details of the normalizations which may have been applied to the underlying data tables. Thus the consumers can be presented with a, er, view, of the data which hides the complexities of the JOIN operations required of the underlying data tables, instead presenting a "flatter" view of the data which is easier to understand.

I doubt there is any material difference in performance between a stored procedure and a view returning identical resultsets. When a query containing a view is processed, the view definition is incorporated into the query plan and this is how the virtual table is materialized. Thus, explicitly coding the view or referring to it are essentially equivalent, performance-wise. If you have a complex set of JOINs and you use that set often, it may be better to code them as a view, thus insuring that all uses of the data do the JOINs the same way.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff, you said it a whole lot better than I did thanks!

Here is a snippet for Flyin
in java, but basically the same

Instance and open an ADODB connection
Instance an ADODB command (stored procedure)
set the commands connection
set the command parameters
execute the command



<%
    var cn = Server.CreateObject ("adodb.connection");
    cn.open("SQL-INTRANET","user","pwd");
    function renderOrders(cusno){

            if(String(cusno) == ""){
                return "No customer number selected";
            }
            var sp = Server.CreateObject("adodb.command");
                sp.CommandType = adCmdStoredProc;
                sp.CommandText = "USP_GET_OPEN_ORDS_CUST";
                sp.ActiveConnection = cn;
                sp.Parameters.Refresh;
                sp.Parameters.Item("@cusno").Value = cusno;

            var rs = sp.Execute();
            var xslpath = Server.MapPath("xsl/oorders.xsl");
            return renderRS(rs,"","",Array("all"), "Open Orders", "", xslpath);
    }
%>

<%=renderOrders(getPerm("cusno"))%>

This surprises me.

Are you sure you were comparing apples and apples? Because of the caching of execution plans, it can be quite difficult to set up equivalent tests.

You might try executing a query using the view and compare its execution plan to that of your equivalent stored procedure to see if there are any differences. That much of a difference you observed in execution time leads me to believe the plans were not identical, meaning the execution circumstances weren't identical either...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

hello Jeff,

Yes i'm certain it was the same. It was a common query we use and i created a view of it with no where clause, and then a stored procedure which has the same exact query with no where cluase and then called both individually from query analyser and i was seeing a big difference.

Fascinating.

Did you examine the query plans to see if there was any material difference?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff,
I tought that a Stored procedure was copile, kept it's execution plan and therefore it would run faster than a view. (except indexed views)

Views are not really compiled, are they?





Sal

Actually it is queries that are compiled into an execution plan, and then that plan is cached. If another query comes along which can use the same (cached) query plan, and nothing significant has changed since the plan was compiled, the plan will be reused.

This simply avoids the cost of compilation of the query plan. "Compiling" a plan doesn't make the query run faster - every query has to be "compiled" before it can run. It's just that circumstances may allow the compilation effort to be avoided on subsequent executions. Compilation can be a non-trivial process, as the query analyzer which is responsible for creating the plan will try very hard to come up with the most efficient plan possible. Really complicated queries may convince the analyzer that it is worth it to spend more time trying to find the best plan, so compilation time goes up.

It is true that it is more likely that using stored procedures will result in a more efficient use of the cache, but again, all this saves is the compilation time and should have no real effect on the query execution time - that is, the time spent actually doing the query.

I can't explain the OP's observation of a 30% difference in execution time between a view and a stored procedure. Perhaps he ran the view first, filling up available memory with data buffers, then the stored procedure came along and found everything it needed in memory. It's hard to know for sure, but the first thing I would compare is each method's query plan to see if they really were identical executions.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com