FIND LAST from 2 tables? Is it possible?

[Squiggs]

Hi,

I just started learning Progress this week, and I've been stuck on an efficient way to do a FIND LAST with a WHERE clause that will look at 2 tables.

My goal, is to find the last entry in a table for a particular day, with conditions.

I was able to find the value using a FOR EACH, however I was shocked at just how long this was taking, so I need a faster way. I had also tried to do a BREAK BY on the date, but I didn't seem to be getting what I was expecting.

Scan Table:
scanID (int, pk)
empl-no (char)
scannedOn (datetime)

ScanCode Table
scanID (int, pk, fk)
oneWay (logical)

My Code:

DEF VAR lastEntryTime AS DATETIME INIT '01/01/1970'.
DEF VAR lastEntryId AS ROWID.
DEF BUFFER scan2 FOR scan.

FOR EACH scan2, EACH scanCode WHERE scan2.scanID = scanCode.scanID
AND DATE(scan2.scannedOn) = DATE(tempReportTable.scannedOn)
AND scan2.empl-no = tempReportTable.scannedOn
AND scanCode.oneWay = FALSE:

IF (scan2.scannedOn > lastEntryTime) THEN
DO:
lastEntryTime = scan3.scannedOn.
lastEntryId = ROWID(scan3).
END.
END.


I had tried to set this up as a "FIND LAST scan2, EACH scanCode" which doesnt compile, and I tried "FIND LAST scan2 OF scanCode" which compiles, but always fails.

Anyone have any suggestions?

 

[lloydt]

Scan Table:
scanID (int, pk)
empl-no (char)
scannedOn (datetime)

ScanCode Table
scanID (int, pk, fk)
oneWay (logical)

My Code:

DEF VAR lastEntryTime AS DATETIME INIT '01/01/1970'.
DEF VAR lastEntryId AS ROWID.
DEF BUFFER scan2 FOR scan.

FOR EACH scan2, EACH scanCode WHERE scan2.scanID = scanCode.scanID
AND DATE(scan2.scannedOn) = DATE(tempReportTable.scannedOn)
AND scan2.empl-no = tempReportTable.scannedOn
AND scanCode.oneWay = FALSE:

first, scan2.empl-no = tempreporttable.scannedon ... is that a correct comparison?

I don't know if it's a performance improvement or not, but try your for each like the following, it's at least easier to read and follow:

for each scan2
where scan2.scannedon = tempreporttable.scannedon
and scan2.empl-no = tempreporttable.empl-no,
each scancode
where scancode.scanid = scan2.scanid
and scancode.oneway = false:

Assuming scan2 in the for each statement is the scan table from your definition above, you aren't using any indexed fields, which is most likely your performance loss. I'd recommend building a composite index for the scan table that contains scannedon and empl-no as key members, and then a composite index on the scancode table that contains scanid and oneway as members.

You can build them as individual indexes as well, but that sometimes can lead to performance issues down the road as there are more considerations that need to be taken into account such as index names, etc. In practice, I always identify and build composite indexes whenever possible, using individual field indexes as sparingly as possible.

regards

--LT

 

[TomBascom]

Suggestion #1 -- put "code" tags around your code samples. It makes them readable.

FIND only ever finds one record for one table. You cannot use FIND to perform a join (that's why your "FIND LAST scan2, EACH scanCode" example fails to compile). OF just relates two tables. You shouldn't use it, it is confusing.

Your performance problem is likely due to the lack of an index on the scannedOn field. Without such an index this query will always be slow.

It is also being exacerbated by the use of the DATE() function in the WHERE clause. It is much better to avoid function calls. If there is an index on scannedOn (contrary to my guess above) then something like:

tmpDate = DATE( tempReportTable.scannedOn ). 

FOR LAST scan2 NO-LOCK,
      WHERE
        scan2.empl-no = tempReportTable.scannedOn AND
        scan2.scannedOn >= tmpDate AND scan2.scannedOn <= tmpDate:

  FOR LAST scanCode NO-LOCK
    WHERE
      scanCode.oneWay = FALSE and
      scanCode.scanId = scan2.scanId:

    LEAVE.

  END.

  LEAVE.

END.

DISPLAY scanCode scan2.

You probably want a multi-component index on scannedOn and empl-no. Why is oneWay in a separate table? That seems to just be complicating things for no apparent reason.

And I don't quite understand why scan2 is defined as a buffer for scan nor what scan3 is supposed to be.