与下次运行相比，sql 查询需要很长时间 [英] sql query takes much long time compared to next run

问题描述

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

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.

我的程序正在处理具有 6 个非聚集索引和 1 个聚集索引的单个表，唯一 id 列是具有 1,218,833 行的 uniqueidentifier 数据类型.

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.

你能指导我问题/可能的性能改进在哪里吗?

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

提前致谢.

这是程序.

 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

推荐答案

第一次运行查询时，数据不在数据缓存中，因此必须从磁盘中检索.此外，它必须准备一个执行计划.以后运行查询时，数据将在缓存中，因此不必去磁盘读取它.也可以复用原来生成的执行计划.这意味着执行时间可以更快，以及为什么理想情况是拥有大量 RAM 以便能够在内存中缓存尽可能多的数据(数据缓存提供最大的性能改进).

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 的压力有多大.如果 SQL Server 需要释放一些内容，它将从缓存中删除一些内容.最常使用/具有最高值的数据/执行计划将保持缓存更长时间.

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.

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

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

这篇关于与下次运行相比，sql 查询需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！