Best use of indices on temporary tables in T-SQL

Question

If you're creating a temporary table within a stored procedure and want to add an index or two on it, to improve the performance of any additional statements made against it, what is the best approach? Sybase says this:

"the table must contain data when the index is created. If you create the temporary table and create the index on an empty table, Adaptive Server does not create column statistics such as histograms and densities. If you insert data rows after creating the index, the optimizer has incomplete statistics."

but recently a colleague mentioned that if I create the temp table and indices in a different stored procedure to the one which actually uses the temporary table, then Adaptive Server optimiser will be able to make use of them.

On the whole, I'm not a big fan of wrapper procedures that add little value, so I've not actually got around to testing this, but I thought I'd put the question out there, to see if anyone had any other approaches or advice?

Answer 1

What's the problem with adding the indexes after you put data into the temp table?

One thing you need to be mindful of is the visibility of the index to other instances of the procedure that might be running at the same time.

I like to add a guid to these kinds of temp tables (and to the indexes), to make sure there is never a conflict. The other benefit of this approach is that you could simply make the temp table a real table.

Also, make sure that you will need to query the data in these temp tables more than once during the running of the stored procedure, otherwise the cost of index creation will outweigh the benefit to the select.

Answer 2

In Sybase if you create a temp table and then use it in one proc the plan for the select is built using an estimate of 100 rows in the table. (The plan is built when the procedure starts before the tables are populated.) This can result in the temp table being table scanned since it is only "100 rows". Calling a another proc causes Sybase to build the plan for the select with the actual number of rows, this allows the optimizer to pick a better index to use. I have seen significant improvedments using this approach but test on your database as sometimes there is no difference.

Answer 3

A few thoughts:

• If your temporary table is so big that you have to index it, then is there a better way to solve the problem?

• You can force it to use the index (if you are sure that the index is the correct way to access the table) by giving an optimiser hint, of the form:

  SELECT * FROM #table (index idIndex) WHERE id = @id

If you are interested in performance tips in general, I've answered a couple of other questions about that at some length here:

• http://stackoverflow.com/questions/18783/sql-what-are-your-favorite-performance-tricks#103176
• http://stackoverflow.com/questions/137226/how-do-you-optimize-tables-for-specific-queries#138949