Hi Arnaldo,
take my advice for what it is - only advice and I don't know if everything
is true so check it for yourself:

1)
Change

> ( presenze.datapresenza >= :piniziale
> and presenze.datapresenza <= :pfinale)

to
( presenze.datapresenza between :piniziale and :pfinale

I'm not sure whether the optimizer is able to use an index properly the way
you've written it, but it should be able to do so with my suggestion.

2)
You use 'LIKE' heavily. LIKE cannot use indices. See if any of them can be
replaced by STARTING WITH or =

3)
Simplify

> sum((cast( presenze.orelavorate as time ) - cast( '0.00.00' as
>time ) ) /60/60 )

to
sum(presenze.orelavorate - cast( '0.00.00' as time ) ) /3600 )
Assuming orelavorate is defined as TIME, there's no need to cast it once
again.

4)
See if any of the fields where you use =, STARTING WITH, BETWEEN or most
other operators excepting LIKE or some form of negation greatly reduces the
number of records returned. E.g is there a lot of different values for
manzione? If so (and if it isn't a primary or foreign key), create an index
for that field - possibly with the addition of the primary key at the end
of the index.

5)
My last suggestion may not be all too important for speed: You seem to use
TIME fields for duration, rather than a set time (i.e. 8:15 meaning 8 hours
and 15 minutes and not 8:15AM). Consider storing these as numbers rather
than TIME, and simply use
cast(sum(presenze.orelavorate)/3600) (this requires that orelavorate is
defined as some kind of number, not TIME)
for display purposes. I haven't thought thoroughly through this last
suggestion, it's just something that seems sensible to me.

Hope this will help you somewhat,
Set

At 15:13 15.02.2001 -0000, you wrote:

>Hi all.
>This is a Query:
>They are equal differ only for a field named: statuspresenza
>as seen below.
>
>If I don't abuse the patience of somebody, could gain a litte support
>to optimize this Query ????????
>Sorry for the Italian names of the field.
>
> /* FERIAL */
> select
> sum((cast( presenze.orelavorate as time ) - cast( '0.00.00' as
>time ) ) /60/60 ),
> sum (presenze.prezzototalesocio)
> from presenze where
> ( presenze.datapresenza >= :piniziale
> and presenze.datapresenza <= :pfinale)
> and presenze.collsocio like :pcollsocio
> and presenze.collditta like :pcollditta
> and presenze.collfattura like :pcollfattura
> and presenze.collpagamento like :pcollpagamento
> and presenze.statuspresenza = 'Presenza Feriale'
> and presenze.mansione=:vmansione
> into :voreferiale, :vpferiale;
>// this is the second part of the query :
> /* SUNDAY */
> select
> sum((cast( presenze.orelavorate as time ) - cast( '0.00.00' as
>time ) ) /60/60 ),
> sum (presenze.prezzototalesocio)
> from presenze where
> ( presenze.datapresenza >= :piniziale
> and presenze.datapresenza <= :pfinale)
> and presenze.collsocio like :pcollsocio
> and presenze.collditta like :pcollditta
> and presenze.collfattura like :pcollfattura
> and presenze.collpagamento like :pcollpagamento
> and presenze.statuspresenza = 'Presenza Domenicale'
> and presenze.mansione=:vmansione
> into :voredomenica, :vpdomenica;
>
>
>TIA
>Arnaldo
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>