TMSAureliusFirebirdSQL

Proposed solutions to improve SELECT performance for querys generated by TMS Aurelius when using Firebird DBMS.

1. by changing LEFT to INNER joins when there are WHERE conditions applying on the right side.
2. (optional) by changing nested joins into 'parallel' joins

Known issues and limitations:

The LEFT JOINS optmization DO NOT analize the WHERE conditions applying to the right side table. So, on particular cases when the condition may not be enforced, the join will still be altered, and so, it may change the expected result set. Eg.

SELECT * FROM MAIN_TABLE A
LEFT JOIN JOINED_TABLE B ON (B.MAIN_ID = A.MAIN_ID)
WHERE (B.SOME_FIELD=0 OR A.OTHER_FIELD=1)

The key here is the use of OR. In the above case, changing LEFT to INNER can lead to diffent result sets, depending on the queried data.