Project

General

Profile

Query Execution

To understand how queries are executed, let's take a simple example:

DEFINE TEMP-TABLE TBook
   FIELD book-id AS INTEGER
   FIELD book-title AS CHARACTER
   FIELD author AS CHARACTER
   FIELD isbn AS CHARACTER
   FIELD cost AS DECIMAL
   FIELD pub-date AS DATE
   INDEX idx-book-id AS PRIMARY UNIQUE book-id
   INDEX idx-isbn AS UNIQUE isbn.

CREATE TBook. book-id = 1001. book-title = "Odyssey".              author = "Homer".        isbn = "000-123-456-987". cost = 3.45. pub-date = 12/01/-758.
CREATE TBook. book-id = 1002. book-title = "Ramayana".             author = "Valmiki".      isbn = "103-460-987-729". cost = 3.45. pub-date = 11/21/-632.
CREATE TBook. book-id = 1003. book-title = "Beowulf".              author = ?.              isbn = "313-470-167-209". cost = 3.45. pub-date = 10/25/998.
CREATE TBook. book-id = 1004. book-title = "Descriptio Moldaviae". author = "D. Cantemir".  isbn = "540-710-845-671". cost = 3.45. pub-date = 09/17/1715.
CREATE TBook. book-id = 1005. book-title = "Lorem ipsum".          author = "Lorem ipsum".  isbn = "284-597-134-001". cost = 3.45. pub-date = ?.
CREATE TBook. book-id = 1006. book-title = ?.                      author = ?.              isbn = "875-658-124-325". cost = 3.45. pub-date = ?.

FOR EACH TBook WHERE book-title = author AND cost NE ?:
   MESSAGE book-id isbn.
END.

We are interested in the converted query at this moment, although the provided data will confirm its execution. After conversion, the Java source will contain the following construct:

         AdaptiveQuery query0 = new AdaptiveQuery();

         forEach("loopLabel0", new Block((Init) () -> 
         {
            query0.initialize(tbook, "upper(tbook.bookTitle) = upper(tbook.author) and tbook.cost is not null", null, "tbook.bookId asc");
         }, 
         (Body) () -> 
         {
            query0.next();

            message(new Object[]
            {
               (integer) new FieldReference(tbook, "bookId").getValue(),
               (character) new FieldReference(tbook, "isbn").getValue()
            });
         }));

P2JQuery.initialize(fql)

Notice the query0 object declared as an AdaptiveQuery. It is initialized in the Init method of forEach block using several parameters: beside the first, tbook which will let it know the table / buffer the query applies to, the second parameter is a Java String which describe which records will be returned by the query in a FWD-specific language named FQL. In fact, this value represents only the predicate of a FQL statement. This is the staring point of the query at runtime.

fql = HQLPreprocessor.translate(fql)

Optionally, in some cases, the buffer on which the query works has different Java name in different procedures. This usually happens because of local name collisions. Due to this fact, the fql is processed and transformed so that the name of the buffer is updated in all places.

Intermediary processing

JOIN-ing queries

In some very specific cases, the queries which were converted into multiple QueryComponent s can be joined to create a single bigger fql which will have the advantage to being fully executed on SQL tier, reducing this way the dialogue with the database server.

processDynamicFilters()

When some dynamic filters are active, the original fql, stored in where member of QueryComponent is enhanced with some terms in order to satisfy the active filters. For example, if the user has selected to filter a specific author, the new query will look like:

(upper(trim(author)) like ?) and (<old-where>)

This new FQL expression will be stored in dfWhere (dynamically-filtered-where) of same QueryComponent.

?

Our example uses an AdaptiveQuery which will attempt to issue SQL statements for increasingly large sequential chunks of the whole query for maximum performance. In our particular case it will not be the case, but occasionally, if the execution flow requires, this sequential approach will change to dynamic mode for flexibility.

HQLPreprocessor

The core of preprocessing happens at this stage. There are several stages here and all the following steps transform the source fql form a rather simple predicate into a complex, optimized, dialect specific still semantically-compatible with 4GL query:
  • parsing: the fql is parsed into a tree of HQLAst nodes which can be easily traversed;
  • restructure qualified names of text properties and functions to embed these names with an SQL function which trims trailing white space;
  • since some dialect do not fully support boolean type, the unary logical expressions to instead be binary logical expressions;
  • restructure the AST to account for the joins and generate any necessary ANSI join subexpressions;
  • possibly inline certain query substitution parameters;
  • does local optimizations for tree subnodes, possibly transforming the fql into a simpler, shorter expression;
  • augmenting the expression for unknown value semantics.