与下次运行相比，sql查询需要很长时间

I'm running a procedure which takes around 1 minute for the first time execution but for the next time it reduces to around 9-10 seconds. And after some time again it takes around 1 minute.

我正在运行一个程序，第一次执行大约需要1分钟，但下次会减少到大约9-10秒。经过一段时间后，大约需要1分钟。

My procedure is working with single table which is having 6 non clustered and 1 clustered indexes and unique id column is uniqueidentifier data type with 1,218,833 rows.

我的程序是使用单个表，它有6个非聚簇索引和1个聚簇索引，唯一的id列是具有1,218,833行的uniqueidentifier数据类型。

Can you guide me where is the problem/possible performance improvement is?

你能指导我问题在哪里/可能的性能提升吗？

Thanks in advance.

提前致谢。

Here is the procedure.

这是程序。

 PROCEDURE [dbo].[Proc] (
        @HLevel NVARCHAR(100),
        @HLevelValue INT,
        @Date DATE,
        @Numbers NVARCHAR(MAX)=NULL
    )
    AS 

    declare   @LoopCount INT ,@DateLastYear DATE 


    DECLARE @Table1 TABLE ( list of columns )
    DECLARE @Table2 TABLE ( list of columns )

    -- LOOP FOR 12 MONTH DATA
    SET @LoopCount=12
    WHILE(@LoopCount>0)
        BEGIN
            SET @LoopCount= @LoopCount -1 

            -- LAST YEAR DATA
            DECLARE @LastDate DATE;
            SET @LastDate=DATEADD(D,-1, DATEADD(yy,-1, DATEADD(D,1,@Date)))




                    INSERT INTO @Table1  
                    SELECT list of columns 
                        FROM Table3 WHERE  Date = @Date   
                    AND 
                    CASE 
                        WHEN @HLevel='crieteria1' THEN col1
                        WHEN @HLevel='crieteria2' THEN col2
                        WHEN @HLevel='crieteria3' THEN col3
                    END =@HLevelValue



                    INSERT INTO @Table2 
                        SELECT list of columns 
                        FROM table4
                        WHERE  Date= @LastDate 
                         AND ( @Numbers IS NULL OR columnNumber IN ( SELECT *  FROM dbo.ConvertNumbersToTable(@Numbers)))

INSERT INTO @Table1
        SELECT list of columns 
            FROM @Table2 Prf2 WHERE Prf2.col1 IN (SELECT col2  FROM @Table1) AND Year(Date) = Year(@Date)



   SET @Date = DATEADD(D,-1,DATEADD(m,-1, DATEADD(D,1,@Date)));

 END 

  SELECT list of columns FROM @Table1

2 个解决方案

#1

The first time the query runs, the data is not in the data cache and so has to be retrieved from disk. Also, it has to prepare an execution plan. Subsequent times you run the query, the data will be in the cache and so it will not have to go to disk to read it. It can also reuse the execution plan generated originally. This means execution time can be much quicker and why an ideal situation is to have large amounts of RAM in order to be able to cache as much data in memory as possible (it's the data cache that offers the biggest performance improvements).

第一次运行查询时，数据不在数据高速缓存中，因此必须从磁盘检索。此外，它必须准备执行计划。在运行查询的后续时间，数据将在缓存中，因此不必转到磁盘来读取它。它还可以重用最初生成的执行计划。这意味着执行时间可以更快，为什么理想的情况是拥有大量的RAM，以便能够在内存中尽可能多地缓存数据（这是提供最大性能改进的数据缓存）。

If execution times subsequently increase again, it's possible that the data is being removed from the cache (and execution plans can be removed from the cache too) - depends on how much pressure there is for RAM. If SQL Server needs to free some up, it will remove stuff from the cache. Data/execution plans that are used most often/have the highest value will remain cached for longer.

如果执行时间随后再次增加，则可能是从缓存中删除数据（并且执行计划也可以从缓存中删除） - 取决于RAM的压力。如果SQL Server需要释放一些，它将从缓存中删除东西。最常使用/具有最高值的数据/执行计划将保持缓存更长时间。

There are of course other things that could be a factor such as what load is on the server at the time, whether your query is being blocked by other processes etc

当然还有其他因素可能是一个因素，例如当时服务器上的负载，您的查询是否被其他进程阻止等

#2

It seems that stored procedure is recompiling repeatedly after some time. To reduce the recompilation please check this article:

似乎存储过程在一段时间后重复重新编译。要减少重新编译，请查看以下文章：

http://blog.sqlauthority.com/2010/02/18/sql-server-plan-recompilation-and-reduce-recompilation-performance-tuning/

#1