IDS Forum

Re: Perfomance

Posted By: Ruben Dutan
Date: Tuesday, 13 July 2004, at 2:07 p.m.

In Response To: Re: update statistic session status! (Rajib Sarkar )

Thanks for your help.
But I think don't optimize correct because not access with index created
The query is:
create temp table regTL
(serial integer,
reg char(211));

create temp table TL
(serial integer,
ruc char(13) NOT NULL,
periodo char(3) NOT NULL,
secuencia char(2) NOT NULL,
no_documento char(13) NOT NULL,
tipo_cbte char(2) NOT NULL,
fecha_e char(8) NOT NULL,
fecha_r char(8) NOT NULL,
serie_cbte char(6),
secuencia_cbte char(7),
autoriza_cbte char(10),
identificacion char(2) NOT NULL,
base_imponibleg char(12) NOT NULL,
codigo_por_iva char(1) NOT NULL,
base_imponible_0 char(12) NOT NULL,
monto_iva char(12) NOT NULL,
monto_ice char(12) NOT NULL,
monto_iva1 char(12),
codigo_ret_iva char(1),
monto_ret_iva char(12),
monto_iva2 char(12),
codigo_ret_iva2 char(1),
monto_ret_iva2 char(12),
tran_dev char(1) NOT NULL,
cant_cbte CHAR(12),
cant_nc CHAR(12),
cant_nd CHAR(12));

create index idx_fr3 on regtl(serial);
create index idx_fr4 on tl(serial);
update statistics for table regtl; -- add but don't work
update statistics for table tl;

load from TL052003.csv
insert into tl;

insert into regtl
select serial,""
from tl;

update regtl
set reg = NULL
where serial>0;

update regtl
set reg[211] = "-"
where serial>0;

set explain on;
update regtl
set reg[1,13] =
(select ruc
from tl
where tl.serial = regtl.serial), -- here access is sequential
-- don't access with index
reg[14,16] =
(select periodo
from tl
where tl.serial = regtl.serial),
reg[17,18] =
(select secuencia
from tl
where tl.serial = regtl.serial),
reg[19,31] =
(select no_documento
from tl
where tl.serial = regtl.serial),
reg[32,33] =
(select tipo_cbte
from tl
where tl.serial = regtl.serial),
reg[34,41] =
(select fecha_e
from tl
where tl.serial = regtl.serial),
reg[42,49] =
(select fecha_r
from tl
where tl.serial = regtl.serial),
reg[50,55] =
(select serie_cbte
from tl
where tl.serial = regtl.serial),
reg[56,62] =
(select secuencia_cbte
from tl
where tl.serial = regtl.serial),
reg[63,72] =
(select autoriza_cbte
from tl
where tl.serial = regtl.serial),
reg[73,74] =
(select identificacion
from tl
where tl.serial = regtl.serial),
reg[75,86] =
(select base_imponibleg
from tl
where tl.serial = regtl.serial),
reg[87,87] =
(select codigo_por_iva
from tl
where tl.serial = regtl.serial),
reg[88,99] =
(select base_imponible_0
from tl
where tl.serial = regtl.serial),
reg[100,111] =
(select monto_iva
from tl
where tl.serial = regtl.serial),
reg[112,123] =
(select monto_ice
from tl
where tl.serial = regtl.serial),
reg[124,135] =
(select monto_iva1
from tl
where tl.serial = regtl.serial),
reg[136,136] =
(select codigo_ret_iva
from tl
where tl.serial = regtl.serial),
reg[137,148] =
(select monto_ret_iva
from tl
where tl.serial = regtl.serial),
reg[149,160] =
(select monto_iva2
from tl
where tl.serial = regtl.serial),
reg[161,161] =
(select codigo_ret_iva2
from tl
where tl.serial = regtl.serial),
reg[162,173] =
(select monto_ret_iva2
from tl
where tl.serial = regtl.serial),
reg[174,174] =
(select tran_dev
from tl
where tl.serial = regtl.serial),
reg[175,186] =
(select cant_cbte
from tl
where tl.serial = regtl.serial),
reg[187,198] =
(select cant_nc
from tl
where tl.serial = regtl.serial),
reg[199,210] =
(select cant_nd
from tl
where tl.serial = regtl.serial)
where serial IN (select serial from tl);

unload to TL052003.ane
select reg from regtl;

Regards
Rubén

Messages In This Thread

• update statistic session status!
  FRANKLIN TANG -- Monday, 27 January 2003, at 4:27 a.m.
  • RE: update statistic session status!
    Andreas.KUTSCHE@spar.at -- Monday, 27 January 2003, at 7:50 a.m.
  • Re: update statistic session status!
    Rajib Sarkar -- Monday, 27 January 2003, at 9:48 a.m.
    • Fwd: Re: update statistic session status!
      ART KAGEL, BLOOMBERG/ 65E 55TH -- Monday, 27 January 2003, at 11:29 a.m.
    • Re: Perfomance
      Ruben Dutan -- Monday, 12 July 2004, at 4:41 p.m.
    • Re: Perfomance
      Ruben Dutan -- Tuesday, 13 July 2004, at 2:07 p.m.
      • RE: Perfomance
        Everett Mills -- Tuesday, 13 July 2004, at 4:30 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.