The point of this post is to cleary explain how the combination of the use of data definition language for your temporary tables and non-clustered indexes can improve the performance of stored procedures that much join data from many large tables by up to seventeen times - as I have seen on stored proc.s that work with tables in the hundreds of millions.

Temporary tables, if used frequently or in stored procedures, in turn, end up using significant input/output on disk. One thing we should be aware of is that they are also created as a heap by defaut, as mentioned the other day at SQLteach.com by the great MVP Joe Webb.  As experience has shown us both, if you are cutting up a very large table and using the temporary database, it is best to first do your DDL (data definition language) before runing through the rest of your operation with the temporary data - as opposed Select * INTO #temp. Thus we should be avoiding into #temp as much as possible (unless the number of rows is insignificant) because being in a single statement, it will create great disk contention within the temp database:

Create
table #tempZ
(
 col1 DataType
 col2 DataType,
 col3 DataType
)

INSERT
into
#tempZ
(
 col1,
 col2,-- you can also avoid NULLs by using ISNULL(col,0)
 col3
)
 
select col1, col2, col3
from SomeHugeSourceTable

drop table #tempZ
-- always explicitly/clearly drop the temp at the end of the stored proc.

Then, if you are using the temporary table to join up with several tables afterwards, it is best to make an index on the column being joined to the related tables (hopefully that it's a numeric value too). 

CREATE NONCLUSTERED INDEX [IX_tempFieldName] ON [dbo].[#tempZ]

(

[HopeFullyANumericField]

ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON

[PRIMARY]

EXECUTE

DBname.dbo.cp_MyProc parm1, param2,

param3

 
Finally, to avoid very long stored procedures and to promote reuse, I recommend placing the select statement that matches the insert by using execution of a stored procedure to populate the temporary table and create the index(es).