Speed up this SP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Speed up this SP

I created a SP that should update a status field. Approx. 22 000 rows needs to be updated in db table z_to_from (which har approx. 500 000 rows). New status comes from z_temp_status. Here`s the SP:
CREATE PROCEDURE [DOCSADM].[UpdateStatus] AS begin declare @rel_status as varchar(40)
declare @customerid as varchar(40)
declare c cursor local for
select customerid,rel_status from z_temp_status order by customerid
open c
fetch next from c into @customerid,@rel_status while @@fetch_status=0
begin update docsadm.z_to_from set rel_status = @rel_status where customerid = @customerid fetch next from c into @customerid,@rel_status
end end
Need help to speed up this SP since it takes a lot of resources for some long time. //Kfinsrud
Check the execution plan from Query analyzer and how about indexes on the table? Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
No indexes on the db table z_temp_status.
Two indexes on the db table z_to_from, one non-clustered index on system_id and one non- clustered on system_id in combination with customerid. Picture of execution plan here:http://finsrud.org/sql/ //kfinsrud
You might want to try this: UPDATE docsadm.z_to_from
SET rel_status = ts.rel_status
FROM docsadm.z_to_from AS tf
INNER JOIN z_temp_status AS ts
ON (tf.customerid = ts.customerid) An index on table z_temp_status on the customerid field, and an index on customerid in the docsadm.z_to_from would probably help.
slight amendment to what Negative has said… UPDATE tf
SET rel_status = ts.rel_status
FROM docsadm.z_to_from AS tf
INNER JOIN z_temp_status AS ts
ON (tf.customerid = ts.customerid)
Indexing z_to_from( customer_id ) no indexes should be required on z_temp_status. Having said that an index on z_temp_status( customer_id, rel_status ) may give you a marginal benefit depending on what else is in z_temp_status If you have indexes on z_to_from( system_id ) as well as z_to_from( system_id, customer_id ), then the index on system_id should be dropped. unless it is used to enforce uniqueness. A clustered index on z_to_from MAY also help, not sure what a unique key may be for this table. If it involves customer_id, then you could try adding a unique clustered index on these fields with customer_id as the leading column Cheers