Example of using the explain query REST API to get the ARJDBC query execution plan

Analyze query execution plan scenario

While generating the quarterly sales report, Seth experiences a delay. To understand the cause of the delay, he must analyze the ARJDBC query execution plan. Seth uses the explain query REST API to fetch the ARJDBC query execution plan from the AR System server.

The following infographic shows the process of fetching a query execution plan by using the explain query REST API:

Retrieve query execution plan workflow

• Seth, the database administrator retrieves the query execution plan by using the Postman client and the explain query REST API call.
  An example of the explain query REST API call is as follows:

  POST /api/arsys/v1.0/sql/explain
  Request: RestSqlQuery object
  
  {
      "sql": "select ...",
      "bind_variables": {
          "1": {
              "value": "Value",
              "type": "Type"
          },
          "2": {
              "value": "Value",
              "type": "Type"
          },
          ...
      },
      "properties": {
          "name", "Value",
          "name2", "Value2"
      }
  }

• The request is processed and a response that contains query execution plan is sent. 
  Here is an example: 

  {
     "execution_plan": "Execution plan as returned by database",
     "table_map" {
         "form/record name": [
             "table_name alias",
             "table_name2 alias2"
          ],
         "form/record name2": [
             "table_name alias",
             "table_name2 alias2"
          ],
          ...
     }
  }
  table_map": {
          "User": {
              "table_info": [
                  {
                      "name": "T35",
                      "alias": "J0"
                  }
              ],
              "field_column_map": {
                  "Full Name": "C8"
             }

Analyze query execution plan result

Seth analyzes the query execution plan. He updates the ARJDBC query by adding an index on the Full Name field to speed up the query processing time.

After the Full Name field is indexed, the query fetches results faster.

explain query REST API

The following table lists the details about using the explain query REST API to get the query execution plan:

Sample query and output formats

The AR System server returns the query execution plan in a database-specific format.

See the sample query and output formats for MS SQL, Oracle, and PostgreSQL databases.