Viewing query plan with SQL in OpenEdge

It is important to review query plan produced by the database and improve queries from time to time. OpenEdge generates query plan for the queries you write. This article shows how to view query plan in OpenEdge.

View query plan

Let’s say the query below is taking a long time:

select * from department;

When this query is executed, _sql_plan table is populated with information about how the query was processed. We can extract information from this table to gain insight on how to optimize the query. Let’s see the query plan.

SELECT "_Pnumber", SUBSTRING("_Description",1,80)
FROM pub."_Sql_Qplan"
WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" ) FROM pub."_Sql_Qplan" WHERE "_Ptype" > 0 );

The output appears like this:

_Pnumber |substring(_Description,1,80)  |
---------|------------------------------|
3739     |SELECT COMMAND.               |
3739     |PROJECT [9] (                 |
3739     ||   PROJECT [7] (             |
3739     ||   |   PUB.DEPARTMENT. [1](  |
3739     ||   |   |   TABLE SCAN        |
3739     ||   |   )                     |
3739     ||   , PUB.DEPARTMENT.DeptName |
3739     ||   )                         |
3739     |, PEXPR1                      |
3739     |)                             |

The above plan shows that there was a table scan done to satisfy the query. Table scans are expensive, especially when the table is large. Did we really want all columns and all rows from department? Perhaps not. We probably wanted just the department name for department 9000. Let’s see if the database understands how to extract the data using an index.

select deptname from department where deptcode = '9000';

SELECT "_Pnumber", SUBSTRING("_Description",1,80)
FROM pub."_Sql_Qplan"
WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" ) FROM pub."_Sql_Qplan" WHERE "_Ptype" > 0 );

_Pnumber |substring(_Description,1,80)                            |
---------|--------------------------------------------------------|
3741     |SELECT COMMAND.                                         |
3741     |PROJECT [15] (                                          |
3741     ||   PROJECT [13] (                                      |
3741     ||   |   PUB.DEPARTMENT. [1](                            |
3741     ||   |   |   INDEX SCAN OF (                             |
3741     ||   |   |   |   DeptCode,                               |
3741     ||   |   |   |   |   (PUB.DEPARTMENT.DeptCode) = (9000)) |
3741     ||   |   )                                               |
3741     ||   , PUB.DEPARTMENT.DeptName                           |
3741     ||   )                                                   |
3741     |, PEXPR1                                                |
3741     |)                                                       |

Alright! We see that the database is actually doing an index scan of deptcode index to satisfy the query. Nice.

Reviewing query plan for your queries can go a long way. Please, especially during development, keep a close watch on the queries that are being executed and whether an index – the right index – is being hit.

Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP.