CODEWITHSUNDEEP
sql-serversql-server-2008sql-execution-plan
Royi Namir
Royi Namir

Reputation: 148524

Cached Query plans?

Query plans are stored in the plan cache for both views and ordinary SQL

from here

ok.

Once and for all : How does it help me ?

Even if I have the Query plans in the cache for a query : Once I run the query he WILL scan the whole table/s + aggregates+.... .

and if i run it tomorrow - It will scan the whole table/s + aggregates again ....

there's can't be a situation like this : " AH !!! I have Data in cache , so Ill take it from there.......( because maybe the table has changed...)

so , where is the true benefit ?

I seems to be missing something.

thank you.

Upvotes: 0

Views: 487

Answers (4)

AakashM
AakashM

Reputation: 63338

Suppose we have a query such as

SELECT *
FROM 
    A 
    INNER JOIN B ON -- omitted
    INNER JOIN C ON -- omitted
    -- omitted
    INNER JOIN Q ON -- omitted

with however many tables that is. Obviously, the order these joins are performed in will affect performance. Also, deciding the best order, given the table statistics, also takes an amount of time.

By caching the query plan, we can pay the cost of deciding the best order just once - every subsequent time the query is run, we already know to first take K, join it to E, then to H, and so on.

Of course, this means that a significant change in the data statistics invalidates our plan, but caching anything always involves a trade-off.

A resource you may find useful for learning more about the hows and whys of query planning is SQL Coach - start with THE Analogy.

Upvotes: 3

Diego
Diego

Reputation: 36146

When you submit a query to SQL, it goes through some steps to display the result. The main ones are parsing, algebrizer and query optimizer.

The query optimizer is the responsible to build an execution plan, or select one from the cache and as I understand the process of building a plan is very expensive, so its better if you can reuse one.

The mains point is that the exec plan doesn't contain the data itself, only a way of retrieving it from the BD. So once the plan is "defined", it gets passed to the storage engine and used to retrieve the data.

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41539

Put simply, an execution plan is an explanation of how the query will be done, not the actual data involved in the query (so an execution plan can be applied over and over again as you re-run a query).

An analogy would be to say that an execution plan is similar to a recipe - it is the method of getting the data/making the meal, not the data/meal itself.

The improvement is that it takes time for the DB engine to work out the execution plan for a query, so if its cached you don't need that overhead next time you run the same query.

Upvotes: 1

anon
anon

Reputation:

The answer is that the query plan is cached to prevent the cost of compiling the query plan every time. The second time you run the query (or another that can use the same plan) it doesn't have to pay to run the compilation process all over again, it just pulls the plan from the cache.

Upvotes: 2

Related Questions