blandry (4/29/2011)
What we found was that on queries that fill large Datasets, we did see some improvement, but on smaller (or some medium) fills, the temporary indexes were actually slowing performance. We were able to increase performance there by making a number of these indexes permanent so we avoided the create-on-the-fly element involved.
I was actually wondering how many of these would work better as permanent indexes. The last place I worked used them for upgrades but that was it. We did index temporary tables but in those cases the indexes were permanent in relation to the lifespan of the table so I wouldn't consider those temporary indexes.