Can't reference index on temporary table

Can't reference index on temporary table

Post by Eric Messenge » Thu, 02 Apr 1998 04:00:00



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

 
 
 

Can't reference index on temporary table

Post by CRH » Thu, 02 Apr 1998 04:00:00


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.


>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


 
 
 

1. Temporary tables VS 'temp-tables'

Generally speaking, what's fastest:
    * Creating a #tempTable, writing to it, selecting it, inserting the
result in a new #tempTable, selecting again and dropping both.
    * Inserting into a table, with a one-column clustered index, selecting
from the same table, deleting the inserted rows. (the rows are recognised

(methods for sharing data between stored proc's, by Erland Sommarskog.

I guess with a small amount of users the 2'nd alternative would be no doubt
fastest, but how about when the users increase? Of course the tablecreation
takes time, but could the tables be created in RAM-memory? Then could
possibly scale better than a scan in a table?
I'm just guessing...

What's the facts?

Thanks!
/Carl

2. QEP, another question

3. Stored procedure -- referencing temporary tables in

4. Filemaker on Unix/Linux

5. Error 208 On Saving Stored Procedure that Creates and References Temporary Tables

6. DB-Lib programming / Sample

7. Cannot reference temporary table in nested SP

8. Date validate question.

9. if you will promise Allahdad's swamp against cans, it will angrily depart the unit

10. cognos transformer (tr1901) can't read bitmap index from temporary file

11. Creating Referencing Column Doesn't Create Index?

12. Using Index on Temporary Table

13. Please help: Creating an index on a temporary table