Developers and Architects
Using conditional expressions for creating conditional indexes and partitions in FairCom’s high-performance NAV and SQL database.
© Copyright 2024, FairCom Corporation. All rights reserved. For full information, see the FairCom Copyright Notice.
FairCom provides a powerful expression parser and analyzer for complex conditional expressions that can be defined and evaluated at run time. The filter expression syntax is patterned after the C language syntax for expressions, including order of precedence. An expression interpreted by the expression parser should compile without errors with a standard C compiler.
Conditional expressions can be used for conditional indexing and the creation of partitioned files. These expressions are supported by all of the FairCom DB Low-Level and ISAM APIs and the c-treeDB APIs. They can also be used in custom applications. A callback function provides further flexibility to conditional expressions.
Developers consistently search for fast, efficient ways to retrieve data. While it is relatively easy to acquire huge masses of data, the more data available, the more critical it becomes to quickly and efficiently retrieve specific data.
FairCom data filters are a powerful method to define temporary specific criteria restricting return of only those records meeting these criteria with dynamic conditional expressions. Generally, indexes are also used to reduce unnecessary record retrievals; however, a filter allows "as needed" criteria on demand.
Unlike conditional index expressions, which become a permanent index definition (except for temporary indexes), a specific data filter lasts only until another filter is specified for the same data file or the data file is closed. Further, a specific filter applies only to the calling user: it does not affect retrieval of records by other users. Filters are similar to, though less powerful than, queries.
Data filters in client/server environments can substantially reduce network traffic as only records satisfying a given filter condition are returned from the server. ISAM-level record requests, including sets and batches, skip records failing filter criteria transparently to the user.
For example, given a data file of 100,000 records, if a filter excludes 90,000 of those records, a FirstRecord() - NextRecord() loop results in only 10,000 round-trip calls to the FairCom Server rather than traversing all 100,000 records individually. That's a 90% reduction with measurable performance gains!
Data filters are implemented with the SetDataFilter() API function. When a filter is established, record returns, such as from FirstRecord() and GetRecord(), can be limited to a specific range. Conditional expressions, in conjunction with the schema map and symbolic field names, describe the range of values returned. For example, the expression “(ZipCode >= 65000) && (ZipCode < 66000)” would limit returns to records with 65xxx ZipCodes. While there is a limit of one expression, expressions can be as complex as necessary to accomplish your goals.
For partitioned files, call SetDataFilter() for the host data file and the filter automatically applies to each partition member file.
A powerful way to enhance data filtering is directly with index-level control. Conditional indexes are sophisticated index-level filters. To illustrate, consider the following example:
An application requires an ability to dynamically view all new customers within the past 6 months who have purchased at least $50,000 worth of product.
This type of search can be accomplished with existing c-tree batch operations, set functions, or a compound index. In fact, with low-level c-tree functions, this was a common approach, that is, only adding index keys when necessary, as developers maintained complete control over all data files and indexes.
Each of these methods can be quite efficient, however an index will likely contain information unrelated to a specific request, requiring additional scanning and searching. Using low-level calls further precludes advanced ISAM features and desired functionality.
With conditional index support, an index can be uniquely defined containing only information satisfying exact search criteria, thereby providing a fast and elegant solution.
Conditional index expressions are defined by UpdateConditionalIndex() and GetConditionalIndex(). UpdateConditionalIndex() allows conditional expression associated with an index to be added, removed or changed. GetConditionalIndex() retrieves the current conditional expression for a given index.
With these functions, indexes can be limited to a specific range. The conditional expressions, in conjunction with the schema map and symbolic field names, describe the range of values included. For example, the expression “(ZipCode >= 65000) && (ZipCode < 66000)” would limit the index to entries with 65xxx ZipCodes. While there is a limit of one expression per index, the expressions can be as complex as necessary to accomplish your goals.
A potential disadvantage with this feature is when a data file has a large quantity of indexes. Each additional index requires extra processing when adding, updating or deleting key values, especially in batch processing situations. To minimize overhead caused by numerous indexes, see Faster ISAM Access.
Note: Conditional index support requires ISAM support, RESOURCES, and a DODA in the data file to provide the schema map and symbolic field names.