Evaluating Queries for Data Ship Using Execution Plans
Studio execution plans can tell you whether a query can make use of data ship or not. When you create views and procedures that involve data from tables that exist on separate sources the execution plan reveals whether the data ship optimization can improve performance of the query.
During the query development phase, verify that the data ship query plan performs better than a standard query plan without the data ship. Sometimes, the time cost of shipping a very large table across the network between two different kinds of data sources can cost almost as much as processing the query normally.
After configuring your data source to use the data ship optimization, you need to evaluate the queries that are run by that data source to determine if they benefit from the data ship. If the queries benefit from the data ship optimization, there is nothing further that you need to do. If the queries do not benefit from the data ship, you need to disable the feature in the hint of the query SQL.
For more information about the execution plan fields and what they represent, see
Performance Tuning.
To use execution plans to evaluate your queries
1. Open Studio.
2. Open the view or procedure that has the query you want to evaluate.
3. Validate that the query is a SQL operation between two or more data sources that can act as the data ship source and at least one data source that can act as the data ship target receiving the shipped tables.
4. Click Show Execution Plan to reveal the Execution Plan tab.
5. Review the query execution report paying specific attention to:
Detail | Description |
Estimated Rows Returned | A value estimated from table cardinalities which are not generally used to estimate the cost of shipping SQL requests. For Netezza, determine the cost of a query with an Explain Plan function. For Netezza, the estimated rows returned field will have a value of Unknown. |
Data Ship target | Where to send the results of SQL execution. The presence of a data ship target indicates that data ship will be performed. |
Data Ship Notes | A report of the estimated number of rows to be shipped, and the amount of time consumed to get the cost estimate. When a condition blocks the use of data ship, this field usually describes the condition, case, or option that ruled out optimization. |
6. Execute other queries to show statistics like costs of table creation and other query initialization costs that add to the overall retrieval time.
See
Execution Plan Panel for information about the buttons and controls on the Execution Plan. See
Working with the SQL Execution Plan for information about using the execution plan and evaluating the results.