Volume 17, Number 3/4/5

Optimization Techniques for SQL+ML Queries: A Performance Analysis of Realtime Feature Computation in OpenMLDB

  Authors

Mashkhal A. Sidiq ¹, Aras A. Salih ² and Samrand M. Hassan ², ¹ Tianjin University, China, ² Nankai University, China

  Abstract

In this study, we optimize SQL+ML queries on top of OpenMLDB, an open-source database that seamlessly integrates offline and online feature computations. The work used feature-rich synthetic dataset experiments in Docker, which acted like production environments that processed 100 to 500 records per batch and 6 to 12 requests per batch in parallel. Efforts have been concentrated in the areas of better query plans, cached execution plans, parallel processing, and resource management. The experimental results show that OpenMLDB can support approximately 12,500QPS with less than 1ms latency, outperforming SparkSQL and ClickHouse by a factor of 23 and PostgreSQL and MySQL by 3.57 times. This study assessed the impact of optimization and showed that query plan optimization accounted for 35% of the performance gains, caching for 25%, and parallel processing for 20%. These impressive results illustrate OpenMLDB’s capability for time-sensitive ML use cases, such as fraud detection, personalized recommendation, and time series forecasting. The system's modularized optimization framework, which combines batch and stream processing without any interference, contributes to its significant performance gain over traditional database systems, particularly in applications that require real-time feature computation and serving. Contributions This study has implications for the need for specialized SQL optimization for ML workloads and contributes to the understanding and design of high-performance SQL+ML systems.

  Keywords

SQL+ML integration, OpenMLDB, real-time feature computation, query optimization, parallel processing, feature store, low-latency databases