January 15, 2004 at 10:03 am
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
January 15, 2004 at 1:04 pm
Did you run both procedures under same conditions. I mean did you use
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE.
It might be that the effect you're observing is due to caching
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 16, 2004 at 4:50 am
May be you need to check execution plans. I suppose that for fast example it was build better execution plan because SQL Server has real values of parameters during building of query execution plan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply