Stored procedure executes slowly on first run

Question

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([DateCreat…

Accepted Answer

I re-wrote the stored procedure as:

```
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 
```

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.

Stored procedure executes slowly on first run

Solution

Problem