RESOURCES

QUERY BY FORMULA - CLIENT/SERVER

warning: file_get_contents(http://www.telize.com/geoip/54.158.212.93) [function.file-get-contents]: failed to open stream: HTTP request failed! HTTP/1.1 404 Not Found in /var/www/www.4d.com/docs/includes/common.inc(1762) : eval()'d code on line 4.

 

Query by Formula - Client/Server

The speed of Query By Formula execution on the server side under 4D v11 SQL.

 

In 4D 2004, a Query by Formula is executed on the client end. This means that every record must pass over the network to have a formula applied, and to determine if it will be part of the selection. When the logic of the request allows it, 4D v11 SQL will perform queries directly on the server to avoid extraneous loads on the network. Only the selection is returned to the client end, as with a classic query.

 

Let’s go back to the formula for measuring distance used in the test QUERY BY FORMULA (standalone).

 

QUERY BY FORMULA(distance($latitudeRef;$longitude;[customer]latitude;
     [customer]longitude)<10)

Under 4D 2004, the query is executed in 8.6 seconds.

 

Now, under 4D v11 SQL, this query executes in .7 seconds.

 

It’s a 12-fold improvement, subject to variables such as the number of records in the table, as well as network speed, latency, etc. The use of fields or variables doesn't pose any problems, and methods can be used inside the formula. It is then necessary to pass all the variables used in the calculation in a parameter, so that 4D Client can send 4D Server the elements necessary for execution.

 

NOTE: In the case of an older database converted to 4D v11 SQL, this optimization must be authorized in the Compatibility section of the preferences.


 

 

More resources