Repeated Queries
4GL provides an optimizing repeated version of the SQL database query statements: select, insert, update and delete.
The syntax for repeated queries varies from the standard data manipulation commands only in that the word repeated precedes the statement:
[repeated] select|insert|update|delete statement
Repeated queries run faster the second and subsequent times they are issued, compared to a query coded without the repeat feature. This can be a major benefit for applications in which the same query is executed repeatedly over the course of the user's session, varying only in that the values supplied to the fields or items in the where clause differ from one execution to another.
On the first execution of a repeated query, Ingres stores an optimized execution plan for the query. On subsequent executions, the work of parsing and optimizing the query need not be redone. The first execution of a repeated query is slightly slower than a nonrepeated query because of the work required to store the query's execution plan. On subsequent executions, the query runs significantly faster than a nonrepeated query, because the parse and optimize steps are skipped.
Repeated queries require additional memory overhead in the database server to store the query execution plan. That memory stays allocated for as long as the current session remains active. However, because all application users share the memory, multiple users can optimize resources.
Repeated statements are often implemented in a procedure or in a loop, where the same query can be executed many times. Because the repeated query is optimized separately as it reappears in different places in the 4GL code, it is important to modularize repeated query code.
The best candidates for repeated queries are queries run three or more times during the life of the application. Queries run inside an unloadtable loop and those in a submenu are often good candidates.
Restrictions
You cannot repeat the following types of queries:
• Queries that use the 4GL qualification function. For example:
/* incorrect */
repeated delete from employees where
qualification (...);
• Queries that specify an entire where clause as a variable. For example:
/* incorrect */
repeated delete from employees where :w;
• Queries that use database table names or column names as variables. For example:
/* incorrect */
repeated delete from :t where
last_name = 'Smith';
• Queries that specify the sort order of a column ("asc" or "desc") as a variable. For example:
/* incorrect */
repeated select * from employees
order by last_name :direction
If you specify any of the above types of queries in a 4GL application, the compiler issues a warning and ignores the repeated qualifier.
Examples
The following are examples of repeated queries.
This example selects all the fields on the form from the table where the value of col is greater than the value in var. Each time the select is run, a new value for var can be specified.
repeated select * from itable
where col > :var;
The next example updates information on an employee. Each time the query is run, a new employee number is specified along with data currently on the form.
repeated update employees
set * = :emptbl.all
where empno = :empno;
The following example deletes a sample test result. Each time the query is run, a new value for sample number and test result code is used.
repeated delete results
where sample_no = :s_no
and res_code = :res_code;
The last example inserts data from the form into the database table customers.
repeated insert into customers (*)
values (custform.all);
Minimizing the Number of Repeated Queries
You can minimize the number of repeated queries in an application by running them from 4GL procedures. Then any frame in the application that needs to run a query, such as inserting a row in a particular table, can call the 4GL procedure that does the insert and pass it the information from the new row.
The following examples demonstrate ways to minimize the number of repeated queries through the use of records and 4GL procedures. Several examples follow in which 4GL frame code calls 4GL procedures and passes them a record. These routines deal with the database table Emp, which is keyed unique on the Emp_id column.
Listings of the 4GL procedures, InsertEmp, SelectEmp, and UpdateEmp, follow the 4GL frame code which calls them. The examples use two integer global constants, OK and Fail. OK is defined to a value of 1 (one) and Fail is defined to a value of 0 (zero).
Insert a row: This example calls the 4GL procedure InsertEmp to add information about a new employee:
if (insertemp(r_emp = r_emp) != ok) then
rollback;
message 'Error inserting data ' +
varchar(:r_emp.emp_id)
with style = popup;
resume;
else
commit;
endif; /*insert accomplished */
Select a row: This example calls the 4GL procedure SelectEmp to retrieve information about an employee:
/* r_emp = type of table emp */
if (selectemp(r_emp = r_emp, emp_id = id)
!= ok) then rollback;
message 'Error selecting data ' +
varchar(:id)
with style = popup;
resume;
else
inquire_sql (rows = rowcount);
commit; /* Resets errorno and rowcount */
if rows = 0 then
message 'Employee number '
+ varchar (:id) + ' not found.'
with style = popup;
resume;
endif;
endif; /*r_emp now contains selected data */
Update a row: This example calls the 4GL procedure UpdateEmp to update information about an employee:
if (updateemp(r_emp = r_emp,
emp_id = id) != ok) then
rollback;
message 'Error updating data ' +
varchar(:r_emp.emp_id)
with style = popup;
resume;
else
commit;
endif; /* update accomplished */
The 4GL procedures called in the code above follow:
/* Procedure InsertEmp */
/* Insert row into Emp table */
procedure insertemp (
r_emp = type of table emp
) =
declare
err = integer;
begin
repeated insert into emp (emp_id, name,
age, hire_date)
values (r_emp.emp_id, r_emp.name,
r_emp.age, r_emp.hire_date);
inquire_sql (err = errorno);
if (err != 0) then
return fail;
else
return ok;
endif;
end
/* Procedure SelectEmp */
/* Use unique key to select 1 row from */
/* the Emp table */
procedure selectemp (
r_emp = type of table emp,
emp_id = integer /* unique key */
) =
declare
err = integer;
begin
r_emp = repeated select
from emp
where emp_id = :emp_id;
inquire_sql (err = errorno);
if (err != 0) then
return fail;
else
return ok;
endif;
end
/* Procedure UpdateEmp */
/* Use unique key to update 1 row */
/* from the Emp table */
procedure updateemp (
r_emp = type of table emp,
emp_id = integer /* unique key */
) =
declare
err = integer;
begin
repeated update emp
set emp_id = :r_emp.emp_id,
name = :r_emp.name,
age = :r_emp.age,
hire_date = :r_emp.hire_date
where emp_id = :emp_id;
inquire_sql (err = errorno);
if (err != 0) then
return fail;
else
return ok;
endif;
end