Examining Query Plans and Evaluating Query Performance
Query Plan Viewer is particularly useful in the development stage of a project for you to test your queries and see how the database engine executes them. You can prepare each of your queries, generate a query plan file and then examine each plan. Based on the information for each query, you can add or remove indexes and then see the affect of the changes. You can also modify the queries to see if a change in the syntax of the statement affects its performance.
Creating Example Query Plans for Comparison
As an example, you can demonstrate the use of Query Plan Viewer using the following steps and a few tables from the DEMODATA database sample database provided with Pervasive PSQL.
For comparison, you will delete an index from the Enrolls table, execute a query and create a query plan file, add the index back to Enrolls, then execute the query again and create a comparison query plan file.
1
In Pervasive PSQL Control Center (PCC), execute the SQL statements to turn on the creation of a query plan and specify the following name of the query plan file:
example1.qpf
. See
Query Plan Settings
.
2
In PCC, execute the following query for the DEMODATA database:
DROP INDEX Enrolls.ClassID
Since DEMODATA is optimized when installed, you need to drop the index from the Class_ID column of the Enrolls table.
3
In PCC, execute the following query for the DEMODATA database:
SELECT Student.ID, Class.Name, Course.Credit_Hours FROM Student, Enrolls, Class, Course WHERE Student.ID = Enrolls.Student_Id AND Enrolls.Class_ID = Class.ID AND Class.Name = Course.Name
This query retrieves all enrolled students, the classes in which they are enrolled, and the credit hours for each course.
4
In PCC, specify the following name of the query plan file:
example2.qpf
. See
Query Plan Settings
.
5
In PCC, execute the following query for the DEMODATA database:
CREATE INDEX ClassID ON Enrolls(Class_ID)
6
In PCC, execute the following query for the DEMODATA database:
SELECT Student.ID, Class.Name, Course.Credit_Hours FROM Student, Enrolls, Class, Course WHERE Student.ID = Enrolls.Student_Id AND Enrolls.Class_ID = Class.ID AND Class.Name = Course.Name
Notice that the query runs faster.
Viewing the Example Query Plans
In Query Plan Viewer, open example1.qpf (through
File
Open). You should see something similar to the following:
Now, for comparison, open example2.qpf in Query Plan Viewer. You should see something similar to the following:
Note the following about this plan:
•
Records are scanned from the Course table.
•
Records are retrieved from the Class table based on the Course.Name value using the Class.Name index.
•
Records are retrieved from the Enrolls table based on the Class.ID value scanning through the Enrolls table.
•
Records are retrieved from the Student table based on the Enrolls.Student_Id using the Student.ID index.
•
The selection of data from Enrolls uses the newly created index, ClassID.
In a similar manner to this example, you can compare your own queries to determine which syntax and structure is right for your needs.
