Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Question Unanswered: How to improve SQL, WITH HOLD is the problem?

    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.

  2. #2
    Join Date
    Jun 2004
    Location
    Madrid, Spain
    Posts
    47
    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.
    José Luis Matute.

    Regards from Spain.

  3. #3
    Join Date
    Sep 2004
    Posts
    5

    Thumbs up begin/commit work was the problem!

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •