c-treeACE SQL Query Performance Improvements

Several c-treeACE SQL queries were reported to have poor performance. Performance was improved by including outer joins in join order optimization. (whereas before, the optimizer was mixing INNER and OUTER joins.) Additional improvements were made in NL join optimization such that if there is only one tuple coming from the left branch there is no need to introduce a 'Project into temp' node on the right branch.

Analysis of these queries also revealed common areas that could be improved within c-treeACE SQL data handling functions. These include the following additional modifications:

• Avoid unnecessary memory allocations and releases for temporary data copies.
• Reduce the number of calls needed when retrieving a field from a record.
• Avoid unnecessary context switch calls that result in no operation.
• Reduce the frequency of checking user and transaction states. These checks are performed for features such as query timeouts and killed users. Abandoned transactions due to dynamic dumps or a server quiesce can also lead to invalid transaction states that must be checked. These checks have been reduced to no more than once per second.