Re: I/O performance problem

From: Giuseppe De Donno <dedonno_at_ariadne.it>
Date: Thu, 30 Apr 1998 10:28:47 +0200
Message-ID: <3548363E.AC822B43@ariadne.it>

Dave O'Keeffe wrote:

> Giuseppe,
>
> I think your problem is your doing 43285 reads and 24,000 odd disk
> accesses in order to return 6 rows. This is about 4,000 disk accesses
> per row returned! The SQL statement may be a little inefficient.
>
> If you post the SQL some of our tuning stars can probably help you.

Ok, my query is:

  select distinct a.serditta, upper(a.codprovincia),

    upper(a.nummeccanog), upper(a.denominazione),
    upper(a.capoperativo), upper(a.comuneoperativo),
    upper(a.provinciaoperativo), upper(a.statoazienda),
    upper(a.flagvalidazione), upper(a.flagcesdir),
    upper(a.flagcessazione)

    from ditte a ,prodotti b
    where upper(a.classefatturato) like 'C' and     upper(a.classeaddetti) like 'B' and     ( upper(a.provinciaoperativo) like 'MI')     and b.serditta = a.serditta and
    ( upper(b.desprodotto) like '%CARNE%' ) and     b.impexp like 'I'
UNION
select distinct aa.serditta, upper(aa.codprovincia),

    upper(aa.nummeccanog), upper(aa.denominazione),
    upper(aa.capoperativo), upper(aa.comuneoperativo),
    upper(aa.provinciaoperativo), upper(aa.statoazienda),
    upper(aa.flagvalidazione), upper(aa.flagcesdir),
    upper(aa.flagcessazione)

    from dittemi aa ,prodotti bb
    where upper(aa.classefatturato) like 'C' and     upper(aa.classeaddetti) like 'B' and     ( upper(aa.provinciaoperativo) like 'MI') and     bb.serditta = aa.serditta and
    ( upper(bb.desprodotto) like '%CARNE%' ) and     bb.impexp like 'I'
order by 4

The indexes on column in the where condition are present .

Thanks
Giuseppe Received on Thu Apr 30 1998 - 03:28:47 CDT