Repeat Queries
To reduce processing overhead for frequently executed queries, EQUEL allows you to specify retrieve, replace, append, or delete statements as "repeat queries." The first time a repeat query is executed, the DBMS Server retains the query execution plan (QEP). For subsequent executions of the repeat query, the retained QEP is used. For non-repeated queries, the DBMS Server must recreate the QEP every time the query executes. The first execution of a repeat query is slightly slower than an ordinary non-repeat query, because of the effort required to store the query plan. On subsequent executions, the query runs significantly faster than a non-repeat query.
The DBMS Server stores one QEP for each repeat query. To minimize the number of QEPs that must be managed, you must place code containing repeat queries in separate modules. When running applications containing repeat queries, each user has its own set of QEPs.
Variables containing values that can change from one pass to the next must be flagged by the "@" character. Any variable not marked as a parameter variable has its value fixed in the execution plan at the time the query is first executed. Typically, parameter variables occur in the where clause of queries, and the target list of append and replace statements. Result variables in the target list of a retrieve statement must not be flagged.
Flagged variables can substitute only for constants in the query. They must not contain qualifications (an entire "where clause") or the names of tables, range variables, or columns. The maximum number of flagged variables in one query is 127.
The following program illustrates the use of repeat queries:
begin program
## ename character_string(26)
## salary float
## eno integer
quit character_string(10)
responsecharacter_string(10)
count integer
## ingres personnel
## range of e is emp
loop while quit = "no"
print "enter an employee number: "
read eno from terminal
print "retrieving data ..."
count = 0
## /* in the following query, eno is flagged */
## repeat retrieve (ename = e.empname,
## salary = e.#salary)
## where e.empnum = @eno
## {
count = count + 1
print ename, salary
## endretrieve
## }
if count 0 then
print "delete that record? [yes or no]: "
read response from terminal
if response = "yes" then
## repeat delete e where e.empnum = @eno
end if
else if count = 0 then
print "no rows matched that employee number"
print "adding employee number to table"
## repeat append to employee (empnum = @eno)
end if
print "inquire about another employee? [yes or no]: "
read quit from terminal
end loop
## exit
end program