Very Computer

Hi all.
I've created a temp table in a long stored proc, and its running slow.  When
I check the plan, I see that its not using the index I created on the temp
table.  When I try to force it to use the index, it tells me that the index
is not found.  Is there anything tricky I need to do to create a valid index
on a temp table and force the optimizer to choose it?

Thanx in advance,
Eric

Because temp table does not exist when procedure is
compiled, optimizer will assume something like 100 records
(or 100 pages??) for size and map its strategies
accordingly.  To get around this, after creating temp table,
creating its index, and populating table inside procedure,
use an Exec statement to run the "slow" statement.  The
Exec command is like an in-line procedure that is compiled
on-the-spot AFTER the temp table and index exist, and this
should allow use of the index.  E.g.,

Create Table #Temp (...)
Create Unique Clustered Index Idx On #Temp (Pk)
Insert #Temp Select ...
...
Exec ("Select a.* From Main a Join #Temp b (Index=1) On a.Pk = b.Pk")

More that one statement can be stuffed inside the Exec, including Declares,
etc.