Did you ever think that it would be great to cache SQL Server database query…
As you probably already know, you can add indexes on temporary tables to improve the performance, but you can probably improve the performance even more. The index can be build with Fill factor 100 to increase the read performance, and you can also disable row and page locking to force Tablock on your index on the temporary table. This will remove the overhead of lock escalation and may improve the performance with approximate 25% for large temptables.
CREATE TABLE #tmp
(
ID INT
)
(
ID INT
)
INSERT INTO #tmp SELECT * FROM sys.indexes
CREATE INDEX ix ON #tmp
(
ID
)
WITH (FILLFACTOR=100, ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)