Optimizing WHERE Clauses

Overview of Optimizing WHERE Clauses

SPD Server includes more advanced methods to optimize WHERE clauses. Before SPD Server 4.0, the rule-based, heuristic WHERE clause planner WHINIT was used to manually tune queries for performance. SPD Server provides dynamic WHERE clause costing, an automatic feature that can replace the need to manually tune queries. SPD Server dynamic WHERE-costing uses factors of duplicity and distribution to calculate relative processor costs of various WHERE clause options. SPD Server users can use server parameter commands in the spdsserv.parm file or macro variables to turn dynamic WHERE-costing on and off. If dynamic WHERE-costing is turned off, SPD Server reverts to using the rules-based WHERE clause planner.

WHERE Clause Definitions and Terminology

WHERE clauses are selection criteria for a query that specify one or more Boolean predicates. Implementing the criteria, SPD Server selects only records that satisfy the WHERE clause.
Predicates
are the building blocks of WHERE clauses. Use them stand-alone or combine them with the operators AND and OR to form complex WHERE clauses. An example of a WHERE clause is
```
"where x > 1 and y in (1 2 3)"
```
In this example, there are two predicates, x > 1 and y in (1 2 3). You specify the negative of a predicate by using not. For example, where x > 1 and not (y in (1 2 3)).
Boolean logic determines whether two predicates, joined with an AND or OR, are true (satisfies) or false (does not satisfy) the specification. The AND operator requires that all predicates be true for the entire expression to be true. For example, the expression p1 AND p2 AND p3, is true only if all three predicates (p1, p2, andp3) are true. In contrast, the OR operator requires only one predicate to be true for the entire expression to be true.
For the WHERE clause (x < 5 or y in (1 2 3)) and z = 10, the following truth table describes the overall result (truth):
```
"x < 5 ?"    "y in (1 2 3) ?"    "z = 10 ?"    Result
=========    ================    ==========    ======
  False          False            False        False
  False          False            True         False
  False          True             False        False
  False          True             True         True
  True           False            False        False
  True           False            True         True
  True           True             False        False
  True           True             True         True
```
Indexes are structures associated with tables that permit SPD Server to quickly access records that satisfy an indexed predicate. In an example WHERE clause, where x = 10 and y > 11, SPD Server selects the best index on column x to directly retrieve records that have a value of 10 in the x column. If no index exists for x, SPD Server must sequentially read each record in the table searching for x equal to 10.
Simple and composite indexes: Simple indexes index a single column; composite indexes index two or more columns. The list of column(s) in an index is sometimes called the index key.
Parallelism is the SPD Server capability that enables multiple threads to execute in parallel. Using multiple processors in parallel mode is sometimes called 'divide and conquer' processing. SPD Server uses parallelism to evaluate the multiple indexes that are involved in more complicated WHERE clauses.