DESCRIBE QUERY EXECUTION

version 11.1

DESCRIBE QUERY EXECUTION (status)

The DESCRIBE QUERY EXECUTION command can be used to enable or disable the query analysis mode for the current process. The command takes into account both queries carried out via the 4D language or by SQL.

Callingthe 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 new 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.

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>

Get Last Query Path, Get Last Query Plan.