Does anyone know - I mean know - not guess - the performance saving in using Natural syntax STARTING FROM & TO versus STARTING FROM ENDING AT.
I incorrectly assumed that there would be one less L3 command issue by Natural to Adabas because Adabas now had the ending value.
However I see this behaviour:
ENDING AT syntax
L3 ISN123 Response Code 0
L3 ISN246 Response Code 0
L3 ISN789 Response Code 0
with Natural displaying ISN123 & ISN246.
TO syntax
L3 ISN123 response code 0
L3 ISN246 response code 0
L3 ISN246 response code 3
with Natural also displaying ISN123 & ISN246.
I had thought part of the saving was reducing Adabas call count by 1. Would have been useful in situation where:
READ (1 million)
READ(1 record - but with 2 L3s)
END-READ
END-READ
saving 1 million L3s on the inner READ. But no.
And what’s that all about with ISN246 returned with the third L3? TEST DBLOG shows the RB populated. Is that left-over from the previous call? I hope so. Otherwise Adabas has decompressed ISN246 for a second time.
My guess is that the saving is (potentially) a DATA block I/O (in the case where the third records is in another block). Also the saving should be in not decompressing into RB when adarsp=3. I had hoped the L3 would be on the 2nd record and had saved the interprocess communication for a redundant Adabas call.
It is not only saving performance, but saving code. If you have STARTING FROM…TO, the starting value is always the lower value and TO is always the higher value, whether you read ascending or descending. With STARTING FROM…THRU you have to provide the higher value in the starting value and the lower value in the ending value if you read descending. Reading in variable or dynamic directions would be very complicated then.
I am guessing on this, but do not see how this could be done otherwise. Suppose I am READing by NAME TO ‘BROWN’. There are five BROWNs. While processing the fifth BROWN, I STORE a new BROWN. Since I have non re-usable ISNs (assume so), the new BROWN should be returned to me.
Thus, there is no way to have Adabas return an "end of data " indicator after the fifth BROWN. Natural MUST call Adabas to see if there is another BROWN.
The saving is in the absence of an I/O for the next record if we are already at the end of the range of the TO.
My understanding has always been that THRU is evaluated before WHERE, not visa versa as in your post Wilfried. Why evaluate a WHERE for a record that might be outside the range?
THRU (i.e. ENDING AT) logic is applied by Natural, so Adabas hits end-of-file or returns an additional record for Natural to reject. Hence, ISN 789 is retrieved, decompressed, and returned in the record buffer.
TO logic is applied by Adabas, so RC 3 (end-of-file indicator) is returned. No retrieval of ISN 789 is necessary, no decompression. Yes, there is a 3rd Adabas call, but it’s a relatively cheap one. And there’s slightly less work for Natural.
I just thought Adabas would have returned the last record and rsp 3. Even in the 5 browns and then a 6th case - at some point rsp 3 has to be issued and a millisecond later there could be yet another.
If anyone is replacing one syntax with the other remember that with TO if the start value is lower than the to value you get adarsp 061 (whereas ending at will just not enter the read loop); and that TO will leave the read loop with the last records in range whereas ending at will have one record past the range.
Maybe the real performance comes into its own when using multi-fetch. Adabas should know when to stop multi-fetching as it has the TO value in hand.