For example, this query causes optimization problems with #huge_result:
create proc base_proc
as
select *
into #huge_result
from ...
select *
from tab,
#huge_result where ...
You can achieve better performance by using two procedures. When the base_proc procedure calls the select_proc procedure, the optimizer can determine the size of the table:
create proc select_proc
as
select *
from tab, #huge_result where ...
create proc base_proc
as
select *
into #huge_result
from ...
exec select_proc
If the processing for #huge_result requires multiple accesses, joins, or other processes, such as looping with while, creating an index on #huge_result may improve performance. Create the index in base_proc so that it is available when select_proc is optimized.
