Our website is undergoing maintenance. You may experience some unexpected updates or outages. We appreciate your patience as we work to improve your online experience.
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:
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:
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:
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.