There is an ongoing discussion in the community about whether or not it is better to use SQL commands vs 4D commands for best performance. It is important to first acknowledge that the primary reasons to use SQL over 4D have nothing to do with performance. There are many advantages to 4D's SQL implementation like the ability to perform operations without disturbing the current selection or current record. There are also things that are simply easier to express in SQL rather than 4D commands (the documentation includes examples of this).

However it can also be noted that SQL commands are sometimes "slower" than equivalent 4D commands.

4D commands tend to be very specialized, they perform an "atomic" task. SQL pretty much relies exclusively on SELECT so SELECT tends to be generic in its implementation.

For example, the commands ALL RECORDS and SELECTION TO ARRAY are basically just memory copy operations, so they tend to be very fast. ALL RECORDS is more or less a free operation, especially if the data is in the cache. SELECTION TO ARRAY just copies the block of data in the cache into arrays.

The performance of SQL commands has mostly to do with parsing and validation but here's a high-level breakdown of where execution time is spent for a SELECT:

• Parsing (7%)
• Validation (does the table exist, does the column exist?) (15%)
• Session management (7%)
• Language bindings (2%)
• Data access (9%)

Remember that both SQL and 4D commands talk to the same database engine and, therefore, receive the same optimizations at that level. But each language has its own query analyzer; i.e. the performance difference comes from the analyzers.

Note that in 4D v12 the SQL implementation was optimized to improve performance vs 4D commands (i.e. v12 is generally faster than v11).

When is SQL faster? In general SQL is better at expression calculation/aggregate functions. Also server-side requests that take a long time, especially if the 4D version would be sending a lot of data over the wire.

These are the major areas of major improvement in v12:

• Local data access.


• Remote data access.


• Expression calculation.


• Statement caching (skips the parsing and validation step).

Note: compiling the database does not affect the performance of SQL because in the end the SQL statement is still text. Do not expect SQL execution time to decrease in compiled mode.