March 11, 2008 at 11:18 am
I have read through the BOL on cached execution plans, and how they differ between a saved view, and a stored proc. The value of the proc is obvious, when you are using statements which need parameters.
However, on a few tests on my own, I see no difference in execution time between a view and a stored proc, if it is the same statement executed, that is for example, a select statement which has no parameters. In my test case, I selected data from a table by its transaction date.
The execution of the view vs stored proc on the first run were definately longer, as SQL was compiling an exection plan, however subsequent runs returned with the same performance. This makes sense as the BOL state view execution plans are cached.
So the question arrises, if one has a select statement, which does not have parameters, say a report listing all sales people or whatnot. Is there still a performance benefit in a stored proc over a view?
I was testing on a table of fair size (2 million recs), and returning 100,000 records. I would tend to think this would be a large enough volume to show even small differences in execution time.
*Note: I do know that a select statement with a where clause including a parameter will reuse the execution plan in a stored proc, while dynamic, or view SQL will not. In this case I am looking at specifically SQL statements which are static, and do not have parameters.
March 12, 2008 at 2:00 am
For all queries SQL executes, the plans are cached and reused. Adhoc or procedures.
Views by themselves do not have execution plans. When a view is queried, SQL expannds the view definition into the query, then compiles and caches that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 8:30 am
Ok, so when executed a view gains a cached execution plan, which is reused, similar to a stored proc. So is there a particular advantage to using a stored proc, over a view if the query itself has no parameters?
The reading I have done so far seems to leave the two options quite similar. I would hazzard a guess there is an advantage in the stored proc over a view, as its the option most commonly suggested.
March 13, 2008 at 12:41 am
David Lester (3/12/2008)
Ok, so when executed a view gains a cached execution plan, which is reused, similar to a stored proc.
Any query that you run, whether you select from a table or from a view gets a cached execution plan (refered to as an ad-hoc plan) which can potentially be reused.
So is there a particular advantage to using a stored proc, over a view if the query itself has no parameters?
Better plan reuse. Adhoc SQL, unless it matched right down to the white space and the data types of the parameters will not reuse a plan, but will compile a new one.
The following two queries will be considered different, and will get different exec plans.
SELECT Col1, Col2, Col3 from MyTable
SELECT Col1, Col2, Col3 from MyTable
The following two will also get different execution plans (the data type of the constant is different)
SELECT Col1 FROM MyTable WHERE Col3 = 5 -- tinyint
SELECT Col1 FROM MyTable WHERE Col3 = 1025 -- smallint
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2008 at 8:09 am
Thanks Gail, I believe I follow it well enough now.
Of course, I will not know if its enough when I encounter a problem. Ahh, the fun of computers, never knowing how much you don't know until you really need to know it.
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply