Concatenated expressions

The LN application can use concatenated expressions, which operate on a combined column. Concatenated expressions that exist on combined columns are:

  • select >=
  • select >
  • select <=
  • select <
  • select between and

For example, an SQL statement can include a where clause such as: 

WHERE comb >= {"tt", "adv", "000"}

Here 'comb' is a combined column of say columns c1, c2 and c3. This expression selects these ranges of rows:

  • c1 = "tt" and c2 = "adv" and c3 >= "000"
  • c1 = "tt" and c2 > "adv"
  • c1 > "tt"

The MSQL driver can use one of the three different techniques: nested, iterative, and filter to let SQL Server solve the WHERE clause in different ways.

These techniques are introduced because the SQL Server optimizer is not able to handle these queries efficiently in all situations (full table scans and sort operations can be introduced for these queries).

Specifying a different technique causes the SQL Server optimizer to make different decisions on how to run a query. It provides some workarounds for typical optimizer behavior.

The MSQLPROF variable can be used to detect long running or bad performing queries. Then you can experiment with these different techniques. MSQLPROF is described in Driver resources and environment variables.

The following describes the nested, iterative, and filter techniques. These techniques are set in the index optimization field of the msql_storage (Infor Baan IV) or msql_driver_param (Infor Baan 5.0/ERP Enterprise/LN) file. See Parameter file formats and configuration options for more information:

  • The nested technique

    The three conditions are ORed to this expression:

    • c1 = "tt" and c2 = "adv" and c3 >= "000" OR
    • c1 = "tt" and c2 > "adv" OR
    • c1 > "tt"

    This can be rewritten as:

    • c1 > "tt" OR
    • c1 = "tt" AND (c2 > "adv" OR
    • c2 = "adv" AND (c3 >= "000"))

    The last expression has a nested AND/OR condition, and will therefore be referred to as the nested technique.

  • The iterative technique

    Multiple SQL statements are issued to resolve one query. These statements do not contain 'OR' conditions, so they can be handled efficiently by the MSQL. The iterative technique can only be used for unbounded queries.

    The iterative technique uses these three conditions:

    • c1 = "tt" and c2 = "adv" and c3 >= "000"
    • c1 = "tt" and c2 > "adv"
    • c1 > "tt"

    First, a query with the first condition is issued. When it does not return a row, it continues with the second condition. When the second condition does not return a row, it continues with the third condition. In addition, when one condition has returned all rows, but more rows are required, the driver continues with the next condition.

  • The filter technique

    This technique is related to the nested technique but has a different approach. It initially selects too many rows, but then filters out those rows that do not match the total WHERE-clause. It selects based on the first column in a concatenated index and filters out rows with the NOT() operator. The query is solved as:

    • c1 >= "tt" AND NOT( c1 = "tt" AND (c2 < "adv" OR
    • c2 = "adv" AND (c3 < "000") )

    As can be seen, the NOT() expression is like an inverted nested query; these rows are filtered out of the initial set determined by the first condition 'c1 >= "tt"'.