The SM Lab team, responsible for digital services at Sportmaster, faced performance challenges in their product availability check system. The system must verify product availability across 15 geographic zones and 3 availability types, requiring 4500 key checks per request.
Initial tests on Oracle Exadata showed that standard SQL queries with large 'in' predicates failed under load. Using 'json_table' improved performance to 2000 requests per second with a 99th percentile response time of 250–300 ms, though some requests still timed out.
The team identified that high-volume queries caused significant pressure on the database buffer cache. They concluded that splitting large SQL queries into smaller parallel requests and aggregating results in application memory could be a viable optimization strategy.
To optimize memory usage and search speed, the developers explored bit arrays. While classic bit arrays are efficient for dense data, they become wasteful with sparse data, as demonstrated by an example where four items with large IDs required 16 words of memory, with 13 words remaining empty.
