SDBQL Benchmarks
Performance characteristics of SDBQL built-in functions. Understand time complexity, optimize your queries, and learn when to use indexes for large datasets.
SDBQL Function Performance
This page documents the performance characteristics of SDBQL built-in functions. Performance is categorized as Fast (O(1)-O(n)), Moderate (O(n log n)), or Slow (O(n²) or worse on large datasets).
Fast
O(1) to O(n) - Safe for large datasets
Moderate
O(n log n) - Noticeable on large datasets
Slow
O(n²) or worse - Avoid on large collections
String Functions
| Function | Complexity | Category | Notes | |
|---|---|---|---|---|
| SUBSTRING | O(1) | Fast | Slice operation, no allocation | |
| LEFT / RIGHT | O(1) | Fast | Slice operation | |
| LENGTH | O(1) | Fast | Returns byte count | |
| STARTS_WITH | O(1) | Fast | Prefix check with memchr | |
| ENDS_WITH | O(1) | Fast | Suffix check | |
| CONTAINS | O(n) | Fast | memchr optimization for small patterns | |
| TRIM / LTRIM / RTRIM | O(n) | Fast | Iterates whitespace only | |
| UPPER / LOWER | O(n) | Fast | Unicode-aware case conversion | |
| REPLACE | O(n) | Fast | Single pass, allocates result | |
| CONCAT | O(n) | Fast | Allocates for final string | |
| SPLIT | O(n) | Fast | Creates array of substrings | |
| REVERSE | O(n) | Fast | Full string traversal | |
| REGEX_TEST / REGEX_MATCHES / REGEX_REPLACE (cached) | ~29-39 ns | O(n) | Fast | All regex functions use global cache (1000 patterns). First call compiles (~336µs), subsequent calls reuse. ~8,500x faster than uncached. |
Array Functions
| Function | Complexity | Category | Notes |
|---|---|---|---|
| FIRST / LAST | O(1) | Fast | Direct access |
| NTH | O(1) | Fast | Direct index access |
| LENGTH | O(1) | Fast | Array metadata |
| CONTAINS | O(n) | Fast | Linear search; use index for frequent queries |
| POSITION / INDEX_OF | O(n) | Fast | Linear search with index return |
| PUSH / POP | O(1) amortized | Fast | End of array operation |
| UNSHIFT / SHIFT | O(n) | Fast | Requires shifting elements |
| SLICE | O(k) | Fast | k = number of elements copied |
| MAP / FILTER | O(n) | Fast | Single pass |
| REDUCE | O(n) | Fast | Single pass aggregation |
| FLATTEN | O(n) | Fast | Depth-limited flattening |
| SORTED / SORT | O(n log n) | Moderate | TimSort implementation |
| UNIQUE | O(n) average | Fast | HashSet deduplication |
| UNION / INTERSECTION / MINUS | O(n * m) | Moderate | Set operations on two arrays |
| APPEND | O(n) | Fast | Concatenation |
Math Functions
| Function | Complexity | Category | Notes |
|---|---|---|---|
| ABS / FLOOR / CEIL / ROUND | O(1) | Fast | Direct IEEE 754 operations |
| SQRT / POW / EXP / LN | O(1) | Fast | Math intrinsics |
| SIN / COS / TAN / ASIN / ACOS / ATAN | O(1) | Fast | Trigonometric intrinsics |
| MIN / MAX / CLAMP | O(1) | Fast | Direct comparison |
| SUM / AVG / MIN / MAX on arrays | O(n) | Fast | Single pass aggregation |
| COUNT_DISTINCT | O(n) average | Fast | HashSet-based |
| MEDIAN | O(n log n) | Moderate | Requires sorting |
Crypto Functions
| Function | Complexity | Category | Notes |
|---|---|---|---|
| MD5 / SHA256 / SHA512 | O(n) | Fast | Optimized native implementations |
| BASE64_ENCODE / BASE64_DECODE | O(n) | Fast | Linear encoding/decoding |
| HMAC_SHA256 | O(n) | Fast | Keyed hash |
| ARGON2_HASH | O(2^t) | Slow | Intentionally slow (DoS protection, t=3 default) |
DateTime Functions
| Function | Complexity | Category | Notes |
|---|---|---|---|
| NOW / DATE_NOW | O(1) | Fast | System time call |
| DATE_YEAR / MONTH / DAY / HOUR / MINUTE / SECOND | O(1) | Fast | Extract from timestamp |
| DATE_ADD / DATE_SUBTRACT / DATE_DIFF | O(1) | Fast | Duration arithmetic |
| DATE_FORMAT / DATE_ISO8601 | O(1) | Fast | Formatting with chrono |
| TIME_BUCKET | O(1) | Fast | Integer division |
Geo Functions
| Function | Complexity | Category | Notes |
|---|---|---|---|
| DISTANCE / GEO_DISTANCE | O(1) | Fast | Haversine formula |
| GEO_WITHIN | O(n) | Fast | Ray casting algorithm, n = polygon vertices |
| GEO_CONTAINS | O(n) | Fast | Point-in-polygon check |
JSON Functions
| Function | Complexity | Category | Notes |
|---|---|---|---|
| JSON_PARSE | O(n) | Fast | Serde parsing overhead |
| JSON_STRINGIFY | O(n) | Fast | Serde serialization |
| JSON_POINTER | O(depth) | Fast | Depth of path traversed |
Type Check Functions
| Function | Complexity | Category | Notes |
|---|---|---|---|
| IS_STRING / IS_NUMBER / IS_BOOL / IS_NULL / IS_ARRAY / IS_OBJECT | O(1) | Fast | Pattern matching on Value enum |
| IS_INTEGER / IS_EMPTY | O(1) | Fast | Direct type check |
Performance Tips
Avoid on Large Collections
REGEX_*(first call with new pattern) - ~336µs compile timeCONTAINSon large strings - Consider fulltext indexesUNION / INTERSECTION / MINUSon large arrays - O(n*m)
Use Indexes for Frequent Queries
- Equality filters:
FILTER doc.field == value - Range queries:
FILTER doc.age >= 25(use Persistent index) - Text search: Fulltext index for
CONTAINSpatterns
Fast Operations
- Array access:
FIRST,LAST,NTHare O(1) ~0.8ns - Math: All basic operations are O(1) - nanosecond speed
- Type checks:
IS_*functions are O(1) REGEX_TEST,REGEX_MATCHES,REGEX_REPLACE(cached) - ~39ns after first compile, 8,500x faster than uncached
Regex Cache
SDBQL uses a global regex cache that stores up to 1000 compiled patterns. The first call to any regex function with a new pattern incurs compilation overhead (~336µs), but all subsequent calls with the same pattern are ~8,500x faster (~39ns). This makes repeated regex operations nearly as fast as simple string functions. Cached functions: REGEX_TEST, REGEX_MATCHES, REGEX_REPLACE.