Improving SQL query performance on embedded devices using pre-compilation
Graeme Douglas, Ramon Lawrence · 2016
Embedded devices are increasingly being used for data collection and on-device data analysis for applications in environmental and infrastructure monitoring, health and wearable computing, and sensor and mobile systems. Processing data on the device rather than transmitting it over a network for analysis reduces energy consumption, network bandwidth usage, and results in more robust and longer functioning devices. A key challenge is enabling efficient data management on devices that may only have a few KBs of memory and limited code space. Previous work has demonstrated that using relational databases is possible on embedded devices with restrictions on the queries that can be processed. In this work, we eliminate one of the key barriers to using relational technology on embedded devices, which is the massive overhead involved in SQL parsing and translation that can take up to 50% of the code and memory resources on the device. Our approach allows developers to continue to use relational APIs and SQL during development which are then pre-compiled when deployed on the device. This produces all of the benefits of relational systems without the on-device overhead and limitations. Experimental results demonstrate that query pre-compiling can reduce query parse times by up to 90% and on-device execution times by up to 50%. The technique is applicable to a wide range of embedded systems and databases.