sp_ExecuteSql Performance without Parameters

• ccundy

  SSCommitted

  Points: 1913

  More actions

  January 15, 2004 at 12:42 pm

  #106841

  Can anyone tell me why this first stored procedure is about 5 time slower than the second one? I would think that passing parameters with sp_ExecuteSQL would be faster, I guess not in this case. The only difference is the fast one I concatenate the Dates and the slow one I pass in as procedures. The changes are marked in Green.

  Any insight anyone?

  These procedures are functionally equivalent.

  The slow one

  CREATE PROCEDURE dbo.esi_PH_OffPeakAvgPerMonth

  @BeginDate datetime,

  @EndDate datetime,

  @Locations nvarchar(1000)

  AS

  Declare @sSQL as nvarchar(4000)

  CREATE PROCEDURE dbo.esi_PH_OffPeakAvgPerMonth

  @BeginDate datetime,

  @EndDate datetime,

  @Locations nvarchar(1000)

  AS

  Declare @sSQL as nvarchar(4000)

  Set @sSQL = N'Select GridPoint, DatePart(yy, DateTime) As Year, DatePart(mm, DateTime) As Month, DateName(mm, DateTime) As MonthName, Avg(Price) As Price

  From esi_PriceHistory eph Join esi_PriceHistoryLocations ephl On eph.LocationID = ephl.LocationID

  Where ((DateName(dw, DateTime) In (''Saturday'', ''Sunday''))

  Or ((DatePart(hh, DateTime) In (0, 1, 2, 3, 4, 5, 6, 23)) And DateName(dw, DateTime) In (''Monday'', ''Tuesday'', ''Wednesday'', ''Thursday'', ''Friday'')))

  And DateTime Between @BeginDate And @EndDate

  And eph.LocationID In (' + @Locations + ')

  Group By GridPoint, DatePart(yy, DateTime), DatePart(mm, DateTime), DateName(mm, DateTime)

  Order By GridPoint, DatePart(yy, DateTime), DatePart(mm, DateTime)'

  Exec sp_Executesql @sSQL, @BeginDate datetime, @EndDate datetime', @BeginDate, @EndDate

  GO

  The fast one

  CREATE PROCEDURE dbo.esi_PH_OffPeakAvgPerMonth

  @BeginDate datetime,

  @EndDate datetime,

  @Locations nvarchar(1000)

  AS

  Declare @sSQL as nvarchar(4000)

  Set @sSQL = N'Select GridPoint, DatePart(yy, DateTime) As Year, DatePart(mm, DateTime) As Month, DateName(mm, DateTime) As MonthName, Avg(Price) As Price

  From esi_PriceHistory eph Join esi_PriceHistoryLocations ephl On eph.LocationID = ephl.LocationID

  Where ((DateName(dw, DateTime) In (''Saturday'', ''Sunday''))

  Or ((DatePart(hh, DateTime) In (0, 1, 2, 3, 4, 5, 6, 23)) And DateName(dw, DateTime) In (''Monday'', ''Tuesday'', ''Wednesday'', ''Thursday'', ''Friday'')))

  And DateTime Between ' + quotename(@BeginDate, '''') + ' And ' + quotename(@EndDate, '''') + '

  And eph.LocationID In (' + @Locations + ')

  Group By GridPoint, DatePart(yy, DateTime), DatePart(mm, DateTime), DateName(mm, DateTime)

  Order By GridPoint, DatePart(yy, DateTime), DatePart(mm, DateTime)'

  Exec sp_Executesql @sSQL

  GO

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 15, 2004 at 1:12 pm

  #490280

  Let's continue on this thread

  http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=95483

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]