< Day Day Up > |
Optimization in DB2 is usually a clear-cut matter. The DB2 optimizer is a state-of-the-art optimizer that, more often than not, can be relied upon to produce properly optimized access paths for SQL statements. The rule of thumb is to code as much work as possible into the SQL and let the optimizer figure out the best way to access the data. However, in a distributed environment, optimization is not quite so simple. To understand this difference, consider a distributed implementation of the DB2 sample tables PROJ , PROJACT , and ACT . A project ( PROJ ) can have many activities, and each activity ( ACT ) can be a part of many projects. The PROJACT table resolves the many-to-many relationship. For more information on these tables, refer to Appendix D, "DB2 Sample Tables." Assume that the PROJ and PROJACT tables exist at one location (say, Pittsburgh), and the ACT table exists at a different location (say, Chicago). The task at hand is to retrieve a list of documentation activities for projects started after January 1, 2000. If DB2 provides distributed request support, the following query would satisfy this request: SELECT A.ACTNO, A.ACTDESC FROM ACT A, PROJ P, PROJACT J WHERE A.ACTNO = J.ACTNO AND J.PROJNO = P.PROJNO AND A.ACTKWD = "DOC" AND P.PRSTDATE > "01/01/2000"; However, DB2 does not provide distributed request. Therefore, issuing this particular join is not possible. Lacking distributed request, what is the best way to satisfy this request? You can optimize this three-table join in (at least) six different ways:
Determining which of these six optimization choices will perform best is a difficult task. Usually, performing multiple smaller requests to a remote location is worse than making a single larger request to the remote location. In general, the fewer messages, the better performance will be. However, this rule of thumb is not always true. Try different combinations at your site to arrive at the optimal method of performing distributed queries. The optimal choice will depend on the following:
|
< Day Day Up > |