SQL Server Performance Forum – Threads Archive

Speed up this SP

Hi,
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
GO
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
Moderator
http://www.SQL-Server-Performance.Com/forum
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
Twan

]]>