NASCIMENTO FILHO, D. C.; http://lattes.cnpq.br/3151296501932443; NASCIMENTO FILHO, Dimas Cassimiro do.
Resumen:
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.