SQL Server - stored procedure suddenly become slow
- by Barguast
I have written a stored procedure that, yesterday, typically completed in under a second. Today, it takes about 18 seconds. I ran into the problem yesterday as well, and it seemed to be solved by DROPing and re-CREATEing the stored procedure. Today, that trick doesn't appear to be working. :(
Interestingly, if I copy the body of the stored procedure and execute it as a straightforward query it completes quickly. It seems to be the fact that it's a stored procedure that's slowing it down...!
Does anyone know what the problem might be? I've searched for answers, but often they recommend running it through Query Analyser, but I don't have have it - I'm using SQL Server 2008 Express for now.
The stored procedure is as follows;
ALTER PROCEDURE [dbo].[spGetPOIs]
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float,
@minLOD tinyint,
@maxLOD tinyint,
@exact bit
AS
BEGIN
-- Create the query rectangle as a polygon
DECLARE @bounds geography;
SET @bounds = dbo.fnGetRectangleGeographyFromLatLons(@lat1, @lon1, @lat2, @lon2);
-- Perform the selection
if (@exact = 0)
BEGIN
SELECT [ID], [Name], [Type], [Data], [MinLOD], [MaxLOD], [Location].[Lat] AS [Latitude], [Location].[Long] AS [Longitude], [SourceID]
FROM [POIs]
WHERE
NOT ((@maxLOD < [MinLOD]) OR (@minLOD > [MaxLOD])) AND
(@bounds.Filter([Location]) = 1)
END
ELSE
BEGIN
SELECT [ID], [Name], [Type], [Data], [MinLOD], [MaxLOD], [Location].[Lat] AS [Latitude], [Location].[Long] AS [Longitude], [SourceID]
FROM [POIs]
WHERE
NOT ((@maxLOD < [MinLOD]) OR (@minLOD > [MaxLOD])) AND
(@bounds.STIntersects([Location]) = 1)
END
END
The 'POI' table has an index on MinLOD, MaxLOD, and a spatial index on Location.
