Re: Database Engine Tuning Advisor suggestion to replace syntax.

Erland,

Actually, what I said was "IF this code is running in a stored procedure",
not "BECAUSE this code is running in a
stored procedure". (Then I recommended that it should be in a stored
procedure.)

However, I appreciate your comments on the reuse. What you said was what I
first planned to say, but then I read the Books Online which implies that
the batch is indeed reused, so I held back from saying that.

http://msdn.microsoft.com/en-us/library/ms188001.aspx "Being able to
substitute parameters in sp_executesql offers the following ... the query
optimizer will probably match the Transact-SQL statement in the second
execution with the execution plan generated for the first execution.
Therefore, SQL Server does not have to compile the second statement."

If that is true, Mike's code would benefit from reuse, but only if he fully
specified the table name. (Because the Books Online also comment "If object
names in the statement string are not fully qualified, the execution plan is
not reused.")

So, are the Books Online comments incorrect? Or did I just misunderstand
them?

RLF


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9B09349D5FDDYazorman@xxxxxxxxxxxx
Mike (mssql@xxxxxxxxxxxxx) writes:
SQL Server 2005 Database Engine Tuning Advisor informs me that I should
replace query 1 below with the syntax in query 2 below. I?m just not
seeing the reason since both the ?SELECT? statements optimization plans
are stored for reuse. Am I missing something?

Mike.

Query 1
EXEC sp_executesql N'SELECT FirstName, LastName, Company, StreetAddress,
City, State, ZIP5, ZIP4
FROM Customers
WHERE City = @P1
AND State = @P2',
N'@P1 char(32), @P2 varchar(32)', 'Poedunk', 'Iowa'

Query 2
DECLARE @P1 varchar(32)
DECLARE @P2 varchar(32)
SET @P1 = 'Poedunk'
SET @P2 = 'Iowa'
SELECT FirstName, LastName, Company, StreetAddress, City, State, ZIP5,
ZIP4
FROM Customers
WHERE City = @P1
AND State = @P2

Russell seemed to assumed that query 2 is a stored procedure. I may be
missing something, but I cannot see any SP.

And as a loose query batch, it's a poor choice. If you change the
parameter values, it will be a new query text, and there will be no
cache it. On top of that, since SQL Server does not know the parameter
values, it will not "sniff" the parameters on the first invocation but
make some standard assumption.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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



.

Relevant Pages

  • Re: Whats the deal with PAGEIOLATCH_SH?
    ... Does SQL Server have trouble when a single table is this size? ... About 20 minutes into the query everything goes bad. ... >> go over some threshold, which seems to vary, the disk queue length ... >> I'll work on getting the real execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Server - Filter
    ... Happy to use a stored procedure but prefer to use a View that can be linked ... >> the sql server tables from access you are pretty much defeating the idea ... > many fewer bugs if they'd just used Access queries for the most part. ... > to query data from the back-end. ...
    (comp.databases.ms-access)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... query in my query 2 example. ... Tibor Karaszi, SQL Server MVP ... Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this ... statement in the second execution with the execution plan generated for the first execution. ...
    (microsoft.public.sqlserver.tools)
  • Re: ADP Help
    ... And the stored procedure would be something like: ... > this, if all the SQL statements are on the SQL Server, how would the SQL ... I use that query as a base ... >>> query for almost all of my reports. ...
    (microsoft.public.access.adp.sqlserver)