Tuesday, June 22, 2010

ADS 10 Tip #17 – Scalar Functions

Advantage includes two distinct data engines an expression engine and an SQL engine. This gives developers the flexibility to choose the access method which provides the best speed and functionality. The Advantage SQL engine uses traditional filters in order to optimize performance. These filters are based on indexes which are created with the expression engine. Therefore for the best performance you should use SQL functions that have complimentary expression engine functions.

We have added many of the SQL functions to the expression engine which can make many queries more efficient. Many of these new functions involve date/time functionality for example: WEEK, QUARTER and MONTH. The image below shows a comparison of the SQL execution plans from version 9 and version 10. With version 10 I was able to create an index on MONTH( OrderDate ) which optimizes the query.

SQL Execution Plan

For a list of all the functions added to the expression engine refer to the What's New in Advantage 10.

No comments: