Why does the first execution of the query take twice as long?

I am running a couple of queries with relatively huge result sets (300K rows) and inserting them into tables in a chained fashion :

I bulk insert data into Table0, then read data from Table0 and bulk insert into Table1, read from Table1 insert into Table2 etc. For reading the data, I am using ADO.NET command which fires an exec_sql stored procedure in the background.

I have experienced an interesting phenomenon. When I recreate the d开发者_如何学运维atabase and run the whole process on a couple of tables, it takes approximatelly 20 seconds for each. However, on every subsequent execution of the process the queries only take 10 seconds to execute. Before inserting the data the tables are always truncated so the data amount doesn't grow.

At first I thought this has something to do with cached execution plans, so I cleared the cache using DBCC FREEPROCCACHE after the 2nd run and the 3rd run was still 10 seconds.

Are there any other things that happen when the query is executed first time and can they be figured out faster (i.e. without actually executing the query) ?

I'd assume that SQL Server has a caches for all kinds of stuff including tables and indexes, so on the second execution it probably has lots of the data needed already in memory.

The first time you execute a query, it is being parsed, compiled and optimized. The next time the query is executed, the DBMS takes the internal compiled and optimized version and executes it. That is what causes the difference in execution times.

For more information about this process, please read this article.