存储过程在第一次运行时执行缓慢

本文介绍了存储过程在第一次运行时执行缓慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.