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
Swarndeep
http://talksql.blogspot.com