Builder Solutions inc.

(1) select continue() endselect
(2) select where() endselect
(3) select_sql
(4) select_sql using()

(1) is the slowest method if LANSA is generating SQL statements based on the select command because the dataset must be generated first and then the code will traverse the entire dataset . (2) is faster than (1) when the fields in the where statement belong to the file and will not cause an Access Path to be built at runtime.

(3) and (4) are the fastest because they do not call the OAM after each record is read.

(4) is faster then (3) when you can utilize TOP(1) instead of FETCH (get 1 record), or using TOP(n) to minimize the dataset. Also (4) can be used to do very complex joins utilizing all the power of SQL (Group By, count , etc)

It is still horses for courses. Factors: existing access paths, complexity of where clause, call server function or just access from client, how many records are being read, is the OAM required when reading records .

Cost v Benefit = Code Complexity v Improved Performance .

For batch processing, reports and server functions (retrieving Instance lists ) we tend to use select_sql and select_sql using() .
For client side we just use standard select and fetch statements.

Its amazing how much faster a cleverly constructed SQL statement with Joins will perform versus using Selects and Fetches to collect the data.