dbTalk Databases Forums  

Round 2:Database Engine Tuning Advisor suggestion to replace synta

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.tools Round 2:Database Engine Tuning Advisor suggestion to replace synta

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools


Discuss Round 2:Database Engine Tuning Advisor suggestion to replace synta in the microsoft.public.sqlserver.tools forum.



Reply
Page 1 of 4 1 2 3 4 >
 
Thread Tools Display Modes
  #1  
Old   
Mike
 
Posts: n/a

Default Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 03:37 PM





This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #2  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM





sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #3  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #4  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #5  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #6  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #7  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #8  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #9  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
  #10  
Old   
Andrew J. Kelly
 
Posts: n/a

Default Re: Round 2:Database Engine Tuning Advisor suggestion to replace synta - 10-01-2008 , 04:23 PM


sp_executesql is the preferred method but I wonder about the datatypes that
you have declared. In example 1 there is a CHAR(32) and a VARCHAR(32) where
as in example 2 they are both VARCHAR's. Make sure to use the exact
datatype as the underlying columns in order to ensure you can use the
indexes properly. If you have them declared properly I would go with #1.
Always take the advise from the DTA with a grain of salt. Use your actual
knowledge and experience to make the final decision as to which way to go.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mike" <realtor (AT) nospam (DOT) nospam> wrote

Quote:
This is a repost in hope Microsoft will notice the alias and maybe answer
this since others have mixed opinions.

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

Mike.


Reply With Quote
Reply
Page 1 of 4 1 2 3 4 >

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top