Update On Indexed Column

Hi,
I have large table rrsn_security_t - more than half a million rows. I do a complete update on all the rows of the table using the following query.

UPDATE rrsn_security_t
set
cusip = b.fmr_cusip,
master_issuer_num = b.mstr_isr_cusip,
ticker = b.fmr_symb,
description = b.fmr_name,
prim_exchange_code = c.exchange_key,
shares_otstndng = d.amount,
iv_type = b.iv_typ,
active = case when b.deact_date is null then 'Y' else 'N' end
FROM rrsn_security_t a INNER JOIN
ref_security_t b ON
a.security_id = b.fmr_cusip LEFT OUTER JOIN
shares_outstanding_feed_t d ON
b.fmr_cusip = substring(d.fmr_cusip,1,9) AND
d.fmr_type = 'OUTS' LEFT OUTER JOIN
rrs_exchange_t c ON
b.dft_exch_cd = c.exchange_id
where b.fmr_cusip not in (select security_id from rrsn_scrty_ovrrd_in_effect_t)


This is a part of a daily batch load and a DTS package. The table has one clustered index and three non clustered indexes. Two of them are covering indexes. The update is on all the columns that have the non clustered indexes.

The problem is that when I run the update the transaction log runs to more than a GB of memory and alomost takes an hour to do so. Without the index it takes around 300 MB and 7 mins.

I am not confortable with the idea of dropping and recreating the index since it is not necessary in SQL Server 7.0, though it was the case in the previous versions of SQL Server.

Also the query plan with the indexes on shows that Table spool/Eager spool to optimize rewinds takes 50% of the query cost.

Could any one help me with how I should deal with this situation.


Thanks in advance
GV