4.8.2 Viewing the SQL Query Plan of a Table

To view the SQL statements that a table will attempt to execute (the query plan), right-click the title bar, and select 'Show Plan' from the context menu. This opens the 'Query Plan' dialog box.

The 'Query Plan' dialog box displays the queries that will be sent to the database at runtime. Additionally, it shows the data operations that the Worksheet will perform in post-processing, after the data is retrieved from the database. The plan also shows the source information for parameterized conditions.

By default, the Worksheet attempts to create a single SQL query that fully generates the table's data. This is typically the most efficient approach because it allows the database to perform all the needed data operations (joins, filtering, etc.). However, you can override this behavior for a particular table by deselecting the 'Merge SQL' option in the 'Table Properties' dialog box, and in some cases this may improve performance. See Setting Table Query and Display Properties for more information.

Example: Viewing the SQL Query Plan  >>

This example illustrates how you can control the SQL query that a table sends to the database.

1. Expand the 'TABLE' > 'SA' node of the 'Orders' data source.

2. Drag the 'products' table to an empty location on the Worksheet.

3. Drag the 'categories' table to an empty location on the Worksheet.

4. Drag the 'category_id' column header from the 'categories' table onto the 'category_id' column header of the 'products' table, and release when the 'join' symbol appears.

This creates a new table called 'Query' that contains the inner join between the two tables based on the 'category_id' columns. (See Inner Join for more details.)

5. Right-click the title bar of the 'Query' table, and select 'Show Plan'. This displays the 'Query Plan' dialog box.

 

The 'Query Plan' dialog box displays the query that the table will send to the database. The statement below the query indicates that this query (including the instructions to join the two tables) will be sent to the database as a single SQL statement, which is also indicated by the single icon in the top panel.

To override the default single-query behavior, follow the steps below:

1. Right-click the title row of the 'Query' table again, and select 'Properties' from the context menu. This opens the 'Table Properties' dialog box.

2. Deselect 'Merge SQL' and click 'OK' to exit the dialog box.

3. Right-click the table title bar, and select 'Show Plan' again.

 

The query plan is again displayed, but the top panel now shows an expandable hierarchy of queries. The 'products' and 'categories' queries are each individually executed as single queries, but the inner join that generates the final 'Query' table is performed by the Worksheet in post-processing.

 

<< 4.8.1 Setting Table Query and Display Properties © 1996-2013 InetSoft Technology Corporation (v11.4) 4.8.3 Editing a Composition Table >>