Databases Reference
In-Depth Information
The code is more easily broken when we use dynamic SQL because the statements that our
application will generate at runtime are unknown. There could be a case that we have not
foreseen, which generates an error—often a syntactically wrong SQL statement to be exact.
It could take long to test every SQL statement generated by our procedures.
When we submit a stored procedure to the database, there is a compilation phase that not
only ensures that the syntax is correct and but also stores the dependencies of the stored
procedure in the data dictionary. If we are referencing the table
EMPLOYEES
in the static SQL,
the database knows it and stores this information to invalidate the procedure if there is a
change made to the referenced object, for example, an
ALTER
TABLE
, forcing the procedure
to be recompiled the next time it's invoked.
The data dictionary is a read-only set of tables that provides
administrative metadata about the database; it contains
the definitions of every schema object, the amount of space
allocated, the name of users and the privileges and roles granted
to them. Auditing information is also stored in the data dictionary.
With dynamic SQL, the database engine cannot anticipate which objects will be referenced
by the stored procedure, so these objects won't be stored in the data dictionary among the
dependencies of the stored procedure. If there is a change affecting the object—even if it is a
DROP
TABLE
—the database cannot force the procedure to be recompiled, because it doesn't
know that there is a relationship between the object and the procedure.
Moreover, when we use dynamic SQL in a stored procedure, tuning the procedure can be a
nightmare. If someone has coded a procedure similar to the example presented earlier, to
search for the
EMPLOYEES
table, there may be situations where the search does not perform
as expected, but by analyzing the code we cannot clearly point at the cause of slowdown,
because the actual query is built at runtime.
See also
F
A working example
in
Chapter 1
,
Starting with Performance Tuning
F
Using bind variables
in
Chapter 4
,
Optimizing SQL Code
