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屋！