Using Select Where.In to optimize your code

The Select statement is an important tool for speeding up an application. For performance, a properly optimized Select statement can be better than writing your own loop to read data from your Synergy DBMS ISAM files. It’s especially important when using xfServer, as all of the reading and selection occurs on the server, with only the desired data being sent over the network to the client. The following environment variables can further improve xfServer performance with Select:

• SCSPREFETCH allows the server to send multiple records at once to the client.
• SCSCOMPR causes the record(s) to be compressed when transferred from the server to the client.

The Where.In clause is a useful way to find the match of several values for a specified field. When you need to compare a field against multiple values, you can specify each comparison separately in the expression, but the Where.In syntax makes it easier to read and understand the code. For example:

(Where)((fld .eqs. “AAA”) .or. (fld .eqs. “BBB”) .or. (fld .eqs. “CCC”))

is simpler written like this:

Where.In(fld, “AAA”, “BBB”, “CCC”)

This simplified form is expanded to the first form (with the .OR. operators) behind the scenes to perform the operation.

The Select statement optimizes access if the file is an ISAM file and fields in the Where.In expression are keys in the ISAM file. But previously the use of .OR. disabled all key field optimization. With Synergy 10.3.1, however, some Select Where expressions that use .OR are optimized. For optimization to occur, comparison operators must be .EQS. or .EQ., all comparisons must use the same field which must be a key, and comparisons must be separated by the .OR. operator. With the above example, the Select can create a series of FINDs (for records matching each of the values) and READs (for fields with the same value).

Even more complex expressions can be optimized. Take the following example:

Where.In(fld, 3, 4, 5) .and. ((fld2 .eq. “NY”) .or. (fld2 .eq. "NJ"))

By duplicating the .AND. portion of the expression with each .OR. comparison, the example results in this:

((fld .eq. 3) .and. ((fld2 .eq. “NY”) .or. (fld2 .eq. "NJ"))) .or.

((fld .eq. 4) .and. ((fld2 .eq. “NY”) .or. (fld2 .eq. "NJ"))) .or.

((fld .eq. 5) .and. ((fld2 .eq. “NY”) .or. (fld2 .eq. "NJ")))

The value comparisons occur first, followed by .AND. expressions. The .AND. expression can be complex, so be sure to use parentheses in the Where expression to ensure that the .AND. has precedence over any .OR..

If any of the following changes were made to the above example, optimization would no longer occur:

• Making the key case-insensitive
• Replacing the .AND. with .OR. (because then either side being true would make a valid match, and the entire file would need to be read instead of only a subset)
• Placing a .NOT. in front of Where.In (which would invert the selection)

To find out if Select has been optimized, use the DBG_SELECT environment variable to log optimization details. (You can control where the log is written by setting the DBG_SELECT_FILE environment variable.)

And note that the following rules apply:

• If two Where.In clauses are specified in the same Where with different keys, Where.Keynum can be used to specify which key to use for optimization.
• If a partial key is specified, it must start at the beginning of the key field and must not be missing any segments in the middle for optimization to occur.
• If two Where.In clauses using the same key field are separated by .OR., they will be combined into a single Where.In with all of the comparison values from both.

As you can see, Select Where.In can be a very powerful tool. If you want your code to run more efficiently (and who doesn’t?), try it in your own application soon.

You can find more information in Synergex.SynergyDE.Select.Where and NoCaseWhere in the Synergy/DE documentation.