Using &NDBSCAN Statements › Efficient Use of &NDBSCAN

Efficient Use of &NDBSCAN

&NDBSCAN can be misused. The scan limits discussed previously are designed to prevent excessive use of resources. When designing applications that use &NDBSCAN, the following should be taken into account:

• Although you can search on any field, use of keyed fields, connected by AND, at the outer level of the scan expression, greatly reduces I/O and elapsed time. Even one keyed field can have a significant impact. For example, the following statement reads every record on the database, if field NAME has no key:

  NAME = 'SMITH' AND ADDRESS CONTAINS 'STREET'
  

  PRESENT and ABSENT use keys if possible. PRESENT makes better use of keys. LIKE uses keys if there are non-special ('_', '%') characters at the front of the pattern. It might still need to examine the actual record.

  The CONTAINS operator always requires records to be read to evaluate success or failure. If you are performing many keyword searches of text using CONTAINS, consider storing the words of the text field as individual, keyed fields. These can then be searched for directly. For example the following statement scans the entire database:

  DESCRIPTION CONTAINS 'WORD'
  

  If, however, as well as field DESCRIPTION, you had fields DESCWD01-DESCWD10, all keyed, containing the individual words of the field DESCRIPTION (&PARSE could be used), you could code the following statement which could use keys:

  ANY DESCWD* = 'WORD'
  

• For interactive applications, encourage the use of scan criteria which reduce the number of hits. If a given scan returns more hits than could be validly processed by the user, display a message requesting more and/or more selective criteria. The scan option to use the results of a previous scan can also be useful in this case.
• Avoid the use of OR at the highest level of the scan expression, if either side of the OR involves a non-keyed field, or PRESENT, ABSENT, or CONTAINS-a full database scan results.
• Only use SORT when absolutely necessary. Sorting will normally involve reading all the records that pass the criteria, extracting the sort field, performing an in-storage sort, and then building the result list. The sort key extraction is done concurrently with final record scanning if non-keyed, and so on, criteria are to be processed.

  This processing can consume large amounts of I/O, as well as storage for the sort keys, and can take significant time.

• Nested scans (SELECT causes) can use large amounts of storage to store inner results.

  Correlated scans can perform multiple passes over the database. This is because an inner scan can be called once for every record in the NDB, and it too can read the NDB, or a large portion of it.

• If you only want to know whether any records at all have, or have not, passed the scan criteria, and you are not interested in the exact number or specific IDs of any such records, then use parameter RECLIMIT=1. &NDBSCAN RECLIMIT=1 does not set the &NDBRID variable.
• If sorting is unavoidable, for example, because you need to be able to reposition in the output sequence, always try to minimize the number of records passing the scan.