Predicting SQL Query Execution Time for Large Data Volume
Rekha Singhal, Manoj Karunakaran Nambiar · 2016
In a production system, increase in data size will increase the execution time of the application's SQL queries and degrade its performance. Tuning SQL queries in production requires additional efforts and cost. Time constraints during application development do not permit testing SQL queries with high data volumes. Having the capability to predict SQL query execution time for large data volumes can alert the developers to tune queries or database design upfront, in such scenarios. Application developers may use 'cost' of SQL query as given by optimizer based relational databases to estimate the SQL query execution time for large data sizes. However, the 'cost' based models may lead to large estimation errors as discussed in this paper. We have presented a modular approach of estimating SQL query execution time for high data volumes using measurements at low data volume. A compound SQL query execution plan is mapped to a sequential execution of a set of elementary steps. The execution time of a SQL query in isolation is predicted as summation of estimated execution time of all its elementary steps. We have built analytical models for estimating execution time of different IO access, DB cache access and SQL operators as function of data size for each such step. The proposed model dynamically adapts itself to the structure of the query execution plans and characteristics of the underlying hardware. We have evaluated the model by generating synthetic queries for all combinations of elementary steps for a range of data sizes. The model has also been validated with TPC-H benchmarks and three real life applications. The proposed model shows an average prediction error to be within 10%.