As I recall, "query by formula" and "order by formula" were optimized in v11 and onwards. Quoting from the Language Reference
*4D Server:* Beginning with version 11 of 4D Server, these commands are run on the server, which optimizes their execution. Keep in mind that when variables are called directly in queryFormula<http://doc.4d.com/4D-Language-Reference-13.2/Queries/QUERY-BY-FORMULA.301-1077481.en.html#>, the query is calculated with the value of the variables on the client machine. For example, the statement *QUERY BY FORMULA*([mytable];[mytable]myfield=myvariable) will be run on the server but with the contents of the myvariable variable of the client machine. On the other hand, this principle is not applied for formulas using methods that, themselves, call variables (the values of the variables are evaluated on the server). In this context, it may be advisable to use the "Execute on server" method attribute, which allows the method to be executed on the server while passing parameters (variables) to it (see the Design Reference manual). In previous versions of 4D Server, these commands were executed on client machines. For compatibility's sake, this functioning is maintained for databases converted to version 11. A compatibility preference and a selector of the SET DATABASE PARAMETER<http://doc.4d.com/4D-Language-Reference-13.2/4D-Environment/SET-DATABASE-PARAMETER.301-1077941.en.html> command can nevertheless be used to adopt the functioning of version 11 (execution on the server) in converted databases The same applies to "order by formula" *4D Server:* Beginning with version 11 of 4D Server, this command is executed on the server, which optimizes its execution. Note that when variables are called directly in the expression<http://doc.4d.com/4D-Language-Reference-13.2/Queries/ORDER-BY-FORMULA.301-1077476.en.html#>, the sort is calculated with the value of the variable on the client machine. On the other hand, this principle does not apply for formulas using methods that, themselves, call variables (the values of the variables are evaluated on the server). In this context, it may be advisable to use the "Execute on server" method attribute that allows a method to be executed on the server while passing parameters (variables) to it (see the Design Reference manual). In previous versions of 4D Server, this command was executed on the client machines. For compatibility's sake, this functioning is maintained in databases converted to version 11. A compatibility preference and a selector of the SET DATABASE PARAMETER<http://doc.4d.com/4D-Language-Reference-13.2/4D-Environment/SET-DATABASE-PARAMETER.301-1077941.en.html>command can nevertheless be used to adopt the functioning of version 11 (execution on the server) in these databases. *your mileage may vary* - I hope this help? On Tue, Nov 27, 2012 at 8:28 AM, Aparajita Fishman < [email protected]> wrote: > 'order by formula' has to load every record in the selection no matter > what the expressions are, because every expression expects the correct > record to be current. > > You can't possibly expect me to do some kind of heuristics on the > expressions and optimize accordingly, especially in this case where you are > using a field of a table as the key for a collection. > > I specifically said on this list and in the documentation that there is a > potential performance penalty on Remote. > > On Nov 27, 2012, at 4:45 AM, Peter Gutbrod <[email protected]> wrote: > > > Out of interest: > > > > What happens internally, if I do an "order by formula" on a collection, > > which has an ID of the sort table as the key? > > > > Aka. > > > > order by formula(*; [contacts]; $collection{[contacts]ID}) > > > > Does is load each record of the current selection or does is just copy > > [contacts]ID into an array and use that for the sort? > > > > Would make a big difference in performace if you run it on Remote. > > > > Peter > > > > > > > >> Hello, > >> > >> The latest and greatest version of Active4D, the ultimate 4D web > development > >> environment, is now available for download. Active4D v6.0r8 is a feature > >> release: > >> > >> - ORDER BY FORMULA now has an option to be executed within Active4D¹s > context, > >> which allows access to Active4D¹s full execution environment within the > order > >> by expressions. > >> > > > > > > > > _______________________________________________ > > Active4D-dev mailing list > > [email protected] > > http://list.aparajitaworld.com/listinfo/active4d-dev > > Archives: http://active4d-nabble.aparajitaworld.com/ > > Regards, > > Aparajita > > _______________________________________________ > Active4D-dev mailing list > [email protected] > http://list.aparajitaworld.com/listinfo/active4d-dev > Archives: http://active4d-nabble.aparajitaworld.com/ > -- m|a _______________________________________________ Active4D-dev mailing list [email protected] http://list.aparajitaworld.com/listinfo/active4d-dev Archives: http://active4d-nabble.aparajitaworld.com/
