To help you improve cross-connection query performance, Toad Data Studio uses the Explain Plan to suggest improvements to your SQL statements and to reveal optimization techniques used in the estimated plan. Use this information to learn to write/rewrite your queries to take advantage of Toad's cross-connection optimization techniques.
Note:The enhanced and fully-optimized version of cross-connection query is available only in the Professional edition of Toad Data Studio.
To optimize a cross-connection query using Explain Plan
In the execution diagram, hover over each step and review the screen tip. Each screen tip will provide one of the following:
A description of the step (non-optimized)
To improve the performance of cross-connection queries (especially if large data sets are involved), Toad attempts to optimize the query by pushing work down to the originating databases.
When writing/rewriting your cross-connection SQL statements, consider the following optimization techniques used by Toad.
| Optimization technique | Description |
|---|---|
| Subquery push-down |
If your SQL contains two or more tables from one database, you can optimize performance by writing your query as a subquery. To utilize subquery push-down, put the tables from one database into the subquery. The subquery is executed in the originating database, reducing execution time. See Add Subqueries for more information. Note: This is the only optimization technique initiated by the user. The other techniques listed in this table are automatically applied by Toad. |
| Filter push-down | In this optimization technique, Toad pushes filter conditions down to the database by using the criteria of the WHERE clause as the parameters of the data request. This technique reduces the amount of data copied to Toad from the remote database. |
| Query push-down | Toad applies query push-down whenever all the tables referenced in a query are from a single database. In this case, Toad pushes the entire query down to the originating database for execution, utilizing the RDBMS's own SQL execution and optimization processes. |
| Materialized tables | In this cross-connection optimization technique, Toad uses bulk data fetches to create local, temporary (materialized) tables for large data sets. In this way, fetching rows is done in Toad and not across the network. |
© ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center
