SPECIAL IMPLEMENTATION CONSIDERATIONS - Database Design and Development: An Essential Guide for IT Professionals - page 402

CONNECT (database, user, password)

SEND (“update employee ……”)

EXECUTE ()
STATUS CHECK ()

OK

SEND (“SELECT * FROM EMPL ……..”)
EXECUTE ()
GETROW ()
(“Bill Jones, …………….”)
…………………………………..
…………………………………..
DISCONNECT ()

DBMS

Figure 13-14 Sample program with SQL CLI.

minimum cost in terms of resource utilization and must result in minimum response
time for the query.
Relational DBMSs translate a nonprocedural SQL data request into an optimal
execution plan using computer science optimization techniques. We will briefly
examine the various steps necessary to parse, analyze, prepare an optimal query
plan, and execute an SQL query. You will gain an insight into the mechanics of query
processing and understand how various components of a relational DBMS work
together to process a query. Query optimization and execution techniques are major
and extensive topics. For our purposes, we need not go into the complexities of the
methods and algorithms. You need a broad and general view of the topics.

Query Processing Steps

Consider what happens to a query once it is written and presented for execution.
What are the distinct steps, and what functions does DBMS carry out in each of
these steps? How do these steps finally produce the result of the query in an optimal
manner?
Let us begin with a diagram. Figure 13-15 highlights the major steps and
indicates the functions in each step.
Note the following highlights of each step shown in the figure and the functions
of each DBMS component:

Scanning. Carefully examine the text of the query and identify the language
components such as SQL keywords, names of relations, names of attributes, built-in
functions, and operators.

Parsing. Verify that the query syntax conforms to SQL syntax grammar.

Validating. Check and validate relation and attribute names and verify the seman-
tics of the query.