Hello,
I have a stored procedure that update 600.000 records, but it is very slow (60 hrs). In order to improve the performance i'm using a view. I'm use a cursor with "WITH HOLD" statement, all tables have index and sqexplain, show this:

----------
Procedure: boleta.sp_insertaspriv
select x0.serv_nm_servic ,x0.circ_nm_cto ,x1.refc_cd_nrut ,x1.refc_vr_digito ,x0.serv_cd_sigla ,(select x3.lugc_cd_sigl from spriv:"spriv".sprmlugc x3 where (x3.lugc_cd_codi = x0.serv_cd_lug1 ) ) ,(select x4.lugc_nm_depe from spriv:"spriv".sprmlugc x4 where (x4.lugc_cd_codi = x0.serv_cd_lug1 ) ) ,(select x5.dicl_gl_glosa_dir from spriv:"spriv".sprmdirclie x5 where ((((x5.dicl_gl_cliente = x0.serv_cd_sigla ) AND (x5.dicl_nm_rut = x1.refc_cd_nrut ) ) AND (x5.lugc_cd_lugar = x0.serv_cd_lug1 ) ) AND (x5.dicl_cd_numdir = x0.circ_cd_dir1 ) ) ) ,(select x6.lugc_cd_sigl from spriv:"spriv".sprmlugc x6 where (x6.lugc_cd_codi = x0.serv_cd_lug2 ) ) ,(select x7.lugc_nm_depe from spriv:"spriv".sprmlugc x7 where (x7.lugc_cd_codi = x0.serv_cd_lug2 ) ) ,(select x8.dicl_gl_glosa_dir from spriv:"spriv".sprmdirclie x8 where ((((x8.dicl_gl_cliente = x0.serv_cd_sigla ) AND (x8.dicl_nm_rut = x1.refc_cd_nrut ) ) AND (x8.lugc_cd_lugar = x0.serv_cd_lug2 ) ) AND (x8.dicl_cd_numdir = x0.circ_cd_dir2 ) ) ) ,x2.svtp_gl_tippal ,x2.svtp_gl_tipsec ,x0.circ_cd_estado ,x0.circ_cd_veloc from spriv:"spriv".sprmccto x0 ,spriv:"spriv".sprmrefclie x1 ,spriv:"spriv".sprmsvtp x2 where (((((x0.serv_cd_sigla = x1.refc_gl_sigla ) AND (x0.serv_cd_ncl = x1.refc_cd_ncl ) ) AND (x0.serv_cd_tipo = x2.svtp_cd_tipo ) ) AND (x0.serv_gl_area = 'LD' ) ) AND ((x0.serv_nm_servic = ? ) AND (x0.circ_nm_cto = ? ) ) )

QUERY:
------


Estimated Cost: 4
Estimated # of Rows Returned: 1

1) spriv:spriv.sprmccto: INDEX PATH

Filters: spriv:spriv.sprmccto.serv_gl_area = 'LD'

(1) Index Keys: serv_nm_servic circ_nm_cto (Serial, fragments: ALL)
Lower Index Filter: (spriv:spriv.sprmccto.serv_nm_servic = '0 ' AND spriv:spriv.sprmccto.circ_nm_cto = '1 ' )

2) spriv:spriv.sprmrefclie: INDEX PATH

(1) Index Keys: refc_gl_sigla refc_cd_ncl
Lower Index Filter: (spriv:spriv.sprmccto.serv_cd_sigla = spriv:spriv.sprmrefclie.refc_gl_sigla AND spriv:spriv.sprmccto.serv_cd_ncl = spriv:spriv.sprmrefclie.refc_cd_ncl )
NESTED LOOP JOIN

3) spriv:spriv.sprmsvtp: INDEX PATH

(1) Index Keys: svtp_cd_tipo
Lower Index Filter: spriv:spriv.sprmccto.serv_cd_tipo = spriv:spriv.sprmsvtp.svtp_cd_tipo
NESTED LOOP JOIN


Because this is a very used information, i must to use a cursor during update process. After every operation, the stored procedure executes a 'commit work' statement.

How to detect what's wrong?


ps. we have another update process in c/sql and in 15 minutes make everything.

I'd try two approaches:
- Simplify the query. ¿is it an external-tool generated one (like Bussiness Objects or Microstrategy do)?
- Do not open and close the transaction for each row. If you can't do it in just one transaction (it can be very long) break it in parts (using a counter, for instance)

Hope it helps.

Thanks José.
I can to resolve the problem using a counter for transactions, every 100 transactions the stored procedure send a 'commit work' statement and then a 'begin work' in order to begin another transaction.
foreach cursor with hold for
....
...
if intCounter = 100 then
commit work;
begin work;
let intCounter = 1;
else
let intCounter = intCounter + 1;
end if

end foreach

the stored procedure run in 1 hr.

thanks again.