4D Doc Center : DESCRIBE QUERY EXECUTION

4D v12.4

DESCRIBE QUERY EXECUTION


DESCRIBE QUERY EXECUTION ( status )

Parameter	Type	Description
status	Boolean	True=Enable internal query analysis, False=Disable internal query analysis

Description

The DESCRIBE QUERY EXECUTION command enables or disables the query analysis mode for the current process. The command takes into account both queries carried out via the 4D language or by SQL.

Calling the command with the status parameter set to True enables the query analysis mode. In this mode, the 4D engine records internally two specific pieces of information for each subsequent query carried out on the data:

A detailed internal description of the query just before its execution, in other words, what was planned to be executed (the query plan),
A detailed internal description of the query that was actually executed (the query path).

The information recorded includes the type of query (indexed, sequential), the number of records found and the time needed for every query criteria to be executed. Y ou can then read this information using the Get Last Query Plan and Get Last Query Path commands.

Usually, the description of the query plan and its path are the same, but they may nevertheless differ because 4D might implement dynamic optimizations during the query execution in order to improve performance. For example, an indexed query may be converted dynamically into a sequential query if the 4D engine estimates that this might be faster — this is sometimes the case, more particularly, when the number of records being queries is low.

Pass False in the status parameter when you no longer need to analyze queries. The query analysis mode can slow down the application.

Example

The following example illustrates the type of information obtained using these commands in the case of an SQL query:

 C_TEXT($vResultPlan;$vResultPath)

 ARRAY TEXT(aTitles;0)

 ARRAY TEXT(aDirectors;0)

 DESCRIBE QUERY EXECUTION(True) `analysis mode

 Begin SQL

    SELECT ACTORS.FirstName, CITIES.City_Name

    FROM ACTORS, CITIES

    WHERE ACTORS.Birth_City_ID=CITIES.City_ID

    ORDER BY 1

    INTO :aTitles, :aDirectors;

 End SQL

 $vResultPlan:=Get Last Query Plan(Description in Text Format)

 $vResultPath:=Get Last Query Path(Description in Text Format)

 DESCRIBE QUERY EXECUTION(False) `End analysis mode

After this code is executed, $vResultPlan and $vResultPath contain descriptions of the queries carried out, for example:

$vResultPlan: 
   [Join] : ACTORS.Birth_City_ID = CITIES.City_ID
$vResultPath: 
   And
      [Merge] : ACTORS with CITIES
         [Join] : ACTORS.Birth_City_ID = CITIES.City_ID (1227 records found in 13 ms)
      --> 1227 records found in 13 ms
   --> 1227 records found in 14 ms

If the Description in XML format constant is passed to the Get Last Query Path command, $vResultPath contains the description of the query expressed in XML:

$vResultPath: 
   <QueryExecution>
      <steps description="And" time="0" recordsfounds="1227">
         <steps description="[Merge] : ACTORS with CITIES" time="13" recordsfounds="1227">
            <steps description="[Join] : ACTORS.Birth_City_ID =CITIES.City_ID" time="13" recordsfounds="1227"/>
         </steps>
      </steps>
   </QueryExecution>