Compatibility settings – or driving with the parking brake on(Part 1)

In code kitchens, I usually spend some time with the database settings, especially with the Compatibility settings. Often certain settings don’t follow best practices and during discussions with the application developer, I hear “oh, I never changed these” or “I’m not sure about the impact, so better not touch them”.

Since they could drastically impact the performance or behavior of your applications, we’ve begun a series of blog posts to discuss some of these “secret” settings.

Part 1 of compatibility settings – QUERY BY FORMULA

 

These settings force 4D to behave as it did in version 2004, reducing the feature set and performance. And it is so easy to change, but customers are afraid of unknown side effects.

QUERY BY FORMULA was quite slow in v2004 (because all records were transferred to the client), without a lot of added value. As result, most developers ignored it. The rules were changed with v11.

The QBF command (just to keep it short in this blog post) allows you to use calculations such as searching for picture size or text length. The benefits of this feature are fairly obvious. However calculations like this can’t use an index, so they’re slower than an indexed query.

But the QBF command can do so much more! It can combine query groups with parenthesis: ((field1=1) and (field2=”A”)) OR ((field1=50) and (field2=”B”)).

Not using the QBF command means running two queries and using sets, resulting in much more network traffic, more dependence on network performance, and more load for the 4D Server. 4D Server’s query optimizer automatically detects the best (fastest) way to handle such conditions on fields of the same table – or even related tables.

And best of all, it can build relations on its own (aka joins), even if no link is drawn in structure mode. Yes, you can build relations dynamically to run queries (if the “QUERY BY FORMULA Uses SQL Joins” option is enabled).

The “QUERY BY FORMULA Uses SQL Joins” option might be confusing because it is not related to SQL at all, you should think of it more like “QBF supports dynamic relations”.

 

Let’s use a simple structure as an example:

 

Both QUERY and QUERY BY FORMULA could be used to search using a relation, such as:

QUERY BY FORMULA([Table_2];[Table_1]Field_2="1")

But only QBF allows you to explicitly define the relationship (allowing the use of any possible relation), instead of requiring a defined relationship to already exist. Now let’s imagine there is a second logical connection between these two tables, but doesn’t already exist as a 4D relation … Field3 with Field2. QBY allows you to specify the relationship on the fly:

QUERY BY FORMULA([Table_2]; ([Table_1]ID=$var)& \
  ([Table_1]Field_2=[Table_2]Field_3))

Note: When using dynamic joins, it must compare two fields of different tables. If part of a query, this is automatically handled as a join, to define a relationship.

If you want to search for a value in another table, you must first assign it to a local variable. This clearly defines that you want to use it as constant.

Enabling the “QUERY BY FORMULA Uses SQL Joins” option is mandatory to allow the “new” query editor (i.e., the query editor introduced with v14) to use relations. The reason is simple, it uses the QUERY BY FORMULA command internally.

Summary:

• QUERY BY FORMULA allows using parenthesis to express complex query conditions, whereas QUERY only allows multiple OR or multiple AND conditions, but not a combination of both.
• QUERY BY FORMULA supports joins and dynamic queries.
• QUERY BY FORMULA allows using formulas, such as picture size.
• QUERY BY FORMULA is executed on the server (similar to a normal QUERY), providing the same or even better speed because it generates less network traffic compared to a combination of QUERY and set operations.

 

Note: all the above is true for the classic 4D language. ORDA-based code is always executed on the server.

Migration work required to enable compatibility

The above settings are only visibile in the Compatibility dialog for structures created with 4D v2004 or older. If you don’t see these settings, your structure is newer and always behaves in v11 mode, so you’re good to go!

Use the Find option in design mode to search for QUERY BY FORMULA. Since your structure is in “slow” v2004 mode, the list should be short. Double-click each result and check the code.

As long as the formula uses constants, such as:

QUERY BY FORMULA([Table_2];[Table_1]Field_2="1")

Or

QUERY BY FORMULA([Table_2];[Table_1]Field_2=$var) // similar for var or <>var

All is well, there’s nothing to do. 4D Remote will interpret the variable and send its content to the server (similar to a constant). The client’s value is always used, even if the query is executed by the server.

If the formula is a method, such as:

QUERY BY FORMULA([Table_2];RunQuery)

You need to open and check the content of the method. If parameters are passed to the method, chances are high that the method is generic (but you still should check). If no parameters are passed, chances are high that it will fail.

Imagine RunQuery contains:

$0:= ([Table_2]=myvar)

The content of myvar will be different on 4D Remote and 4D Server, so running this method on 4D Server will fail. It will return different results.

 

There are two ways to fix this issue. For simple queries as above, you could simply rewrite it as RunQuery(myvar) and inside the method use $1. Problem solved.

But you might find a really complex method. Thousands of lines of code, no documentation, written 20 years ago. And only used once per year. That’s a lot of work and risk for errors, for minimal impact. To workaround the issue, you can opt to only run this code in v2004 mode, while keeping everything else in modern mode:

SET DATABASE PARAMETER(Query by formula on server;1)
QUERY BY FORMULA([Table_2];RunQuery )
SET DATABASE PARAMETER(Query by formula on server;0)

SET DATABASE PARAMETER allows changing the mode on the fly, avoiding the need to spend hours on rarely used code.

 

Finally, you should check if fields are used in a way which hurts the join feature. With v2004 a formula such as [Table_1]Field_2=[Table_2]Field_3 is interpreted as searching for static/fixed content, using the content of Field_3. After enabling the “QUERY BY FORMULA Uses SQL Joins” option, this is interpreted as a join.

So you need to check every QBF to see if it uses a field name on the right side of an “=” operator. If yes, assign it to a local variable and use the local variable in the query.

 

After you’ve checked all occurrences of QUERY BY FORMULA, you’re ready to enable both options.

Enjoy fast searching…