Best Practices - Avoid Unnecessary "Order By"

The purpose of this document is to provide best practices to access databases with large datasets when developing in X3 syntax.

Description

When using a For [ABV] loop on a table, the X3 engine sends a Select ... statement to the database, with an Order By clause by default. In most cases, this clause is critical to ensure the consistency of the expected results. Below are examples of situations where the Order By clause is useful:

• When a user interface is involved.
• To promote business logic and manage priorities.
• To ensure technical consistency (for example when a large request is split into several transactions, in which case the order can be critical to manage consistent chunks).

However, the Order By clause can cause serious performance issues if a Select ... statement is performed on a large dataset (in the case it is not useful).

Rules used by the engine

By default, the X3 engine always sends an Order By clause. It generates the SQL sentences based on the various X3 instructions.

Example:For [table] does the following:

• If the syntax is For[table]KEYNAME, the Order By clause defined by the key is sent to the database.
• If the syntax used is For [table] (without key name), an Order By clause is sent based on default rules:
  • If the corresponding File declaration has been written with an Order By Key clause or an Order With clause, this clause will be used.
  • If not, the default key will be used (the first key, unless the [G:table]currind variable has been assigned).

Workaround

• If you want to avoid the Order By clause, use the following syntax:

  >>For [table]reckey

• If you need to check how many records correspond to a given criterion, use the following syntax:

  >> Local File MYTABLE [XXX] Where ...
  >> If rowcount([XXX])<>0 : # records have been found

  This performs a count(*) type of SQL query.
• If you need to check that at least one record corresponds to a given criterion, use the following syntax:

  >> Local File MYTABLE [XXX] Where ...
  >> Look [XXX]reckey First
  >> If fstat=0 : # There is a record

  This performs a query without order and with hints, which tells the system to use a "first row" type of implementation.

Testing results

As a test, a search based on a post code value that matches approximately 40 lines was performed on POSCOD table (1,200,00 records). The script always starts with:

>> Local File POSCOD [POS]
>> Filter [POS] Where POSCOD="..."

The following lines depend on the results expected:

For [POS]
 FOUND=1
 Break
Next

For [POS]reckey
 FOUND=1
 Break
Next

FOUND=nbrecord([POS])

Look [POS]reckey First
FOUND=(fstat=0)

Read [POS]reckey First
FOUND=(fstat=0)

• If you need to check that at least one record exists, use syntax 4.
• If you need to get a record that matches regardless of the record found, use syntax 2.
• If you need to count the number of records matching the result, use syntax 3.
• If you need to access all the first records in a given order, use syntax 1.
• If you need to access all the records, regardless of the order, use syntax 2. The process is significantly longer when all the "fetch operations" are done, but avoiding the order by clause still saves time. For only one fetch operation (syntax 4 vs syntax 5), there is no significant difference.

If this script is frequently used and no standard index exists, consider creating an additional index, or at least an optimization index for the customers that are frequently in this situation.

See also

For, Reckey, Order By, Filter.