Execution plan for view
in [Programming SQL]
|
Prev: SQLCMD NUMERIC FORMAT ERROR
Next: DBMail Problems
From: Gert-Jan Strik on 24 Mar 2010 16:51 od, SQL Server does not create execution plans for views. It only creates execution plans for queries. So the fact that you are using a view, and that that view joins several tables or selfjoins is not relevant. When you use a view in a query, the view is expanded *, and the view's definition is merged with the rest of the query before the execution plan is determined. (* indexed view work differently) So that reduces your question to the questions: 1) will SQL Server create a new execution plan if you add an extra expression in the WHERE clause that uses a parameter/local variable. 2) And will the "earlier" execution plan for the query without the extra expression still be available? Answer to 1: Yes, a new execution plan will always be determined for a new query. Answer to 2: That depends on the way you supply the parameter, and whether auto-parameterization kicks in. As Uri suggested, it is probably easiest to simply test this. -- Gert-Jan "OceanDeep via SQLMonster.com" wrote: > > We are using SQL 2005/2008 std. I have a view that joins and self joins > between two tables with various join criteria and the criteria is static. > Once the view is created, we have a query that will join the view with > another big table and the where clause has two parameters which are based on > the big table. When this query runs with various values in the two > parameters, I believe the execution plan will be cached based on the new > feature, parameter sniffing in SQL 2005/2008. Is this correct? > > Furthermore, say a third parameter is introduced in this query, obviously the > optimizer will need to redo the execution plan. My another question is that > would SQL keeps two execution plans, one for the two parameter one and one > for the three parameter one assuming memory resouce is plenty and these two > queries are run often? > > Keep in mind that the query mentioned above is not a stored procedure. Our > web application calls the query whenever a user uses that page on our web > application. > > od > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1
From: Erland Sommarskog on 25 Mar 2010 19:13
OceanDeep via SQLMonster.com (u46587(a)uwe) writes: > Thank for the reply. My wording may be a bit confusing but as mentioned > in my post, the view is static. Our web application uses that view and > joins another big tables so parameterizing is done in the code in the > web application. When SQL receives the query, the parameter values > (literals) should be in place already as in your first example. This is bad! You should not interpolate parameter values into the query string, but you should use parameterised queries instead. Right now, you get one new cache entry for each new set of parameters you use. There is also the risk for SQL injection. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |