Svein.
Thanks for Your competence and patience.

Regards
Arnaldo

--- In ib-support@y..., Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> 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
> >
> >
> >
> >