Structuring Queries

Improving query structures can make your queries run faster.

In some cases, command statements that include subqueries can also be formulated as joins and may run faster.
If you group on multiple columns in a GROUP BY clause, list the columns by descending order by number of unique values if you can. This will give you the best query performance.
You can improve performance by using an additional column to store frequently calculated results.

Enhancing ORDER BY Query Performance
Using multicolumn HG indexes can enhance the performance of ORDER BY queries.
Improved Subquery Performance
Use SUBQUERY_FLATTENING_PREFERENCE and SUBQUERY_FLATTENING_PERCENT to control subquery flattening.
Using Caching Methods
Set the SUBQUERY_CACHING_PREFERENCE option to choose caching methods for a correlated subquery.

Parent topic: Queries and Deletions

Created May 23, 2013. Send feedback on this help topic to Technical Publications: pubs@sybase.com