It is a huge procedure, and I am copying the stuff related to temp table only....

create table #tmpReport1 (

record_key numeric(18,0) identity,

l1_type_id int null,

l1_type_name varchar(50) null,

l1_bu_id int null,

l1_bu_name varchar(50) null,

l2_type_id int null,

l2_type_name varchar(50) null,

l2_bu_id int null,

l2_bu_name varchar(50) null,

l3_type_id int null,

l3_type_name varchar(50) null,

l3_bu_id int null,

l3_bu_name varchar(50) null,

l4_type_id int null,

l4_type_name varchar(50) null,

l4_bu_id int null,

l4_bu_name varchar(50) null,

l5_type_id int null,

l5_type_name varchar(50) null,

l5_bu_id int null,

l5_bu_name varchar(50) null,

carrier_name varchar(50) null,

product_name varchar(50) null,

business_unit_name varchar(50) null,

business_unit_id numeric(18,0) null,

app_agent_number varchar(9) null,

app_agent_name varchar(50) null,

payment_method varchar(30) null,

commission_option varchar(30) null,

trans_type varchar(30) null,

premium_amount float null,

commission_amount float null,

Date_Modified datetime null,

submission_date datetime null,

account_number varchar(30) null,

money_form varchar(30) null,

application_id varchar(25) null,

product_type varchar(64) null,

customer_name varchar(255) null,

app_status varchar(50) null,

cusip varchar(25) null,

Contract_number varchar (25) null,

Transmission_Date datetime null,

Transaction_ID int,

Annuitant varchar(255)Null,

STATUS_NAME_ID INT

)

Then I am inserting few hundreds to few thousand rows in temp table (depending on the parameters).

Now I am adding index on temp table.

CREATE NONCLUSTERED INDEX IDX_1_TMPReport1

on #tmpReport1(record_key)

CREATE NONCLUSTERED INDEX IDX_2_TMPReport1

on #tmpReport1(app_status)

CREATE NONCLUSTERED INDEX IDX_3_TMPReport1

on #tmpReport1(l1_bu_name)

CREATE NONCLUSTERED INDEX IDX_4_TMPReport1

on #tmpReport1(trans_type)

CREATE NONCLUSTERED INDEX IDX_5_TMPReport1

on #tmpReport1(contract_number)

CREATE NONCLUSTERED INDEX IDX_6_TMPReport1

on #tmpReport1(application_id)

After this is done, there are couple of updates, deletes on temp table.

--runs in a cursor

update #tmpReport1 with(rowlock)

set l1_type_id = @l1_type_id, l1_bu_name = @l1_bu_name, l1_type_name = @l1_bu_type_name, l1_bu_id = @l1_bu_id,

l2_type_id = @l2_type_id, l2_bu_name = @l2_bu_name, l2_type_name = @l2_bu_type_name, l2_bu_id = @l2_bu_id,

l3_type_id = @l3_type_id, l3_bu_name = @l3_bu_name, l3_type_name = @l3_bu_type_name, l3_bu_id = @l3_bu_id,

l4_type_id = @l4_type_id, l4_bu_name = @l4_bu_name, l4_type_name = @l4_bu_type_name, l4_bu_id = @l4_bu_id,

l5_type_id = @l5_type_id, l5_bu_name = @l5_bu_name, l5_type_name = @l5_bu_type_name, l5_bu_id = @l5_bu_id

where record_key = @rec

update.....

delete......

select * FROM #tmpReport1 where app_status <> 'DELETED'

Thanks