|
|
 |
Blogs
Toad and Database Commentaries |
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
|
|
|
|
|
|
|
Author: |
RichardTo |
Created: |
Monday, October 30, 2006 2:28 PM |
 |
 |
Richard To is chief technologist and designer of Quest SQL Tuning products. Richard specializes in using AI technique to solve database performance problems.
Richard's blog helps solve problems in your SQL code, tune queries, and shares new tips, tricks, and techniques on SQL tuning.
Recent postings on SQL tuning:
|
|
|
|
|
A Question from our Quest SQL Optimizer User |
|
RichardTo
|
Thursday, September 04, 2008 1:50 AM
|
|
|
Recently, we got a question from a customer:
“Why does adding a ||'' to the end of a key value improve performance... in this case significantly, this query runs in 187 milliseconds regularly where without the ||'' it takes minutes to run... This is the only modification in the result set from SQ ...
|
 |
|
|
Read More...
|
|
|
|
A New Intelligent Test Run Function is under Research |
|
RichardTo
|
Friday, June 13, 2008 3:20 AM
|
|
|
|
A problem in the area of SQL optimization that has been around for a long time is the inaccuracy of the cost estimation of a SQL statement. A lot of people have the wrong conception that inaccurate cost estimations generated for a SQL statement by the database optimizer are caused by outdated statistics; whereas in fact, outdated or incorrect statistics is only one o ...
|
|
|
|
|
Read More...
|
|
|
|
Batch Optimizer Part 4 – Options Settings for the Batch Optimizer |
|
RichardTo
|
Friday, May 09, 2008 5:53 AM
|
|
|
|
Written by Rene Woody
This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings for determining the criteria for selecting the best alternative, for terminating longer running SQL, and eliminating the effect of caching on the comparison ru ...
|
|
|
|
|
Read More...
|
|
|
Batch Optimizer Part 3 – Options Settings for the Batch Optimizer |
|
RichardTo
|
Friday, April 25, 2008 6:44 AM
|
|
|
|
Written by Rene Woody
This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings that determine which SQL statements will be automatically executed by the Batch Optimizer process and the method used for their execution.
|
|
|
|
|
Read More...
|
|
|
|
Batch Optimizer Part 1 – How the Batch Optimizer Works |
|
RichardTo
|
Friday, March 28, 2008 4:01 AM
|
|
|
|
Written by Rene Woody
We all know that it is important to create SQL statements that perform well in our database environment, but all too often, after creating a SQL statement that retrieves the correct results, the optimizing of the SQL statement is left for another time. Frequently, that time never arrives. Quest SQL ...
|
|
|
|
|
Read More...
|
|
|
How to use the Quest SQL Optimizer |
|
RichardTo
|
Tuesday, March 18, 2008 5:31 AM
|
|
|
|
Written by Rene Woody
Quest SQL Optimizer can do an extensive transformation of the syntax of a SQL statement while still maintaining the exact same result set. This exhaustive transformation can produce hundreds of SQL alternatives. So it is necessary to have some insight as how to find a SQL alternative that is better than your ...
|
|
|
|
|
Read More...
|
|
|
What about a Self-learning SQL Optimizer? |
|
RichardTo
|
Tuesday, October 09, 2007 4:45 AM
|
|
|
|
There are at least two database vendors are trying to build self-learning SQL optimizers. The idea is to use actual statistics from executed SQL statements to rectify the future cost estimation of the same or similar SQL statements. It seems like a good idea, but, you will find that their existing self-learning ...
|
|
|
|
|
Read More...
|
|
|
A SQL Performance History from AWR |
|
RichardTo
|
Thursday, September 13, 2007 2:51 PM
|
|
|
|
I have been working on SQL Tuning research for more than 10 years, but my focus has changed to time-series forecast technology in the last two years. Recently, Oracle’s AWR and other database vendor’s statistics/metrics repository have drawn my attention. The relatively low cost of keeping performance statistics in databases, combined wi ...
|
|
|
|
|
Read More...
|
|
|
How Quest SQL Optimizer works with Hints |
|
RichardTo
|
Tuesday, August 14, 2007 10:28 AM
|
|
|
|
Today database vendors are more willing to provide a means for the end user to influence the decision of which execution plan to use for a SQL statement. They provide this because database optimizers cannot guarantee that they the will generate the best execution plan for a given SQL statement. However, when you do apply Optimization Hints you ...
|
|
|
|
|
Read More...
|
|
|
|
|
|
Quest Recursive SQL Transformation Technology |
|
RichardTo
|
Wednesday, June 20, 2007 4:58 AM
|
|
|
|
Quest Recursive SQL Transformation technology is an innovative AI technology that simulates human SQL transformation technique. It incorporates a set of transformation rules to transform SQL statements on a section-by-section basis. This replaces the trial and error method used by human to rewrite the syntax of a SQL statement. Each t ...
|
|
|
|
|
Read More...
|
|
|
From SQL Optimization Hints to Plan Instructions |
|
RichardTo
|
Wednesday, May 02, 2007 7:05 AM
|
|
|
|
Most database vendors provide optimization hints which enable a user to influence the decision the database SQL optimizer will make when determining which execution plan it will choose. Oracle provides a full set of optimization hints to help users to rectify an i ...
|
|
|
|
|
Read More...
|
|
|
|
Dummy SQL Transformation Rules? |
|
RichardTo
|
Thursday, March 01, 2007 10:33 AM
|
|
|
|
I have been asked many times that why there are some dummy SQL transformations/rewrites that look meaningless, but it works in certain situations. Let me give you some examples that may help to explain what theory on behind of Quest SQL Optimizer.
|
|
|
Read More...
|
|
|
Transformation Rules Relating to Index Usage |
|
RichardTo
|
Friday, February 09, 2007 8:25 AM
|
|
|
|
In Quest SQL Optimizer, transformation rules relating to index usage are designed to guide the database SQL optimizer as to how it should use the indexes for a specific SQL statement. Although those rules may familiar to most of SQL developers, but it is still worth to illustrate some commonly used rules for SQL beg ...
|
|
|
|
|
Read More...
|
|
|
How to control many table join |
|
RichardTo
|
Wednesday, January 03, 2007 3:42 PM
|
|
|
|
In my last blog, I use two tables join to illustrate the simple path control, now let’s use a three table join SQL statement to demonstrate a more complicated scenario. Let’s assume that A.key, B.key and C.key are all indexed.
|
|
|
|
|
Read More...
|
|
|
How to Control Two Tables Join Path? |
|
RichardTo
|
Thursday, December 07, 2006 3:06 PM
|
|
|
|
In old version of Oracle database, it is easy to control the join path by reordering the tables in table list after the FROM clause, but it is getting difficult to control the join path in today’s cost based SQL optimizer, I am going to introduce method that is applicable in most databases such as Oracle, Sybase, DB2 and SQL Server in the following:
To control a join path, we cannot tell the database SQL optimizer which path is the best one to select. Instead, we add something to the syntax of the SQL statement that causes an increase to the cost of the current join path selected by database SQL optimizer. Let’s take a look at the following example of two table join scenarios.
|
|
|
|
|
Read More...
|
|
|
Why Join Path Matters |
|
RichardTo
|
Monday, November 13, 2006 12:28 PM
|
|
|
|
The Nested Loop join operation is the basic join operation which is supported by most RDBMS, since it requires less memory and less temporary space. Normally, it can provide faster data response time than other join operations. But, the path of a Nested Loop join will significantly affect the speed of the join operation. Let’s use a two table join as an example to understand how this works.
|
|
|
|
|
Read More...
|
|
|
Why SQL Tuning? |
|
RichardTo
|
Monday, October 30, 2006 2:28 PM
|
|
|
|
For years, commercial database manufacturers have fought an endless battle to improve the performance of inserting, updating, deleting, and retrieving information stored in the database. Despite their continual efforts and hard work, we have not seen a significant improvement in the performance of most Relational Database Management Systems (RDBMS). Users still suffer from under-performing SQL statements and database expe ...
|
|
|
|
|
Read More...
|
|
|
|
|
|
|
|
|
|
|