Selecting Data

Use the following guidelines when selecting data from the database:

Avoid multiple selects of the same logical database record when possible. This is critical within a single transaction, but should be evaluated within a process lifetime as well.
Use the methods listed below to select data in the implementations described:

Class	Method	Implementation
DbSet		Use instead of ResultSet class if an object is subsequently instantiated from the selected keys.
DbSet ResultSet		Use either class to collate through primary keys. Do not use Db.nextKey.
Db	getRecord	When instantiating records that may be new or existing, use the Db.getRecord(Class,keys,classNew) method to minimize IO. Db.getRecord is optimized for single row selects and should always be used if the intention of the program logic is to select a single row from a single table. Specifying a value for the classNew parameter will provide better performance results than using Db.isDefined followed by If/Else coding to instantiate a record or do a Class.new on the record.
Db	getOneRow	Returns a single row. Do not use this method when Db.getRecord can satisfy the program's requirements. NOTE: There are more effective ways of performing the function previously fulfilled by the Db.getOneRow method. At a future date, this method may be removed from the PSL compiler. Therefore, Fidelity recommends that instead of using the Db.getOneRow method, instantiate the object.
Db	select	Generally used to return individual columns from multiple rows.
Db	selectDbSet	Use the Db.selectDbSet method any time the program logic loops through a ResultSet and subsequently instantiates Record<class> objects based on the key columns. The Db.selectDbSet and Db.getRecord method provide improved performance over the combination of Db.select and Db.getRecord. Use the Db.selectDbSet method in conjunction with the DbSet.getRecord method when a single table select is required.

In general, do not try to outperform the SQL engine. Use the SQL aggregate functions COUNT, SUM, AVE, MIN, and MAX instead of calculating these results yourself. Use the SQL WHERE clause, GROUP BY clause, and ORDER BY clause to retrieve only the required rows in the order needed.