Question about optimizer with (big) temp tables - Microsoft SQL / MS SQL Server

[Eric Mamet]

If I ask query yser to give me an estimated query plan, how does
it work this out since my temp table will only be populated when I
fire the query?
I suppose this "estimated" plan is only worth so much...

Now, what happens at run time, if I create a temp table and populate
it with 1 million rows?

Is the optimiser "aware" of this 1 million rows or does it make its
plan before populating the table?

If I create one or several indexes on the temp table during the
execution, what does the optimiser does with it?


Thanks


Eric

[Jacco Schalkwijk]

You can't work out an estimated execution plan for a query with a temporary
table in it. (unless of course the temp table already has been created
outside that query).

When you run the query the Query Optimizer will generate an execution plan
but as soon as the CREATE TABLE statement is encountered during the
execution it will recompile the execution plan and proceed with the new
execution plan. If you have auto update statistics on on tempdb the
statistics will be update a few times when you insert the 1 million rows and
that will cause recompiles too. So the optimizer is aware of the fact that
there are 1 million rows in the table (give or take 100,000)

Creating an index in itself will _not_ cause a recompilation, but when the
query plan gets recompiled for any other reason the new indexes will be
taken into account. This means that it is not very useful to create the
indexes after you have inserted all the data in the table, unless you know
the query plan is going to be recompiled for some reason after that.

If you create indexes on a temporary table with 1 million rows, avoid
creating a clustered index, unless the data you are going to insert in it is
already in the order of the clustered index. Ordering 1 million rows takes a
heck of a long time. (well, tens of seconds probably)

hth

Jacco


"Eric Mamet" <eric_mamet_testyahoo.co.uk> wrote in message
news:11269dcb.0307010813.79b929fdposting.google.c om...

> If I ask query yser to give me an estimated query plan, how does
> it work this out since my temp table will only be populated when I
> fire the query?
> I suppose this "estimated" plan is only worth so much...
>
> Now, what happens at run time, if I create a temp table and populate
> it with 1 million rows?
>
> Is the optimiser "aware" of this 1 million rows or does it make its
> plan before populating the table?
>
> If I create one or several indexes on the temp table during the
> execution, what does the optimiser does with it?
>
>
> Thanks
>
>
> Eric

[Eric Mamet]

Cool, very interesting.

Thanks a lot: I was about to create my indexes AFTER populating the temp table data!

Can I force a recompilation by forcing an update of statistics on my temp table?
Can we do that?