The order in of tables specified in a FROM clause may make a difference to database performance. This dependency is based on how the parser reads the SQL statement. Depending upon the parser, some users have found that listing the larger tables in a from clause last proves to be more efficient.
Example:
select * from small_table, next_smallest_table, ... largest_table
Proper Order of Join Conditions
Most joins use a base table to link tables that have one or more common columns on which to join. A base table is the main table that most or all tables are joined to in a query. The column from the base table is normally placed on the right side of a join operation in the WHERE clause. The tables joined to the base table are normally ordered from small to large. If you do not have a base table then the tables should be listed from small to large with the largest tables on the right hand side of the join operation in the WHERE clause.
The join conditions should be in the order of the first position(s) of the WHERE clause followed by the filter clause(s).
from table1, - Smallest Table
table2, - to
table3 - Largest Table - or Base table
where table1.column = table3.column - join condition
and table2.column = table3.column - join condition
[and condition1 ] - filter condition
[and condition2 ] - filter condition
In this example table3 is the base table, table1 and table 2 are joined to table3.
Because joins typically return a high percentage of the rows from the table(s), join conditions should be evaluated after the more restrictive conditions.
Most Restrictive Conditions
The most restrictive condition is that condition in the WHERE clause that returns the least number of rows. The least restrictive condition is the condition that returns the most number of rows.
A SQL optimiser should evaluate the MOST restrictive condition first in order to reduce your queries overhead. Optimisers can either work from the bottom up (last where clause first) or top down (first where clause first).
To determine the way your SQL parser works, either ask your DBA (if he/she knows their stuff then they'll give you the answer) or failing that, run a simple test - perhaps with a phony table. In this test, take a table with a large number of rows. Figure out two WHERE clauses: one returning significantly fewer rows than the other.
select * from small_table, next_smallest_table, ... largest_table
Proper Order of Join Conditions
Most joins use a base table to link tables that have one or more common columns on which to join. A base table is the main table that most or all tables are joined to in a query. The column from the base table is normally placed on the right side of a join operation in the WHERE clause. The tables joined to the base table are normally ordered from small to large. If you do not have a base table then the tables should be listed from small to large with the largest tables on the right hand side of the join operation in the WHERE clause.
The join conditions should be in the order of the first position(s) of the WHERE clause followed by the filter clause(s).
from table1, - Smallest Table
table2, - to
table3 - Largest Table - or Base table
where table1.column = table3.column - join condition
and table2.column = table3.column - join condition
[and condition1 ] - filter condition
[and condition2 ] - filter condition
In this example table3 is the base table, table1 and table 2 are joined to table3.
Because joins typically return a high percentage of the rows from the table(s), join conditions should be evaluated after the more restrictive conditions.
Most Restrictive Conditions
The most restrictive condition is that condition in the WHERE clause that returns the least number of rows. The least restrictive condition is the condition that returns the most number of rows.
A SQL optimiser should evaluate the MOST restrictive condition first in order to reduce your queries overhead. Optimisers can either work from the bottom up (last where clause first) or top down (first where clause first).
To determine the way your SQL parser works, either ask your DBA (if he/she knows their stuff then they'll give you the answer) or failing that, run a simple test - perhaps with a phony table. In this test, take a table with a large number of rows. Figure out two WHERE clauses: one returning significantly fewer rows than the other.
- Run a SQL command with the most restrictive clause first and time it.
- Run a SQL command with the least restrictive clause first and time it.
select count(*)
from TEST_TABLE
where
and
If the most restrictive clause was last and it completed in the fastest time, then your SQL parser is a Bottom Up parser (WHERE clauses are read "last first").
Example 2.
from table1, - Smallest Table
table2, - to
table3 - Largest Table - or Base table
where table1.column = table3.column - join condition
and table2.column = table3.column - join condition
[and condition1 ] - Lease restrictive Clause
[and condition2 ] - Most restictive Clause.
Further More...
It is good practise to try to use an indexed column as the most restrictive condition in a query as indexes usually improve a query's performance.
No comments:
Post a comment