The ttPOHeader table only contains the rows for the transaction currently occurring so there shouldn't be anything really in that table.
However, I did get a reply from Epicor on making more efficient BPM's. I didn't know you could restrict the amount of data moving around for a BPM by declaring only the fields you need from the tables.
Without field declarations, the entire table is moving and joining behind the scenes.
So, my BPM, had I needed the 3 tables joined, would look something like this (and I did test it, runs without any delay).
FOR EACH ttPOHeader NO-LOCK,
EACH PORel FIELDS(company,ponum,poline,duedate,trntype)
WHERE PORel.Company = ttPOHeader.Company
AND PORel.PONum = ttPOHeader.PONum
AND PORel.TranType <> 'PUR-UKN'
AND PORel.DueDate = '' NO-LOCK,
EACH PODetail FIELDS(company,ponum,poline)
WHERE PODetail.Company = ttPOHeader.Company
AND PODetail.PONum = ttPOHeader.PONum
AND PODetail.POLine = PORel.POLine NO-LOCK
However, I did get a reply from Epicor on making more efficient BPM's. I didn't know you could restrict the amount of data moving around for a BPM by declaring only the fields you need from the tables.
Without field declarations, the entire table is moving and joining behind the scenes.
So, my BPM, had I needed the 3 tables joined, would look something like this (and I did test it, runs without any delay).
FOR EACH ttPOHeader NO-LOCK,
EACH PORel FIELDS(company,ponum,poline,duedate,trntype)
WHERE PORel.Company = ttPOHeader.Company
AND PORel.PONum = ttPOHeader.PONum
AND PORel.TranType <> 'PUR-UKN'
AND PORel.DueDate = '' NO-LOCK,
EACH PODetail FIELDS(company,ponum,poline)
WHERE PODetail.Company = ttPOHeader.Company
AND PODetail.PONum = ttPOHeader.PONum
AND PODetail.POLine = PORel.POLine NO-LOCK
--- In vantage@yahoogroups.com, Mark Wonsil <mark_wonsil@...> wrote:
>
> Would it be a good general rule to make the table with the most restrictive
> criteria the first table from a performance point of view?
>
> Mark W.
>
> On Tuesday, March 5, 2013, pbparker wrote:
>
> > **
> >
> >
> > Solved it myself, didn't really need to join to the PODetail table when
> > all my fields of concern are in the PORel table.
> >
> > Rewritten as:
> >
> > FOR EACH ttPOHeader,
> > EACH PORel NO-LOCK
> > WHERE ttPOHeader.Company = PORel.Company AND ttPOHeader.PONum =
> > PORel.PONum AND PORel.TranType <> 'PUR-UKN' AND PORel.DueDate = ''
> >
> > --- In vantage@yahoogroups.com <javascript:_e({}, 'cvml',
> > 'vantage%40yahoogroups.com');>, "pbparker" wrote:
> > >
> > > So, I had a BPM that executed without delay in 9.05.700, however in
> > 9.05.701 it's taking 45 seconds. It's all coming down to this condition
> > query. I'm checking for Due Dates on all releases at the point of approving
> > a PO, so here's my query looking for rows more than 0.
> > >
> > > So, my question is, does anyone know of a better more efficient way of
> > joining the 3 tables together?
> > >
> > > FOR EACH ttPOHeader,
> > > EACH PORel NO-LOCK,
> > > EACH PODetail NO-LOCK
> > > WHERE ttPOHeader.Company = PODetail.Company
> > > AND ttPOHeader.PONum = PODetail.PONum
> > > AND ttPOHeader.Company = PORel.Company
> > > AND ttPOHeader.PONum = PORel.PONum
> > > AND PODetail.POLine = PORel.POLine
> > > AND PORel.TranType <> 'PUR-UKN'
> > > AND PORel.DueDate = ''
> > >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>