SQL Programming Manual for Pascal

Program Compilation and Execution
HP NonStop SQL Programming Manual for Pascal—528614-001
5-33
Estimating Memory Use
•
Count a host variable once per occurrence.
•
Count only the following SQL statements and directives (which generate a run-time
call to the SQL executor):
Do not count:
BEGIN DECLARE SECTION and END DECLARE SECTION CONTROL EXECUTOR,
CONTROL QUERY, and CONTROL TABLE
DECLARE CURSOR
INVOKE
WHENEVER
ALTER DROP LOCK TABLE
BEGIN WORK END WORK OPEN
CLOSE EXECUTE RELEASE
COMMENT EXECUTE IMMEDIATE ROLLBACK WORK
CREATE FETCH SELECT
DELETE FREE RESOURCES UNLOCK TABLE
DESCRIBE HELP TEXT UPDATE
DESCRIBE INPUT INSERT UPDATE STATISTICS
Caution. The system allocates real memory in 2 KB pages. If an SQL statement uses only
part of a page, the system allocates the entire page. Therefore, the real memory used by
embedded SQL statements can be larger than the figures shown in Ta bl e 5 -1
on page 5-32.
A program can encounter memory problems in these cases:
•
It contains a large number of embedded SQL statements.
•
It runs on a system with limited memory (for example, 16 MB or less).
•
It runs in a CPU that is also running a large number of other programs.
To reduce the memory use in the extended data segment:
•
Declare only the host variables that your program actually requires.
•
Declare all host variables in one Declare Section if possible. The system allocates the host
variables contiguously in one or more pages, rather than allocating each host variable in a
separate page.
•
Execute SQL statements in listing order as often as possible. Thus, the SQL statements
can share many of the pages in the extended data segment.
•
As a last measure, use dynamic SQL statements. Using dynamic SQL statements can
reduce memory use. However, it also can degrade a program’s performance because of
the additional SQL run-time compilations.