Saturday, April 30, 2011

Query forceSelectOrder and forceNestedLoop hints are not saved when you pack and unpack a query

In some situations it's useful to take a query from a form or a calling class, transform this query somehow and use in another part of the application. For instance, you might take a query from a form datasource to process all the records queried by the form, or take a query and transform it to count records that can be fetched by the query (what SysQuery::countTotal() does), or just pass a query object from one tier to another (client to server). In all those cases you would do something like this:

Query newQuery = new Query(oldQuery.pack());

Yes, I know that in case of a form datasource query you should also take care of dynalinks but this is not the point. The point is that you might not get the same execution plan for the resulting query because forceSelectOrder and forceNestedLoop hints are not preserved during query pack/unpack. This is true at least for the AX 2009 SP1 RU7 kernel (5.0.1500.4570) so for now if you use these hints you should not rely solely on the kernel when you copy a query. For this purpose you can create a SysQuery method like this:

/// <summary>
/// Copies a query including a one with dynalinks and hints
/// </summary>
/// <param name="_q">
/// source query
/// </param>
/// <param name="_copyHints">
/// must the forceSelectOrder and forceNestedLoop hints also be copied
/// </param>
public client server static Query copy(
    Query   _q,
    boolean _copyHints = true
    QueryBuildDataSource    qbdsOld;
    QueryBuildDataSource    qbdsNew;
    QueryBuildDynalink      qbdl;
    QueryBuildRange         qbr;
    Query                   ret;
    str                     sq;
    Counter                 n;
    if (!(_q && _q.dataSourceCount()))
        throw error(Error::wrongUseOfFunction(funcname()));
    ret = new Query(_q.pack(false));
    if (_copyHints)
        // Query methods forceSelectOrder() and forceNestedLoop() have mandatory parameters
        // so you cannot use these methods as ordinary properties to find out current values
        sq = _q.dataSourceNo(1).toString();
        if (match(@"^SELECT WITH.* SELECT_ORDER[ ,]", sq))
        if (match(@"^SELECT WITH.* NESTED_LOOP[ ,]", sq))
    qbdsNew = ret.dataSourceNo(1);
    qbdsOld = _q.dataSourceNo(1);
    for (n = 1; n <= qbdsOld.dynalinkCount(); n++)
        qbdl = qbdsOld.dynalink(n);
        // clear all existing ranges for this field
        while (qbdsNew.findRange(qbdl.field()))
        // set the range value by the current dynalinked cursor field value
        qbr = qbdsNew.addRange(qbdl.field());
    return ret;

No comments: