Complex SQL Queries - Number of Other Users
(Page 2 of 4 )
Finally, concurrency is a factor that you must carefully take into account when designing your SQL code. Concurrency is usually a concern while writing to the database where block-access contention, locking, latching (which means locking of internal DBMS resources), and others are the more obvious problem areas; even read consistency can in some cases lead to some degree of contention. Any server, no matter how impressive its specification, will always have a finite capacity. The ideal plan for a query running on a machine with little to no concurrency is not necessarily the same as the ideal plan for the same query running on the same machine with a high level of concurrency. Sorts may no longer find the memory they need and may instead resort to writing to disk, thus creating a new source of contention. Some CPU-intensive operations—for example, the computation of complicated functions, repetitive scanning of index blocks, and so forth—may cause the computer to overload. I have seen cases in which more physical I/Os resulted in a significantly better time to perform a given task. In those cases, there was a high level of concurrency for CPU-intensive operations, and when some processes had to wait for I/Os, the overworked CPUs were relieved and could run other processes, thus ensuring a better overlap. We must often think in terms of global throughput of one particular business task, rather than in terms of individual user response-time.
NOTE
Chapter 9 examines concurrency in greater detail.
Filtering
How you restrict your result set is one of the most critical factors that helps you determine which tactics to apply when writing an SQL statement. The collective criteria that filters the data are often seen as a motley assortment of conditions associated in the where clause. However, you should very closely examine the variouswhere-clause (andhaving-clause, too) conditions when writing SQL code.
Meaning of Filtering Conditions
Given the syntax of the SQL language, it is quite natural to consider that all filtering conditions, as expressed in the where clause, are similar in nature. This is absolutely not the case. Some filtering conditions apply directly to theselect operator of relational theory,wherechecking that a column in a row (purists would say an attribute in a relation variable) matches (or doesn’t match) a given condition. However, historically thewhereclause also contains conditions that implement another operator—thejoinoperator. There is, since the advent of the SQL92 join syntax, an attempt to differentiatejoinfiltering conditions, located between the (main)fromclause and thewhereclause, from theselectfiltering conditions listed in thewhereclause. Joining two (or more) tables logically creates a new relation.
Consider this general example of a join:
select .....
from t1
inner join t2
on t1.join1 = t2.joind2
where ...
Should a condition on columnc2belonging tot2come as an additional condition on theinner join, expressing that in fact you join on a subset oft2? Or should a condition inside thewhere clause, along with conditions on columns oft1, express that the filtering applies to the result of joiningt1tot2? Wherever you choose to place yourjoincondition ought not to make much of a difference; however, it has been known to lead to variations in performance with some optimizers.
We may also have conditions other than joins and the simple filtering of values. For instance, we may have conditions restricting the returned set of rows to some subtype; we may also have conditions that are just required to check the existence of something inside another table. All these conditions are not necessarily semantically identical, although the SQL syntax makes all of them look equivalent. In some cases, the order of evaluation of the conditions is of no consequence; in other cases, it is significant.
Here’s an example that you can actually find in more than one commercial software package to illustrate the importance of the order of the evaluation of conditions. Suppose that we have aparameterstable, which holds:parameter_name,parameter_type, andparameter_value, withparameter_valuebeing the string representation of whatever type of parameter we have, as defined by the attributeparameter_type. (To the logical mind this is indeed a story of more woe than that of Juliet and her Romeo, since the domain type of attributeparameter_valueis a variable feast and thus offends a primary rule of relational theory.) Say that we issue a query such as:
select * from parameters
where parameter_name like '%size'
and parameter_type = 'NUMBER'
With this query, it does not matter whether the first condition is evaluated before or after the second one. However, if we add the following condition, where
int()is a function to convert fromchartointegervalue, then the order of evaluation becomes very significant:
and int(parameter_value) > 1000
Now, the condition onparameter_typemust be evaluated before the condition on the value, because otherwise we risk a run-time error consequent upon attempting to convert a character string (if for exampleparameter_typefor that row is defined aschar) to aninteger. The optimizer may not be able to figure out that the poor design demands that one condition should have higher priority—and you may have trouble specifying it to the database.
All search criteria are not equal; some are more equal than others.
Next: Evaluation of Filtering Conditions >>
More SQL Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of The Art of SQL, written by Stephane Faroult and Peter Robson (O'Reilly; ISBN: 0596008945). Check it out today at your favorite bookstore. Buy this book now.
|
|
