Hi!

The following is a select statement found in a BaanERP DLL. A client has done some research and found the performance on the select to be very poor. They are using level 2 drivers on an Informix database. Can anyone suggest on how to improve the statement?

select whinh220.lsta, whinh225.pkdf,
whinh225.oorg, whinh225.orno, whinh225.oset,
whinh225.pono, whinh225.seqn, whinh225.sern,
whinh225.cwar, whinh225.loca, whinh225.item,
whinh225.clot, whinh225.idat,
whinh200.sfty, whinh200.stty, whinh200.sfco
from whinh200, whinh220, whinh225
where whinh225._index2 = {:whinr140.cwar,:whinr140.loca,
:whinr140.item,:whinr140.clot,
:whinr140.idat}
and whinh225.cmbd refers to whinh220
and whinh225.shpc = tcyesno.yes
and whinh225.loct = " "
and whinh220.lsta <> whinh.lstb.shipped
and whinh200._index1 = {whinh225.oorg,
whinh225.orno,
whinh225.oset}
and whinh225.oorg <> whinh.oorg.production and whinh225.oorg <> whinh.oorg.production.man and whinh225.oorg <> whinh.oorg.assembly and not (not (whinh220.oorg = whinh.oorg.assembly) and whinh200.sfty = whinh.type.warehouse and whinh200.stty = whinh.type.warehouse and whinh200.sfco = whinh200.stco) and not (not (whinh220.oorg = whinh.oorg.assembly) and whinh200.sfty = whinh.type.work.center and whinh200.stty = whinh.type.work.center)
order by whinh225.cwar, whinh225.loca, whinh225.item,
whinh225.clot, whinh225.idat,
whinh225.oorg, whinh225.orno, whinh225.oset,
whinh225.pono, whinh225.seqn

Thanks!

I came across the same problem with one client. we logged a case with baan and we did got a solution later on. Basically this huge select statement is been split in to two smaller select statements which improved the performance. you can check with Baan solutions and you could find it. SOrry I dont remember the exact solution number, but it is in outbound (maintain or release).

Ugh, that looks very familiar. Please check that you do not have field level warehouse validations set up on table whinh225 in the AMS as well, that was a recent problem with outbounding that we ran into.