第一次运行SQL Server T-SQL查询速度慢，然后快速运行

问题描述：

我有一个非常简单的存储过程;它第一次运行缓慢，如果输入参数相同，则运行速度很快。第一次运行SQL Server T-SQL查询速度慢，然后快速运行

它返回两个表，表头正在迅速恢复，但第二个表，它作为我与表1的结果加入它得到正确的数据变得缓慢

这里是我的存储过程的代码：

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

ALTER PROCEDURE [dbo].[PR_ReadMdgObj] 
     (@objId int, 
     @dtFrom datetime = NULL, 
     @dtTo datetime = NULL) 
AS 
BEGIN 
    SET NOCOUNT ON 

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

    DECLARE @engine_SensorSource_id INT 

    SELECT @engine_SensorSource_id = SensorSourceid 
    FROM dbo.SensorSource 
    WHERE sourcenameid = 1 
     AND objectid = @objId 

    CREATE TABLE #10msgtable 
    (
     rownum int IDENTITY (1, 1) NOT NULL, 
     MessageId bigint NOT NULL, 
     ObjectId int NOT NULL, 
     VectorAngle int NOT NULL, 
     VectorSpeed int NOT NULL, 
     Altitude int NOT NULL, 
     GpsTime datetime NOT NULL, 
     VisibleSatelites int NULL, 
     X float, Y float, 
     engine int, 
     st int 
    ); 

    CREATE CLUSTERED INDEX IDX_C_returnTable10_GpsTime 
    ON #10msgtable (GpsTime); 

    INSERT INTO #10msgtable 
     SELECT 
      [Message].messageid, [Message].objectid, 
      [Message].vectorangle, [Message].vectorspeed, 
      [Message].altitude, [Message].gpstime, 
      [Message].visiblesatelites, 
      [Message].x, [Message].y, 
      0 Engine, 0 as t 
     FROM 
      dbo.[Message] WITH (nolock) 
     WHERE 
      [Message].ObjectId = @objId 
      AND [Message].GpsTime BETWEEN @dtFrom AND @dtTo 
      -- AND m.Valid = 1 
      -- AND m.VectorSpeed < 250 

    DELETE FROM #10msgtable 
    WHERE VectorSpeed = 250; 

    SELECT * 
    FROM #10msgtable 
    ORDER BY GpsTime ASC 

    --- select 2 
    SELECT 
     MessageSensors.MessageId, 
     SensorSource.SourceNameId, 
     MessageSensors.Value 
    FROM 
     dbo.MessageSensors 
    INNER JOIN 
     #10msgtable WITH (nolock) ON MessageSensors.MessageId= #10msgtable.MessageId 
    INNER JOIN 
     dbo.SensorSource WITH (nolock) ON SensorSource.SensorSourceId = MessageSensors.SensorSourceId 
    --where MessageSensors.MessageId in (select MessageId from #10msgtable) 

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
END

这里是我的实际执行计划：

https://gist.github.com/aymanstar/3ed882c6330ee6252751ce9dd2f5beac

能运行得更快是因为查询和执行计划被缓存的原因。这意味着如果参数不改变，查询会更快，第二次运行时它会更快 – luchosrock

我知道，但是，如何优化查询以及为什么它会变慢？它的正常读数没有任何复杂的计算 –

“第一次运行”是什么意思？就像重新启动SQL Server之后一样？首次运行速度通常会很慢，因为所有数据都必须从磁盘读取到内存中。一旦数据被缓存，在后续运行中会更快，除非在此期间再次强制数据从内存中溢出。如果执行计划被缓存，则不太可能引起显着差异;虽然不是不可能，但编译本身不太可能造成显着的，明显的延迟（除非SQL Server此次生成效率更低的计划）。 –

答

有你添加一些不必要的步骤。第一次运行总是很慢（正如你已经知道的那样）。在你的脚本

没有必要添加with(nolock)的原因是由@marc_s
不必要的变量声明（@engine_SensorSource_id）explaned。
如果您在第一次尝试中过滤掉数据，则无需执行delete。

所以修改后的脚本应该是这样的： -

SET ANSI_NULLS ON 
GO 

SET QUOTED_IDENTIFIER ON 
GO 

ALTER PROCEDURE [dbo].[PR_ReadMdgObj] (
    @objId INT 
    ,@dtFrom DATETIME = NULL 
    ,@dtTo DATETIME = NULL 
    ) 
AS 
BEGIN 
    SET NOCOUNT ON 
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

    --DECLARE @engine_SensorSource_id INT 

    --SELECT @engine_SensorSource_id = SensorSourceid 
    --FROM dbo.SensorSource 
    --WHERE sourcenameid = 1 
    -- AND objectid = @objId 

    CREATE TABLE #10msgtable (
     rownum INT IDENTITY(1, 1) NOT NULL 
     ,MessageId BIGINT NOT NULL 
     ,ObjectId INT NOT NULL 
     ,VectorAngle INT NOT NULL 
     ,VectorSpeed INT NOT NULL 
     ,Altitude INT NOT NULL 
     ,GpsTime DATETIME NOT NULL 
     ,VisibleSatelites INT NULL 
     ,X FLOAT 
     ,Y FLOAT 
     ,engine INT 
     ,st INT 
     ); 

    CREATE CLUSTERED INDEX IDX_C_returnTable10_GpsTime ON #10msgtable (
     MessageId 
     ,GpsTime 
     ); 

    INSERT INTO #10msgtable 
    SELECT [Message].messageid 
     ,[Message].objectid 
     ,[Message].vectorangle 
     ,[Message].vectorspeed 
     ,[Message].altitude 
     ,[Message].gpstime 
     ,[Message].visiblesatelites 
     ,[Message].x 
     ,[Message].y 
     ,0 Engine 
     ,0 AS t 
    FROM dbo.[Message] 
    WHERE [Message].ObjectId = @objId 
     AND [Message].GpsTime BETWEEN @dtFrom 
      AND @dtTo 
     AND VectorSpeed <> 250 

    -- AND m.Valid = 1 
    -- AND m.VectorSpeed < 250 
    --DELETE 
    --FROM #10msgtable 
    --WHERE VectorSpeed = 250; 

    SELECT * 
    FROM #10msgtable 
    ORDER BY GpsTime ASC 

    --- select 2 
    SELECT MessageSensors.MessageId 
     ,SensorSource.SourceNameId 
     ,MessageSensors.Value 
    FROM dbo.MessageSensors 
    INNER JOIN #10msgtable ON MessageSensors.MessageId = #10msgtable.MessageId 
    INNER JOIN dbo.SensorSource ON SensorSource.SensorSourceId = MessageSensors.SensorSourceId 

    --where MessageSensors.MessageId in (select MessageId from #10msgtable) 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
END

@ engine_SensorSource_id为下一个建议，因为我将在稍后使用它，因为此列不包含在索引中，并且原始表很大，所以我从返回的结果中筛选，（无锁），我同意没有它的需要，但它不会延迟读取速度？ –

第一次运行SQL Server T-SQL查询速度慢，然后快速运行

相关推荐