- author = "James Callan and Justyna Petke",
- title = "Optimising {SQL} Queries Using Genetic Improvement",
- booktitle = "GI @ ICSE 2021",
- year = "2021",
- month = "30 " # may,
- editor = "Justyna Petke and Bobby R. Bruce and Yu Huang and Aymeric Blot and Westley Weimer and W. B. Langdon",
- publisher = "IEEE",
- address = "internet",
- pages = "9--10",
- keywords = "genetic algorithms, genetic programming, genetic improvement, SQL, query optimisation",
- isbn13 = "978-1-6654-4466-8/21",
-
URL = "
https://geneticimprovementofsoftware.com/paper_pdfs/gi2021icse/callan_gi-icse_2021.pdf",
-
video_url = "
https://www.youtube.com/watch?v=WopftjIYPgs&list=PLI8fiFpB7BoKDaxvS7SQp0iA7fN7rrvDD&index=5",
-
video_url = "https://www.youtube.com/watch?v=A-QX5dlsKUI&list=PLI8fiFpB7BoKDaxvS7SQp0iA7fN7rrvDD&index=6",
-
video_url = "https://www.youtube.com/watch?v=nqBWLbtq6yQ&list=PLXTjhGKkSnI-se7uQneCX-pEDiDrQ7TIS&index=7",
-
DOI = "
doi:10.1109/GI52543.2021.00010",
- size = "2 pages",
- abstract = "Structured Query Language (SQL) queries are ubiquitous in modern software engineering. These queries can be costly when run on large databases with many entries and tables to consider. We propose using Genetic Improvement (GI) to explore patches for these queries, with the aim of optimising their execution time, whilst maintaining the functionality of the program in which they are used. Specifically, we propose three ways in which SQL JOIN statements can be mutated in order to improve performance. We also discuss the requirements of software being improved in this manner and the potential challenges of our approach.",
-
notes = "refers to
https://www.microsoft.com/en-gb/sql-server/sql-server-downloads
Read only queries, join. 3 mutations reorder SQL select/join statements, delete join statements, change join type.
Aim minimise number of rows returned by join.
Testing. Representative databases, but bigger database means heavier load on computer and network. Multiobjective. Test the program not the query. Faster query can still give same result.
Video nqBWLbtq6yQ James Callan
1:47 Discussion: Chair: Bobby R. Bruce (balck). Answers: James Callan(green) and Justyna Petke (red).
2:19 Q: W. B. Langdon GI versus conventional SQL query optimisation? A: James Callan existing join order optimisation done at run time and can go wrong. Offline GI query optimisation may be more effective. Database may be over engineered, eg too many columns in table. GI has more scope to get desired result, ie freedom to not replicate query exactly.
3:40 Q: Myra B. Cohen. A: GI good as can adapt as database moves over times.
4:22 Q: Alexandre Bergel, genetic programming. A: GI applicable to long and complex queries as seen in existing human written queries
5:43 Q: Westley Weimer, fitness over fitting. A: Most useful on large table and complex queries, ideally an SQL query which is used multiple times in a program.
6:54 Q: Giovani Guizzo, swapping join with where?
7:38 Q: Westley Weimer, map SQL back into relational algebra, mutate/edit in relational algebra and then transform patches into SQL. A: James Callan A: Justyna Petke motivation integration into existing or extended GI tools.
9:44 Q: Bobby R. Bruce, multi-objective. A: James Callan query time and size of result (bandwidth usage)
part of \cite{Petke:2021:ICSEworkshop} http://geneticimprovementofsoftware.com/events/icse2021.html",
