Using the ShowPlan Function

When the ShowPlan function is activated, Microsoft Jet appends text to a file called Showplan.out when a query is compiled. This text contains the plan that the query optimizer has generated for that query.

Note If you have the ShowPlan function activated, Microsoft Jet will append query plans to the Showplan.out text file every time a query is compiled. As such, the file can grow quite large. You should only turn this feature on during your development and debugging processes. Never leave it on for production databases.

The following text shows the query plan for the Invoices query stored in the Northwind.mdb sample database included with Microsoft Access 97:

---------------------------------------------
DATE: 01/19/97
VER:  3.50.3428

NOTE: Currently does not handle subqueries, vt [virtual table] parameters, and subqueries
NOTE: You may see ERROR messages in these cases

--- Invoices ---

- Inputs to Query -
Table 'Orders'
Table 'Order Details'
	Using index 'OrdersOrder Details'
	Having Indexes:
	OrdersOrder Details 2155 entries, 8 pages, 830 values
	  which has 1 column, fixed
	OrderID 2155 entries, 8 pages, 830 values
	  which has 1 column, fixed
Table 'Customers'
	Using index 'PrimaryKey'
	Having Indexes:
	PrimaryKey 91 entries, 1 page, 91 values
	  which has 1 column, fixed, unique, primary-key, no-nulls
	PostalCode 91 entries, 1 page, 87 values
	  which has 1 column, fixed
	CompanyName 91 entries, 3 pages, 91 values
	  which has 1 column, fixed
	City 91 entries, 1 page, 69 values
	  which has 1 column, fixed
Table 'Employees'
Table 'Products'
Table 'Shippers'
- End inputs to Query -

01) Sort table 'Orders'
02) Inner Join table 'Shippers' to result of '01)'
	  using temporary index
	  join expression "Shippers.ShipperID=Orders.ShipVia"
03) Sort table 'Employees'
04) Inner Join result of '02)' to result of '03)'
	  using temporary index
	  join expression "Orders.EmployeeID=Employees.EmployeeID"
05) Inner Join result of '04)' to table 'Customers'
	  using index 'Customers!PrimaryKey'
	  join expression "Orders.CustomerID=Customers.CustomerID"
06) Inner Join result of '05)' to table 'Order Details'
	  using index 'Order Details!OrdersOrder Details'
	  join expression "Orders.OrderID=[Order Details].OrderID"
07) Sort table 'Products'
08) Inner Join result of '06)' to result of '07)'
	  using temporary index
	  join expression "[Order Details].ProductID=Products.ProductID"

--- temp query ---

- Inputs to Query -
Table 'Customers'
	Using index 'CompanyName'
	Having Indexes:
	CompanyName 91 entries, 3 pages, 91 values
	  which has 1 column, fixed
	City 91 entries, 1 page, 69 values
	  which has 1 column, fixed
- End inputs to Query -

01) Scan table 'Customers'
	Using index 'CompanyName'

--- temp query ---

- Inputs to Query -
Table 'Products'
	Using index 'ProductName'
	Having Indexes:
	ProductName 77 entries, 1 page, 77 values
	  which has 1 column, fixed
	PrimaryKey 77 entries, 1 page, 77 values
	  which has 1 column, fixed, unique, clustered and/or counter, primary-key, no-nulls
	CategoryID 77 entries, 1 page, 8 values
	  which has 1 column, fixed
	CategoriesProducts 77 entries, 1 page, 8 values
	  which has 1 column, fixed
- End inputs to Query -

01) Scan table 'Products'
	Using index 'ProductName'