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 [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 [MaxLOD])) AND (@bounds.STIntersects([Location]) = 1) END END
The 'POI' table has an index on MinLOD, MaxLOD, and a spatial index on Location.
Ah, can it be the query plan sucks?
SP's get compiled / query lpan deterined on FIRST USE - depending on parameters. So, the parameters of the first call (when no lpan is present) determine the query plan. At one piont i gets dropped from cache, new plan generated.
Next time it runs slow, possibly make a call using query analyzer and get the selected plan - and check how it looks.
if it is this - put in an opton to recompile the SP on every call (with recompile).
parameter sniffing google it. try this, which will "remap" the input parameters to local variables to prevent SQL Server from trying to guess the query plan based on parameters:
ALTER PROCEDURE [dbo].[spGetPOIs]
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float,
@minLOD tinyint,
@maxLOD tinyint,
@exact bit
AS
BEGIN
DECLARE @X_lat1 float,
@X_lon1 float,
@X_lat2 float,
@X_lon2 float,
@X_minLOD tinyint,
@X_maxLOD tinyint,
@X_exact bit
-- Create the query rectangle as a polygon
DECLARE @bounds geography;
SET @bounds = dbo.fnGetRectangleGeographyFromLatLons(@X_lat1, @X_lon1, @lX_at2, @X_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 ((@X_maxLOD [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 ((@X_maxLOD [MaxLOD])) AND
(@bounds.STIntersects([Location]) = 1)
END
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With