The difference between embedded SQL and dynamic SQL is in when Caché resolves the SQL:
For dynamic SQL, the SQL is parsed at run time when it is prepared. This allows the SQL to be customized based on feedback from the application. This flexibility comes with a cost. Parsing the SQL is additional overhead, and the first time a dynamic query is run, Caché needs to generate the cached query for the SQL. The cached query is designed to be reused, so you only have this second cost once for each SQL statement.
Embedded SQL is parsed and compiled at routine/class compile time, so it cannot be changed by input from the application. Values in the WHERE clause can be changed, of course, but, at run time, you cannot add new conditions and you cannot change the SELECT list.
While the gap in performance between dynamic SQL and embedded SQL is getting smaller, embedded is still faster. If you do not need dynamic SQL but still want to use result sets instead of dynamic SQL, you could define the SQL in a class as a class query or as a class method that returns a result set. This removes the overhead of parsing the SQL at run time.
