March 22, 2006 at 7:51 am
Folks,
I want to use a View or a Stored Procedure to get a derived table which is generated by using SELECT statement(s) along with many joins of other physical tables.
What are the drawbacks of using View or Stored Procedure? I mean comparisons of their performance, occupied physical database spaces, re-compiling needs, maintenance.
Thanks so much.
johnsql
March 22, 2006 at 10:04 am
Views and stored procedures serve different purposes, it is difficult to compare the two.
The primary purposes of views is to simplify data access (by hiding the details of joining several table together, CASE statements, conversions, etc.) and ease security management (you can grant users SELECT access to views that only access a subset of data without granting them access to the underlying tables).
A non-indexed view is just a stored validated query, and takes up the space necessary to store the definition in syscomments, plus entries in syscolumns, security, permission, dependancies, and so on. An indexed view is basically a physical table that is automatically updated by the underlying tables, and takes up the space necessary for data and indexes just as physical tables do.
Views are not compiled, and thus are never recompiled. For maintenance, if the underlying column definitions for a non-indexed view change, (name, data type, columns added to a 'select *', etc.), then you should drop and re-create the view to update the system table entries, because the view will retain the old definitions until you do. Indexed view must be schema-bound to their base tables, so table definition changes against them are not allowed until the indexed view is dropped.
Unlike stored procedures, you can select SELECT, INSERT*, UPDATE*, and DELETE* against views, create indexes on views*, and have triggers on views. (* with some restrictions) If the users will be directly accessing the data using Access or some other querying tool (and you're not allowed to kill them to keep them from doing it), then you must use views or allow table access. Interactive query tools like Access generally bind to tables and views, but not stored procedures. Most reporting tools work with procedures, though.
A SELECT against a view returns a single resultset. You may join views to tables and other views in queries.
If your derived table requires additional logic beyond that which can be included in a single SELECT statement (a SELECT statement with many nested and UNIONed SELECT statements counts as a single SELECT statement here), then you must pursue other means to build your resultset, such as stored procedures, table-valued user defined functions, and client-side code.
A stored procedure is a stored and validated list of actions, which also and takes up the space necessary to store the definition in syscomments, plus entries in syscolumns, security, permission, dependancies, and so on.
That's where the similarities end.
Stored procedures may return multiple resultsets. They may contain multiple logical steps before and after each resultset. Procedures have output paramters as well.
Stored procedures are compiled on first use. At that time, the compiled execution plans take up memory (viewable in the syscacheobjects virtual table) until SQL Server ages out the plan to use the space for other needs. The more complicated a procedure gets, the more space the plan takes.
If a compiled plan is in memory, it will be reused if the procedure is called again. This is usually a good thing; compilation takes server resources. However, this is occasionally a bad thing depending on the indexes of your table and the distribution of data: if a different index better would serve later executions, they will not be used because that decision has already been made and stored in the compiled plan. In this case you would want the procedure to be recompiled every execution, or at least each time it would be wise to re-check the indexes when building the plan.
Stored procedures are automatically recompiled, even when a plan exists, whenever:
- a table accessed by by the procedure is automatically marked for recompilation (meaning every plan that includes the table should be recompiled, not the table itself) due to updated statistics, indexes, etc., or an admin manually set the table for recompilation to force it
- DDL is encountered in the procedure - such as creating a temp table in the procedure
- it is run if it is created with the RECOMPILE option
- there may be more, I'm running off of memory here
On heavy-use database servers, elimination of the extra recompilations on stored procedures - especially the complex ones - saves time and server resources that adds up. Even on mild-use servers, recompiling a single huge proc can slow every call to it. (Hint: break it up into smaller pieces!)
-Eddie
Eddie Wuerch
MCM: SQL
March 23, 2006 at 11:03 am
Eddie Wuerch,
Thanks so much for your time spent for my question. Your words are great and really helpful.
Johnsql
March 23, 2006 at 6:11 pm
Eddie, you forgot to mention that stored procedures may return no resultset.
It may be designed to work out data without any immediate output.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply