Optimize stored procedure

Other SQL Server Topics
1

I have stored procedure with several table variable .Performance is slow and not able to create index on table variable.I cannot change to temp table can anyone please suggest how to optimise table variable.

2
  1. dont use table variables as they cannot be optimized
  2. add index on joining columns and filter columns

why cant you use temp tables

3

You can create PRIMARY KEY or UNIQUE KEY indexes on table variables.

Clarification. PRIMARY KEY and UNIQUE KEY are only designed to enforce the constraints on the table. The problem with table variables is that you don't get statistics on data like you do on temp tables. The optimizer will use row counts and not actual data distribution.

Here is a link with more info: Table Variables vs. Statistics

4

For more help post DDL for the stored procedure and table dependencies with DML for sample data.

5

SET @RowCount = 1
While (@RowCount <= @NumberRecords)
begin
select @tempCode = temp_code_cid, @Invest = inv_def_fund_cid from @tempData where ID = @RowCount

  insert into #tempFund  
  exec temp_TransactionData @tempCode, @asOfBeginDate, @asOfDate, null, @Invest, @inUseSettle, @inUseAsOf, @inAddGT, @inDEBUG, @inEndingBalanceOnly

set @RowCount = @RowCount + 1
end

This loop takes long time to run .4 mins to complete .Rest of code executes in 0 secs.

6

RBAR is the reason for your woes. Please post the DDL for temp_TransactionData and the tables it uses.