Newsgroups: bit.listserv.sqlinfo
Date:         Sun, 8 Nov 1992 13:38:05 -0600
From:         Stellar Systems Group <STELLAR@BCSC02.BITNET>
Subject:      Re: IN predicate

 On Fri, 6 Nov 1992 18:41:51, Arthur Keller wrote:

 >John F. Griffin writes:
 >> Please note that in the subquery, I coded it as y.filed2 = x.field2.  The
 >> order is CRITICAL.  If it is coded in the reverse order (x.field2 =
    y.field2)
 >> then it will not perform as well.  This is a common problem, since the
 >> intuitive way to write the statement is not the best way.  (I have written
 >> this type of query, given it to people to implement, and had them change the
 >> order to make it read better, but then it performs much worse.)  The reason
 >> you need to have the order correct, is because for the predicate to be
 >> sargable, it must be of the form <column> <operator> <column or value>.
 >> When the subquery is evaluated, the current value of x.field2 is used as a
 >> value.  If you code it in the wrong order (x.field2 = y.field2) then it is
 >> of the form <value> <operator> <column>, which is non-sargable, and
 >> non-indexable.
 >
 >Arthur Keller replies:
 >Is this an inherent restriction of SQL (I sure hope not!) or a restriction
 >of the particular vendor's implementation.  Please explain why some
 >vendor's implementation could not reverse the order of an equality
 >expression or do some other equivalence transformations.

 I should have clarified, that my statement was referring to the IBM SQL/DS
 and DB2 optimizers (and I suspect the OS/2 and AS/400 as well).  I do not
 know if it applies to other vendor's optimizers (but I would expect that it
 would for some of them).

 This is not an inherent restriction of SQL.  Each vendor has it's own
 optimizer which chooses the access path.  Most of the optimizers are
 cost-based; they estimate the cost of all/most of the reasonable paths,
 then choose the least expensive path.  The cost estimate is based on
 the estimated cpu, I/O, and sort requirements for each path.

 Each vendor's optimizer is different.  Learning to tune SQL queries is
 somewhat vendor specific, since there are some constructs that the
 optimizer fails to recognize.  In the case mentioned in the original
 note, the order of the predicates in the subquery is something which
 the IBM optimizer does not handle as well as you'd like.  What has been
 happening, is that the optimizers are getting better, and these type
 of situations get resolved in subsequent releases.  There are a number
 of "rules" that I discovered when tuning SQL/DS queries and applications
 that I needed to know in 1985, which I no longer need to know, since the
 optimizer now handles the transformations for me.

 I suppose the moral of this note, is that if you want to tune your SQL
 database then you have to understand the database's optimizer.  The
 manuals will give you some information, but often the real tricks to
 tuning come from experimentation and other users.  The optimizers will
 continue to improve, but then the queries are also getting more complex
 as we more fully utilize relational databases, so there may need to be
 this type of vendor specific tuning knowledge for some time to come.

 Regards,
 John F. Griffin
 Stellar Systems Group
 306 - 1095 McKenzie Avenue
 Victoria, B.C.  Canada  V8P 2L5
 Phone:(604) 479-7722  Fax:(604) 479-6526