CODEWITHSUNDEEP
sqlt-sqlsql-server-2008temp-tables
mg1075
mg1075

Reputation: 18155

Can creating a primary key on a #temp table increase proc speed?

If you create a primary key on a large #temp table, or perhaps a few other indexes, what are the potential speed benefits, and what are the potential slowdowns?

Is it normally a good idea to this with a large #temp table?

I've seen one article referenced in another stackoverflow question, http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables, but it doesn't give much of an explanation.

Upvotes: 1

Views: 1795

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

As a general rule, if you are going to query that table a lot, then create the index. If you are just going to insert or delete records from it, then don't.

Anyway, testing the table with and without indexes and comparing the results after its use would be all you need to answer the question :)

Upvotes: 1

user166390
user166390

Reputation:

Indices (including an implicit index for a primary key) are useful in the same way for a temp table as with a normal table. That is, if they can be used in the query they will be. (In actuality, the query planner may choose to ignore indexes, and indexes over only small scans are largely wasted.)

However, due to caveats of when query plans are derived, etc, it isn't as simple as just using create index on the temp-table later. The article in the post discusses ways of specifying the indexes in the table schema through implicit indexes created due to the use of a primary key and UNIQUE -- in this manner it ensures that the query planner "sees" this extra information.

(Note that the article was written in 2004; SQL Server 2008 may not have this same issue. I do not know. In any case it is a [clever] trick to obtain the desired results.)

The negatives of indexes are the same as well -- over indexing leads to both more overhead in creation/maintenance of the indexes and may, in suboptimal cases, confuse the query planner. Thus, the indexes created should be based on the usage pattern and supported with benchmarks/query analysis.

Happy coding.

Upvotes: 4

Related Questions