IDS Forum

RE: Perfomance

Posted By: Everett Mills
Date: Tuesday, 13 July 2004, at 4:30 p.m.

In Response To: Re: Perfomance (Ruben Dutan )

Comments below --EEM

> -----Original Message-----
> From: Ruben Dutan [mailto:rdutan@aplext.com]
> Sent: Tuesday, July 13, 2004 1:07 PM
> To: ids@iiug.org
> Subject: Re: Perfomance [3236]
>
> 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;
>

These update statistics statements will not do anything useful until data is loaded into the temp tables.

> load from TL052003.csv
> insert into tl;
>
> insert into regtl
> select serial,""
> from tl;
>
Try them here, instead:
update statistics for table regtl;
update statistics for table 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.