Feasibility of database query composition and reuse with Ecto
Kunnas, Mika (2021)
2021
Tietotekniikan DI-ohjelma - Master's Programme in Information Technology
Informaatioteknologian ja viestinnän tiedekunta - Faculty of Information Technology and Communication Sciences
This publication is copyrighted. You may download, display and print it for Your own personal use. Commercial use is prohibited.
Hyväksymispäivämäärä
2021-05-24
Julkaisun pysyvä osoite on
https://urn.fi/URN:NBN:fi:tuni-202104273924
https://urn.fi/URN:NBN:fi:tuni-202104273924
Tiivistelmä
Software reuse is a common practice in software development, and it is a widely studied topic. However, there is not much research on how to take advantage of reuse from the perspective of SQL queries and data. When many features of a software require similar set of data from the database, there is an opportunity for reuse.
The workforce optimization software at RELEX combines data from different database queries to avoid writing similar queries from scratch. This is done by chaining calls to functions that each execute a database query passing the data to the next query as an input. The downsides of this approach is that the queries are not tailored to each use case, and that the number of queries executed is not explicit. The queries are built with Ecto, which is a query generator for Elixir.
This thesis investigated query composition as an alternative way of query reuse. The idea of query composition is to build small query blocks that can be combined in different ways to form the final single query. Two case queries in the application were chosen and rewritten using query composition. Based on the predefined criteria, such as performance, and experience of working with query composition, the proposed method was evaluated against the current method to determine whether query composition could generally be recommended over the current method. The research was based on the engineering method.
The new implementation outperformed the current implementation, on average, by over 35% in three of the four performance test cases, being 12% slower in the last case. The new implementation required 48 source lines of code versus 53 lines in the current implementation. Based on the implementation project, query composition provides many important benefits over the current method, such as removing the need for manual transaction control and enabling use case specific tailored queries. Thus, query composition can be recommended over the current method of query reuse. Future research topics include investigating the validity of the results of this research with other technologies similar to Ecto.
The workforce optimization software at RELEX combines data from different database queries to avoid writing similar queries from scratch. This is done by chaining calls to functions that each execute a database query passing the data to the next query as an input. The downsides of this approach is that the queries are not tailored to each use case, and that the number of queries executed is not explicit. The queries are built with Ecto, which is a query generator for Elixir.
This thesis investigated query composition as an alternative way of query reuse. The idea of query composition is to build small query blocks that can be combined in different ways to form the final single query. Two case queries in the application were chosen and rewritten using query composition. Based on the predefined criteria, such as performance, and experience of working with query composition, the proposed method was evaluated against the current method to determine whether query composition could generally be recommended over the current method. The research was based on the engineering method.
The new implementation outperformed the current implementation, on average, by over 35% in three of the four performance test cases, being 12% slower in the last case. The new implementation required 48 source lines of code versus 53 lines in the current implementation. Based on the implementation project, query composition provides many important benefits over the current method, such as removing the need for manual transaction control and enabling use case specific tailored queries. Thus, query composition can be recommended over the current method of query reuse. Future research topics include investigating the validity of the results of this research with other technologies similar to Ecto.