Why SQL query with short date range runs slow, while a long range runs quickly?

Question

Platform: SQL Server 2008 R2

I have a reasonably complex SQL query that queries (2) distinct databases and collects a report based on the date range of the records. Searching through over 3,000,000 rows for a date range of, say, 2 months is nearly instantaneous. However, searching a short date range of, say, 7 days takes nearly two minutes. For the life of me, I cannot understand why this would be. Here's the query below:

;with cte_assets as ( 
 select a.f_locationid, a.f_locationparent, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage 
 from [db_assets].[dbo].[tb_locations] a where f_locationID = '366' UNION ALL 
 select a.f_locationid 
           ,a.f_locationparent 
           ,c.[lev] + 1 
           ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid)) 
    from   cte_assets c 
    join   [db_assets].[dbo].[tb_locations] a 
           on a.f_locationparent = c.f_locationID 
 ),
 cte_a AS 
 ( 
    select f_assetnetbiosname as 'Computer Name' 
    from cte_assets c 
    JOIN [db_assets].[dbo].[tb_assets] ass on ass.f_assetlocation = c.f_locationID 
 ) 
      select apps.f_applicationname, apps.f_applicationID, sum(f_runtime/60) [RunTime] 
 from cte_a c 
 JOIN [db_reports].[dbo].[tb_applicationusage] ss on ss.f_computername = c.[Computer Name] 
 JOIN [db_reports].[dbo].[tb_applications] apps 
 ON ss.f_application = apps.f_applicationID 
 WHERE ss.f_runtime IS NOT NULL AND f_starttime BETWEEN '1/26/2015 10:55:03 AM' AND '2/12/2015 10:55:03 AM'
 group by apps.f_applicationname, ss.f_application, apps.f_applicationID 
 ORDER BY RunTime DESC

The final WHERE clause (3rd-to-last line) is where the date range is specified. The date range shown in the query of BETWEEN '1/26/2015 10:55:03 AM' AND '2/12/2015 10:55:03 AM' works quickly without issues. If we change the query to, for example, BETWEEN '1/27/2015 10:55:03 AM' AND '2/12/2015 10:55:03 AM' (just a day later) it takes over two minutes to run. I have absolutely no idea why a short range would cause the query to run more slowly. Any assistance is appreciated.

Thanks, Beems

Answer 1

I apologize, I should have Googled this problem more thoroughly first. I found the answer on another StackOverflow post regarding the need to update statistics:

SQL query takes longer time when date range is smaller?

Using the MSDN article not linked in that article, but this one here:

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

I ran the following:

USE db_reports;
GO
UPDATE STATISTICS tb_applicationusage;
GO

2 seconds later the update was complete, and my short-term recent queries now run quickly.

Thanks, Beems