General discussion


Indexing temp tables in stored procedures.

By Spanr ·
Is there any advantage to creating indexes on temp tables created in a SQL stored procedure?

In theory, creating an index on a table where that data element is used for joining or selection should improve performance. But so far the indexing doesn't seem to make a significant difference. Perhaps my sample data is not large enough for the indexing to come into play.

I would be interested in opinions on this topic.

And to those who post responses, my thanks in advance for making the effort.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Indexing and Temp tables

by j.lupo In reply to Indexing temp tables in s ...

First, are you using PL/SQL stored procedures for ORACLE or some other database. I am most familiar with Oracle and we have started using HINTS in our SQL statements embedded in our SP's.

They take the form of
SELECT /*+Hint*/ column, column
FROM table t1, table t2
WHERE t1.col = t2.col

At least something like that. This does improve performance WHEN the temporary table is going to be very large. If the table is not normally large then you don't gain very much from the idea or from having it indexed.

Good Luck

Collapse -

Indexing is not free

by Tony Hopkinson In reply to Indexing and Temp tables

It takes time to build one after data is entered and time to keep one up to date if it's defined first.
Unless there was a very large number of rows or perhaps a smaller amount used repeatedly, I doubt it would be worth the effort performance wise.

Collapse -


by Tony Hopkinson In reply to Indexing is not free

How that that get here ?
Suspect dumb user.

Collapse -

Indexing Cost

by Spanr In reply to Oops

Thanks for the repsonses.

I am using MS SQL Server, just to clarify - sorry should have said so in my initial post.

The point about the "cost" was what was bothering me. I figured that there had to be some point when the cost of determining the indexes would be out-weighed by the quantity of data, but I guess my sample data is simply not large enough for that to kick in.

If anyone reading this has any further ideas, I would be pleased to see them.

Thanks again.

Collapse -

Don't know what more can be said

by Tony Hopkinson In reply to Indexing Cost

Aside from from building test scenarios where you look at various volumes of temporary data vs indexed and unindexed solutions. Even that is going to be dependant on varios extrenal factors. Basically if it's still taking too long and indexing doesn't sort it out, you are going to have to do a bit of lateral thinking a come up with a different solution.
Using a cursor and building a one pass solution, for instance. Or using a trigger to build up the data on a record by record basis and then just selecting from that.

Collapse -

Try using . . .

by j.lupo In reply to Indexing Cost

I know you are not using Oracle or any of its tools, but see if SQL Server has an Explain Plan tool. This can help you evaluate what the cost of an SQL will be on tables in the query. It will also help you know if you are gaining anything by indexing the table.

Collapse -

Index temp tables

by MirrorMirror In reply to Indexing temp tables in s ...

You can definitely put indexes on temp tables. Some reasons you may not be seeing any performance gain is that the data that you are indexing may not be selective enough to allow and index seek over an index scan. Or, if the query you are using to get data from the temp table is not filtered enough, the optimizer may be doing an index scan because of the sheer nature of the query.

It kinda depends on what is in the temp table and if you are creating the index before or after data is loaded into the temp table.

Related Discussions

Related Forums