RESOURCES

QUERY BY FORMULA - STANDALONE

 

Query by Formula - Standalone

Speed improvements in Query By Formula under 4D v11 SQL.

 

In 4D 2004, a formula is calculated for each record sequentially. 4D v11 SQL analyzes formulas beforehand to optimize queries. The syntactic analyzer can identify parts of the formula that can be optimized to reduce the sequential parts of the query.

 

Our test case involves the georeferencing (location by degree of latitude and degree of longitude) of customers. We’d like to find the customers who are within 10km of a particular point. Say we have a function, "Distance," that returns the number of kilometers (as the crow flies) between two points.

 

Note: we are using the Pythagorean Theorem, which is fast and short but only accurate to a distance up to 20 km (12 miles). For global distances we recommend using the Haversine formula.

 

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

 

The Query by Formula on 10,000 customers yields 7 records in 556 milliseconds in 4D v11 SQL… which is not significantly better than in 4D 2004, as the search is sequential out of necessity, and ends up sweeping the entire file. But if we pre-filter our query by limiting it to the city corresponding to our starting point, we get very different results:

 

QUERY BY FORMULA([customer];([customer]city=$city) & (distance($latitude;
        $longitude;[customer]latitude;[customer]longitude)<10))

 

In effect, 4D v11 SQL discerns that it can reduce the perimeter of search by taking advantage of the indexed field [customer]City. In this case, it restricts us to 42 records.

 

The rest of expression is then assessed on the records within this selection, and this query also yields 7 records, but in 8 milliseconds – that’s 70 times as fast!

 

The difference here with 4D 2004 is huge: Unlike 4D v11 SQL, 4D 2004 would not have been able to create a shorter preselection to restrict its sequential research to the desired city. The entire would have been combed for data, like in the first research.

 

The performance benefits of QUERY BY FORMULA depend, of course, on the type of request, but developers can count on 4D v11 SQL’s query analyzer to automatically find all possible optimizations.


 

 

More resources