Hi Baan Gurus,

Can you help me out in optimizing the below query, i need all the clauses to print my report.

Thanks in advance.

select sdshp910.*
from sdshp910
where sdshp910._index2 = { :sdshp920.fshp, tdilc.koob.act.rpl }
and sdshp910.shpd = :sdshp920.shpd
and sdshp910.rwar between :cwar.f and :cwar.t
and sdshp910.dwar between :dwar.f and :dwar.t
and sdshp910.sreg between :sreg.f and :sreg.t
and sdshp910.owsn = :alia.f
and sdshp910.scut = :scut.f
and (( :curr.dsty = sddoc.dsty.bfin and
sdshp910.bdst between :dset.f and :dset.t )
or ( :curr.dsty = sddoc.dsty.afin and
sdshp910.adst between :dset.f and :dset.t )
or ( :curr.dsty = sddoc.dsty.mass and
sdshp910.mdst between :dset.f and :dset.t ))
and sdshp910.cdac between :cdac.f and :cdac.t
and sdshp910.cuno between :cuno.f and :cuno.t
and sdshp910.cdel between :cdel.f and :cdel.t
and sdshp910.ctyp between :ctyp.f and :ctyp.t
and sdshp910.cfrw between :cfrw.f and :cfrw.t
order by sdshp910.sref
selectdo

I assume the query as it is slow? l am not really sure how to optimize it, but you can play with trying a couple of things. For example you can comment out each of the and statements to see if one really slows up the query. I am thinking maybe the curr.dsty pieces might be quicker just putting in an if-then-endif statement in the selectdo. You can also try commenting out the order by clause - if possible you can try to let the report sort. Been a few years but I recall order by causing some issues.

Mark is correct - it is slow mainly because of the order by clause.

The second reason could be the OR clauses - I have noticed that sometimes moving the OR conditions to inside of the selectdo to skip what is not required improves performance. For some reason, the BAAN select is slow when OR conditions are involved.

You should get better performance if your order by was sdshp910._index2, since that would force the index to be used.