FWD » Core Development » Database

I have the following SQL generated in H2 for a PresortQuery:

select tt.* 
from tt 
where tt._multiplex = ?
order by tt._multiplex asc, tt.recid asc, tt.recid asc

The ORDER BY has two recid clauses. This may affect the H2 planner, as the order by doesn't match the index. In fact, I tested H2 and I get index sorted for a correct ORDER BY and tableScan for the example above.
I think we can handle this easier from FWD than from FWD-H2. I have an implementation for "incremental indexed sorting" in H2 that could handle this, but the overhead seemed too big for other queries back then.

Please note that such example occurred to me when using PresortQuery over a table with no indexes (except the implicit [multiplex, recid]). For AdaptiveQuery, it looks right.

Danut, please prioritize this if you think it can be fixed by the end of this week. From my POV, there shouldn't be any obvious impediments. Feel free to update on your progress here.

Committed to 7026e, revision 14603.

Review of 7026e, revision 14603

• We can go the extra mile here for performance sake
  • let recIdsSet be null initially
  • only if you intend to add something to the set, consider instantiating it
• Delay crit computation as much as possible
  • fill the set only with the dmo alias to avoid computing the whole <alias>.<field> asc. The set should have only <alias>
  • if the size of the set is equal to the number of components, you can skip the whole for loop
  • otherwise, only check if the set has the buffer.getDMOAlias(). If it doesn't, compute crit and add to the list of sort criteria.
• Fine tuning:
  • do for (i ...) class loop instead of for-each (it is faster as we avoid instantiating an Iterator under the hood).

I added all the changes.
Committed to 7026e revision 14604.

This can be optimized a bit further:

• Avoid using .size() in for - precompute it in another variable.
• Use SortCriterion.isPrimaryKey to detect if this is a primary key faster (instead of retrieving name and doing equals).

Done, I committed the changes on 7026e, revision 14605.

I added it, committed to 7026e, revision 14647.

This can be closed.