已经创建了一个用于监控网站的存储过程.
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.
更多推荐
存储过程在第一次运行时执行缓慢
发布评论