Slow update

[rotelmak]

Table finarh have this fields:

customer_id char(9)
datanal datetime
nalog char(6)
dokument char(12)
diznos decimal(12,2)
piznos decimal(12,2)
datadok datetime
valudok datetime
oe char(4)
re char(2)

Also table have clustered index on godina, re, customer_id, oe, datanal

There are about 170,000 records

This is the stored procedure (sql 2000)

*******************************************************************
CREATE PROCEDURE [dbo].[test]
AS
BEGIN

set NOCOUNT on

Select finarh.customer_id,
finarh.datanal,
finarh.nalog,
finarh.dokument,
finarh.diznos,
finarh.piznos,
diznos as saldo,
finarh.datadok,
finarh.valudok,
finarh.oe,
finarh.re
into #pom1
from finarh
where godina = '2008'
and re = '01'
and customer_id between '000000000' and '999999999'
order by customer_id, datanal, nalog
-----------------------------


DECLARE @mcustomer_id char(9), @miznos decimal(12,2)

DECLARE k_cursor CURSOR FOR
SELECT finarh.customer_id, SUM(iznos) as iznos
FROM finarh
where godina = '2008'
and re = '01'
and customer_id between '000000000' and '999999999'
group by finarh.customer_id
ORDER BY finarh.customer_id

OPEN k_cursor

FETCH NEXT FROM k_cursor
INTO @mcustomer_id, @miznos

----
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @saldo decimal(12,2)
SET @saldo = 0
UPDATE #pom1
SET @saldo = saldo = @saldo + diznos - piznos
where customer_id = @mcustomer_id


FETCH NEXT FROM k_cursor
INTO @mcustomer_id, @miznos
END

-------------------------------

CLOSE k_cursor
DEALLOCATE k_cursor

SELECT * FROM #pom1
order by customer_id, datanal, nalog

set NOCOUNT off

END

**********************************************************
Executing this procedure take about 100 seconds.

Is it possible to get results faster ?

 

[SQLSister]

You should never use a cursor for an update. Look up the syntax in Books Online for using joins in an update and use that instead.

"NOTHING is more important in a database than integrity." ESquared