Consultor Eletrônico

Planned Performance improvements for V7 ESQL

Performance improvements planned for V7 ESQL per Bill Bartow 1/4/93
-------------------------------------------------------------------

V7 ESQL Performance Improvements

1. Preselect is no longer used

In V6 HLI, the first time HLI access a file, HLI uses the PRESELECT
function to build a temporary index that contains pointers to each
of the records that satisfies the selection criteria. Building
this index is resource intensive and ,depending on the size of the
file, can result in significant preformance degradation when the
file is initially accessed.

V7 ESQL will no longer use Preselect. This should improve
performance significantly.

2. Improved management of ".R" files

In V6 HLI dynamic SQL HLI creates a ".R" on the disk for every
unique SQL statement. After the SQL statement is compiled and
executed, the ".R" is deleted. Dynamically creating these ".R"
files is an expensive operation and can slow down an application.

V7 ESQL will take advantage of new ".R" file management
techniques which are currently being defined. A couple of
possible enhancements being considered are:
- ESQL may write to a buffer in memory instead of
creating a ".R" file on disk.
- ESQL may continually write to one ".R" file that
expands and
contracts as required. Consequently, not as many
".R" files will be dynamically created on disk which
should improve performance.

3. Parameter Markers

Parameter markers allow ESQL applications to prepare a SQL request
once, and execute it multiple times while supplying different
parameters or data values to the request each time. Therefore,
the ESQL application does not have to contantly open and close new
cursors for the same SQL statement just to give it a new value.
The same cursor can be executed repeatedly with new values.

Opening and closing cursors takes time and generates new ".R"
files for each cursor. Parameter markers reduces the number of
cursors required (and .R files generated) and therefore will
improve performance.

4. Application Server Architecture

In V6 HLI, the HLI application contains all of the Progress client
code. In a networked configuration where the HLI client and
database server are on different machines, selection of database
records will often occur on the HLI client machine. In some cases,
the entire file is shipped over the network to the HLI client. In
V7 ESQL, the application server contains all of the Progress
client code, in addition to a variety of ESQL specific
functionality. In V7, the application and database servers can be
placed on the same machine. The application server will then
process all SQL queries not processed by the server (such as
aggregates: SUM, AVG, COUNT, etc.). The results of the query
are then shipped over the network to the ESQL client.

V7 ESQL will also benefit from the new query resolution
("Query by Server") work being done. For example, in a networked
configuration
where the application server is on the client machine, the
database server will

use many new query resolution techniques to process the query on
the server machine and ship only the results over the network to
the ESQL client.

Progress Software Technical Support Note # 11425