2

I ran a query on about 1,300,000 records in one table. It takes certain records that meet some WHERE conditions and inserts them into another table. It first clears out the target table entirely.

The time taken to complete the query gets drastically better with each Execute:

1st: 5 minutes, 3 seconds
2nd: 2 minutes, 43 seconds
3rd: 12 seconds
4th: 3 seconds

I'm not doing anything other than just hitting Execute. My query looks like this (somewhat abbreviated for length purposes):

DELETE FROM dbo.ConsolidatedLogs --clear target table

DECLARE @ClientID int

DECLARE c CURSOR FOR SELECT ClientID FROM dbo.Clients
OPEN c

FETCH NEXT FROM c INTO @ClientID --foreach LogID
WHILE @@FETCH_STATUS = 0
BEGIN

   INSERT INTO dbo.ConsolidatedLogs
        (col1, col2)
        SELECT col1, col2
        FROM dbo.CompleteLogsRaw
        WHERE col3 = true AND
              ClientID = @ClientID

   FETCH NEXT FROM c INTO @ClientID

END
CLOSE c
DEALLOCATE c

How/why does this happen? What is SQL Server doing exactly to make this possible?

This query is going to be run as an SQL Server Agent job, once every 3 hours. Will it take the full 5 minutes every time, or will it be shorter because the job is only running this one query, even though it's got a 3 hour delay?

CptSupermrkt
  • 6,844
  • 12
  • 56
  • 87

2 Answers2

8

If identical queries get faster with each run, there is an outstanding chance that things are being cached. So, where can things be cached?

  • SQL Server Query Plan Cache
  • SQL Server's Query Cache
  • Operating System IO Buffers
  • Hard Disk Controller Cache
  • Hard Disk On-Disk Cache

You can clear the SQL Server Query Cache between runs to see what the impact of that cache is

How can I clear the SQL Server query cache?

SQL Server will use whatever RAM is dedicated to it to keep things that it accesses frequently in RAM rather than on disk. The more you run the same query, the more the data that would normally be found on disk is likely to reside in RAM instead.

The OS level and hardware-level caches are easiest to reset by performing a reboot if you wish to see whether they are contributing to the improving results.

If you publish the query plan that SQL Server is using for each execution of your query, a more detailed diagnostics would be possible.

Community
  • 1
  • 1
Eric J.
  • 147,927
  • 63
  • 340
  • 553
1

When Sql Server executes a query in Enterprise Manager, it creates an "execution" or "query plan" after the first execution, and caches that plan. A "query plan", in a nutshell, describes how SQL Server will attack the tables, fields, indexes, and data necessary to satisfy the result. Each time you re-run it, that query is fetched from the plan cache, and the "heavy lifting" that the query preprocessor would ordinarily have to do is omitted. That allows the query to be performed more rapidly on second and subsequent executions.

Mind you, that's an oversimplification of a much more detailed (and, thus, inherently cooler) process, but that's Query Plan 101 :)

David W
  • 10,062
  • 34
  • 60