Some Notes On Fast Formula GET_VALUE_SET Calls

Introduction

The GET_VALUE_SET formula functions allow fast formula developers to specify queries where fast formula database items are not available. The query SQL is defined in a table-based value set. The value set SQL contains placeholders for bind variables. The GET_VALUE_SET function calls specify the value set's value set code, and the bind variables and their values.

These notes covers GET_VALUE_SET function pitfalls, how to implement GET_VALUE_SET calls, and how to troubleshoot GET_VALUE_SET calls.

GET_VALUE_SET Pitfalls

The GET_VALUE_SET functions are a useful extensibility mechanism when used judiciously. However, bad GET_VALUE_SET SQL has been seen to cause a lot of performance issues, especially for batch jobs. A batch job with multiple workers can make many thousands, or even millions, of GET_VALUE_SET calls. A query executing in a tenth of a second, or even a few seconds, might be acceptable in isolation. Huge numbers of slow query executions can result in hours unnecessarily added to processing time, and significantly increase resource usage.