Cached Query plans?

Question

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.

Answer 1

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.