存储过程在第一次运行时执行缓慢 [英] Stored procedure executes slowly on first run

问题描述

已经创建了一个用于监控网站的存储过程.

Have created a stored procedure which is utilised for monitoring purposes of a website.

在第一次运行时，该过程需要一分钟多的时间来执行，如果在此之后不久运行，则只需几秒钟即可运行.问题是脚本被安排在十分钟的间隔内运行，每次运行都要超过一分钟，这太长了.

On first run the procedure takes over a minute to execute and if run shortly after this it takes only a few seconds to run. The problem is that the script is scheduled to run at ten minute intervals and each time it runs, it takes over a minute which is too long.

有没有人知道我们如何提高这个查询的性能?我知道有一个原因它第一次运行缓慢，然后在任何后续运行都很快，但一直无法找到答案.

Is anyone aware of how we can improve the performance of this query? I know there's a reason it runs slowly the first time and then quickly for any subsequent but have been unable to find an answer.

这是代码，提前致谢:)

Here's the code, thanks in advance :)

SET NOCOUNT ON
SET DATEFORMAT ymd

declare @start datetime
declare @end datetime
set @start = DATEADD(dd,-1,GETDATE())
set @end = GETDATE()

declare @errorToday int
declare @unconfirmedToday int

set @unconfirmedToday = 
(
 SELECT COUNT([DateCreated])
 FROM GenericLeadLogs WITH(NOLOCK)
 WHERE DestinationConfirmation IS NULL 
 AND [DateCreated] BETWEEN @start AND @end
)

SET @errorToday = 
(
 SELECT COUNT([DateCreated])
 FROM GenericLeadLogs WITH(NOLOCK)
 WHERE Severity = 'Error' 
 AND [DateCreated] BETWEEN @start AND @end
)

CREATE TABLE #GenericLeadStats 
(
 UnconfirmedToday int null, 
 ErrorToday int null
)

INSERT INTO #GenericLeadStats (UnconfirmedToday, ErrorToday)
values(@unconfirmedToday, @errorToday)

SELECT * FROM #GenericLeadStats
DROP TABLE #GenericLeadStats 

推荐答案

我将存储过程重写为:

SET NOCOUNT ON

SELECT SUM(CASE WHEN DestinationConfirmation IS NULL THEN 1 ELSE 0 END) AS unconfirmedToday,
       SUM(CASE WHEN Severity = 'Error' THEN 1 ELSE 0 END) AS errorToday
  INTO #GenericLeadStats
  FROM GenericLeadLogs WITH(NOLOCK) 
 WHERE [DateCreated] BETWEEN DATEADD(dd,-1,GETDATE()) AND GETDATE()

SELECT * FROM #GenericLeadStats

DROP TABLE #GenericLeadStats 

在 SQL Server 中，SELECT INTO 子句创建了一个尚不存在的表.我要离开它，但根据所提供的内容，它毫无用处.

In SQL Server, the SELECT INTO clause creates a table that doesn't already exist. I'm leaving it, but it serves no purpose based on what's provided.

这篇关于存储过程在第一次运行时执行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！