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.
- dont use table variables as they cannot be optimized
- add index on joining columns and filter columns
why cant you use temp tables
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
For more help post DDL for the stored procedure and table dependencies with DML for sample data.
SET @RowCount = 1
While (@RowCount <= @NumberRecords)
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
This loop takes long time to run .4 mins to complete .Rest of code executes in 0 secs.
RBAR is the reason for your woes. Please post the DDL for temp_TransactionData and the tables it uses.