Quick question that I couldn’t come up with a method/ability to solve on my own.
Situation:
I have a set of records provided by a 3rd-party. I have previously received many such sets, including a unique ID which is saved in our ADA database. This new set is proposing revisions to some of the previously received meta-data and the only value which should never change is the ID.
I currently do the following:
Read in the entire file, push the unique identifier (and other fields) into a blank object which matches the previously stored object, and then call a sub-program to perform:
define-data
parameter using returnobj /* contains an object handle called return-object
local using externalrecordsview
local
01 #unique-id (a10)
01 #ext-rec-obj handle of object
end-define
#unique-id := return-object.unique-id
find external-records-view with external-records.unique-id = #unique-id
#ext-rec-obj := null-handle
create object #ext-rec-obj of class external-records
reset return-object
move by name external-records to return-object /* moves the fields
end-find
end
Sorry, the above is a typed paraphrase of a DB method, not tested or even stowed - just to show the concept. I am using a single field (#unique-id) to retrieve an object which matches that unique-id for further manipulation.
What I would LIKE to be able to do is to speed up the DB reading process. In effect, since I already have a known #unique-id for each record, I can push them into an array of values (#unique-id-array(*)). Can I use the FIND command in adabas to do a one-time DB call (potentially multi-fetch) to grab an array of the objects matching the IDs in #unique-id-array rather than returning each object after each Adabas call?
The only method I can think of is to make some complicated logical statement (if (examine #unique-id-array(*) for external-record.unique-id giving #number) > 0?) and use it in the WHERE clause, or use it to skip each record that fails the condition. However, if I am processing only a few records this would still result in reading the entire DB by ISN and evaluating WHERE / condition so it would be hugely inefficient.
I don’t have any experience with object-oriented Natural statements or networked database accesses, but I tried this on my NaturalONE CE version and it seems to do what you want. Something like this…
01 #unique-id (a10/ 8 )
Load the first 8 id’s in the array, somehow. Maybe pass in as a parameter.
FIND multi-fetch on external-records-view
with external-records.unique-id = #unique-id(1) or = #unique-id(2) or= #unique-id(3) or= #unique-id(4)
or = #unique-id(5) or = #unique-id(6) or= #unique-id(7) or= #unique-id( 8 )
Process each record as you are doing, I guess returning an array of 8 object handles if that is allowed. Then repeat to process the 2nd set of 8, and so on.
I think there is a limit how many "or = "s you can use, but at least 8 worked. I tested with the NaturalONE profiler and multi-fetch worked fine. Only 1 database call loaded 8 records and then the Natural program printed them for me. Not sure if it will work that way across a network connection to your remote database, but you can give it a try.
Alternately, for more efficient I/O and network usage, could you write a stored procedure on the Adabas server and use an RPC call to pass the data from the 3rd party to it and let the stored procedure do the Adabas reads and updates on the server? Just a thought, never written one myself.
First, excellent solution, George. Thinking outside the box is really important in today’s IT world.
There is one potential problem. Way back when, Adabas had different algorithms to handle different search criteria. There was an algorithm 4 that was particularly inefficient. In the older versions of Adabas, algorithm 4 was invoked whenever you had three separate criteria joined by ANDs. I am not sure, but I seem to recall there was a similar switch to algorithm 4 for some number of criteria OR’ed together. I have not found anything in the documentation yet to indicate if this is true, and if so, what the cutoff number is to make the switch to algorithm 4.
Anyone out there (SAG or non-SAG) who can clarify this?
Jared, there are a number of things that would be useful to know.
First, you mentioned you get a “set of records provided by a 3rd-party.” Is there a typical number of records in a set? What percent of the file might be a typical set? Is there some characteristic of a “set”, such as a value of some field in common?
The last characteristic would perhaps suggest a FIND with that characteristic (in a WITH clause) WHERE the unique ID equals the set of IDs submitted by the 3rd party.
George, on paper that appears to be a solution where I can ‘batch’ together a sub-set of the Unique IDs, however my preference would be to issue a single ‘FIND’ or ‘READ’ command which is capable of returning all of the object results. In your case, I would still have to re-issue the FIND or READ command after each set of 8 objects is read, and thus I doubt it results in any real advantage over simply coding (which may end up being my final implementation)
#num-records := *occ(#unique-id-array)
expand array #return-objects-array to (1: #num-records)
for #i = 1 to #num-records
FIND external-records-view with unique-id = unique-id-array(#i)
create object #return-object of class external-records
move by name external-records-view to #return-object
#return-objects-array(#i) := #return-object
#return-object := null-handle
END-FIND
end-for
Steve; there is no set of typical records, except that I expect the same data fields to exist which may or may not be populated. As such, all the records are stored in a single File (thank God). Sets typically range between 500-10000 records, and the newly encountered ‘Adjustments’ record set could span any number of previously read records where a change to the record has been made and needs to be updated to the previously received record. As such, an *isn exists for the previous records, but the only correlation for the ‘Adjustments’ is the field (unique-id) provided which matches the field (unqiue-id) in the database.
Essentially, what I’m looking for is something like the following:
FIND external-records-view WHERE unique-id exists in #unique-id-array(*)
/* gets all the objects referred and builds the array in a single adabas call
END-FIND
OR
FIND external-records-view WITH unique-id in #unique-id-array(*)
/* gets all objects
END-FIND
However, to the best of my knowledge, since the ‘WITH’ clause is required to identify the descriptor in use, and the valid search-criteria only contain the following:
EQ / =
NE / <>
GT / LT / > / <
NOT (inverted criteria)
There is no criteria which I can specify to verify that the unique-id descriptor exists as a value in an array.
The problem is that there is no Adabas Search command that will accomodate a long list of search values. George’s code is excellent, since a Multifetch of eight eliminates on the order of almost 90% of the Adabas calls.
The coding is quite simple. Instead of your
for #i = 1 to #num-records
FIND external-records-view with unique-id = unique-id-array(#i)
you would have something like
for #j = 1 to #num-eight record groups
compute #i = (#j - 1) * 8
FIND multi-fetch on external-records-view
with external-records.unique-id = #unique-id(#i + 1) or = #unique-id(#i+2) or= #unique-id(#i+3) or= #unique-id(#i+4)
or = #unique-id(#i+5) or = #unique-id(#i+6) or= #unique-id(#i+7) or= #unique-id(#i+ 8 )
This would give you a great improvement over the Adabas calls versus your one-at-a-time Adabas call.
Hopefully someone will answer my question re algorithm 4.