SQL Server - 存储过程突然变慢 [英] SQL Server - stored procedure suddenly become slow
问题描述
我写了一个存储过程,昨天通常在一秒钟内完成。今天,大约需要18秒。我昨天遇到了问题,它似乎通过DROPing和重新创建存储过程来解决。今天,这个技巧似乎没有奏效。 :(
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...!
有谁知道问题可能是什么?我已经搜索了答案,但他们经常建议通过查询分析器运行它,但是我没有 - 我现在正在使用SQL Server 2008 Express。
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.
存储过程如下:
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
'POI'表有一个关于MinLOD,MaxLOD和位置空间索引的索引。
The 'POI' table has an index on MinLOD, MaxLOD, and a spatial index on Location.
推荐答案
啊,它可以是查询计划很糟糕?
Ah, can it be the query plan sucks?
SP的编译/查询lpan在FIRST USE上确定 - 取决于参数。因此,第一次调用的参数(当没有lpan时)确定查询计划。在一个piont我从缓存中删除,生成新计划。
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.
如果是这样的话 - 在每次调用时放入一个opton来重新编译SP(重新编译)。
if it is this - put in an opton to recompile the SP on every call (with recompile).
这篇关于SQL Server - 存储过程突然变慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!