Chat now with support
Chat with Support

Toad Data Point 6.2 - User Guide

Optimize Cross-Connection Queries

To help you improve cross-connection query performance, Toad Data Point 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 Point.

To optimize a cross-connection query using Explain Plan

  1. After building a cross-connection query, select the Explain Plan tab in the Results window.

  2. 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)

    • A description of the optimization technique used in the step
  3. In the Query window, Toad highlights the SQL clauses that are candidates for optimization.
  4. If necessary, try to optimize your query by rewriting any highlighted clauses using the techniques described in the following section.

Cross-Connection Optimization Techniques

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.

 

Related Topics

Create Cross-Connection Queries

Generate Explain Plans

Add Subqueries

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating