dynamicsaxhints: When is it best to use Table::find().Field or select Field from Table?

[Blog bot]

Источник: http://dynamicsaxhints.blogspot.com/...findfield.html
==============

In this post I am going to analyse the question and define rules for each option.

Problem description
On the one hand, it is a common best practice to select only required information from a database, but quite often an entire record is fetched from the database, even though only one field is actually used. It can be compared to driving a car always on the 1st gear - the engine is fully used, but the car cannot drive fast.
On the other hand, there is a static find method design pattern - the method must be used whenever a record is selected by its key. Find method returns the entire record. Is it contradictory to the common best practice? Let's find out.

Solution
The main decision variable for the question is CacheLookup property of a table. Let's analyse common cases and compile an algorithm.

1. Table CacheLookup property set to EntireTable
It is called set-based caching - all records of a table are placed in the cache after the first select. Subsequent select will get record from the cache instead of the database.
It means that select Field from Table cannot improve performance, so Table::find().Field can be used without performance penalty.

2. Table CacheLookup property set to NotInTTS, Found, FoundAndEmpty
It is called single-record caching - a record is placed in the cache provided that where clause of the select statement matches fields in any unique index. Find method is always based on unique key, so again Table::find().Field can be safely used.
The only exception is a specific case, when CacheLookup property is set to NotInTTS and a field is selected inside a transaction. select Field from Table should be used.

3. Table CacheLookup property set to None
Record is not cached, so select Field from Table is preferable.

4. Find method is not based on unique key
There are table methods in standard code with find prefix, which do not follow design pattern. For example \Data Dictionary\Tables\CustTable\Methods\findByLedgerDimension. Regardless of CacheLookup property a record is not cached.
It means that it is preferable to use select Field from Table.

Conclusions
In the following cases it is best to use select Field from Table:
- CacheLookup property is set to None
- CacheLookup property is set to NotInTTS and field is selected inside a transaction
- Find method is not based on unique key
In all other cases Table::find().Field should be used.

Источник: http://dynamicsaxhints.blogspot.com/...findfield.html