Uma abordagem para análise estática automática de procedimentos armazenados em bancos de dados.

Abstract

Stored Procedures are commonly used by applications to access and manipulate data in databases. If procedures are inefficiently implemented, excessive delays are passed to the application layer and can bring significant financial losses to enterprises. If procedures are implemented using bad programming practices, it may become hard for a developer to read and understand them. Considering that a significant part of the business logic of the applications can be implemented as stored procedures, the reduction of these problems using manual inspections may become a costly, error-prone and discouraging process to the developers. In this work, we propose an approach, based on static analysis of source code, to check the conformity of stored procedures with predefined efficiency and quality guidelines. The approach was instantiated to a specific database programming language and evaluated in three different ways. First, we measured the impact of the appliance of efficiency guidelines on the execution time of stored procedures. Considering the highest processing loads under test, the investigated guidelines led to optimization gains greater than 80%. Moreover, a case study, using real open source projects, was conducted to evaluate the efficacy (amount of reported warnings) and efficiency (automatic analysis time) of the proposed approach. In this process, 299 warnings were reported after the analysis of the selected procedures (∼2KLoC) and, for doing so, less than 7 seconds of automatic analysis were required. Finally, we performed experiments, in the context of an industrial project, in order to compare the automatic analysis with manual inspections performed by developers. In these experiments, the automatic approach presented a superior efficacy and efficiency in all observed treatments.