The emergence of database-as-a-service platforms has made deploying database
applications easier than before. Now, developers can quickly create scalable
applications. However, designing performant, maintainable, and accurate
applications is challenging. Developers may unknowingly introduce anti-patterns
in the application's SQL statements. These anti-patterns are design decisions
that are intended to solve a problem, but often lead to other problems by
violating fundamental design principles.
In this paper, we present SQLCheck, a holistic toolchain for automatically
finding and fixing anti-patterns in database applications. We introduce
techniques for automatically (1) detecting anti-patterns with high precision
and recall, (2) ranking the anti-patterns based on their impact on performance,
maintainability, and accuracy of applications, and (3) suggesting alternative
queries and changes to the database design to fix these anti-patterns. We
demonstrate the prevalence of these anti-patterns in a large collection of
queries and databases collected from open-source repositories. We introduce an
anti-pattern detection algorithm that augments query analysis with data
analysis. We present a ranking model for characterizing the impact of
frequently occurring anti-patterns. We discuss how SQLCheck suggests fixes for
high-impact anti-patterns using rule-based query refactoring techniques. Our
experiments demonstrate that SQLCheck enables developers to create more
performant, maintainable, and accurate applications.Comment: 18 pages (14 page paper, 1 page references, 2 page Appendix), 12
figures, Conference: SIGMOD'2