Sometimes a SQL-92 SELECT statement using a WHERE clause with the key fields referenced returns all the records and is not using the expected indexes. In that case, it is helpful to find out exactly what index was used in the query, to compare it with what index
should have been used.
The ability to show the Query plan that was used to execute a query was introduced in Progress Version 9.1A, where a SQL-92 virtual system table named "
_Sql_Qplan" was added to the PUB schema.
- It is specific to each connection and behaves as a temporary table on the SQL-92 server side.
- As a result, it can only be accessed by the user who submitted the query to retrieve information about the way the previous query was optimized.
- The query plan might change after runing UPDATE STATISTICS.
The following SQL statement is run directly after a SQL SELECT statement to show the query plan used to execute that statement:
SELECT "_Pnumber", SUBSTRING("_Description",1,80)
FROM pub."_Sql_Qplan" WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" )
FROM pub."_Sql_Qplan" WHERE "_Ptype" > 0 );
For Progress 9.1A, the following query must be used which returns all available query plans. The relevant query plan must extracted from the results:
SELECT "_Pnumber", SUBSTRING("_Description", 1, 80)
FROM pub."_Sql_Qplan";
To query the _Sql_Qplan table for a query plan:
- Execute the SQL query in question. The following query executes against Sports2000:
SELECT custNum, name, salesRep
FROM pub.customer WHERE salesRep = 'HXM' AND custnum BETWEEN 1 AND 50;
This displays the following results:
CustNum Name SalesRep
----------- ----------------------- --------
1 Lift Tours HXM
3 Hoops HXM
15 Hoopla Basketball HXM
19 Buffalo Shuffleboard HXM
21 Pedal Power Cycles HXM
30 Fast Flipper Pinball HXM
44 Ship Shape Yachting HXM
47 Batter Up Baseball HXM
50 Chip's Poker HXM
- Execute the following query immediately after the last statement:
SELECT "_Pnumber", SUBSTRING("_Description",1,80)
FROM pub."_Sql_Qplan" WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" )
FROM pub."_Sql_Qplan" WHERE "_Ptype" > 0 );
This will provide a result set similar to the following:
Optimized Query Plan :
SELECT CustNum, Name, SalesRep
FROM pub.customer WHERE SalesRep = 'HXM' AND custnum BETWEEN 1 AND 50
PROJECT [26] (
| RESTRICT [12] (
| | PROJECT [24] (
| | | PUB.CUSTOMER. [3](
| | | | INDEX SCAN OF (
| | | | | CustNum,
| | | | | | (PUB.CUSTOMER.CustNum) between (1,50))
| | | )
| | , PUB.CUSTOMER.CustNum
| | , PUB.CUSTOMER.Name
| | , PUB.CUSTOMER.SalesRep
| | )
|
| | (PEXPR3) = (HXM)
| | Evaluation callback list(
| | | col id# 11
| | | [can terminate scan]
| | )
| )
, PEXPR1
, PEXPR2
, PEXPR3
)
For further advice on interrogating a SQL Query Plan refer to Articles:
To log a query plan for SQL SELECT statements:In
OpenEdge 10.1B, Progress introduced a method to log the query plan for all SQL SELECT statements to a log file. For further information refer to Article
1. Turn SQL Logging
ON:
SET PRO_SERVER LOG ON WITH (QUERY_PLAN);
COMMIT;
2. Execute the SQL statement in question.
SELECT custNum, name, salesRep
FROM pub.customer WHERE salesRep = 'HXM' AND custnum BETWEEN 1 AND 50;
3. Review the SQL log file (generated in the database's working directory).
The SQL log file name will have the following format:
SQL_server_<server-id>_<ddmmmyyyy>_<hhmmss>.log.
4. Turn SQL Logging
OFF:
SET PRO_SERVER LOG OFF;
COMMIT;
