Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!usc!zaphod.mps.ohio-state.edu!sdd.hp.com!decwrl!nsc!amdahl!JUTS!rbw00 From: rbw00@ccc.amdahl.com ( 213 Richard Wilmot) Newsgroups: comp.databases Subject: Re: SQL Precompilers Keywords: efficiency Message-ID: Date: 27 Jul 90 01:05:04 GMT References: <1990Jul26.141643.6361@dg-rtp.dg.com> <0afnkLa00UhBQ1qkxT@andrew.cmu.edu> Reply-To: rbw00@JUTS.ccc.amdahl.com ( 213 Richard Wilmot) Organization: Amdahl Corporation, Sunnyvale CA Lines: 28 Regarding precompiling of SQL queries Bruce E. Golightly wrote: >I'm not sure that you'd like the kind of pre-optimization you described. If the >characteristics of the data base changed to any real extent, performance >could fall right through the floor. Good performance can be related to >optimization that makes use of statistics on the data base AS IT PRESENTLY >EXISTS. Old stats may be very misleading!! While it is true that query efficiency can suffer from reliance on out-of- date population and value distribution statistics, there are many applications where dynamically determining the access path is not efficient. The type of transaction used in the TPC benchmarks, for example, will benefit markedly from precompiling (by the query optimizer) the database accesses. TPC benchmarks are very short financial update transactions. It might be worthwhile someday for the DB vendors to arrange their systems so that growth beyond some thresholds in certain populations/value sets could automatically trigger recompilation of designated queries. I understand that precompiled DB2 access modules are invalidated and recompiled when there are any structural changes to associated tables or indexes. In highly repetitive systems (e.g. banking, airlines) compilation is popular for because one can amortize a single reptition of optimization over many executions of the same code. -- Dick Wilmot | I declaim that Amdahl might disclaim any of my claims. (408) 746-6108