Re: SQL Precompilers (preoptimize!)

Path: emory!swrinde!elroy.jpl.nasa.gov!orchard.la.locus.com!prodnet.la.locus.com!jfr From: jfr@locus.com (Jon Rosen) Newsgroups: comp.databases.informix Message-ID: <1992Mar04.203112.01004902@locus.com> Date: 4 Mar 92 20:31:12 GMT References: <1992Mar4.160455.12513@rock.concert.net> Organization: Locus Computing Corp, Los Angeles In article <1992Mar4.160455.12513@rock.concert.net> davec@rock.concert.net (David Cohen -- DBLinx) writes: >I worked on an RDBMS at Data General a few years back, and the precompiler >actually preoptimized and stored statements (ie, like a single statement >Sybase stored procedure). What other database managers do this, and >what major platforms do they run on? > >Of course, Sybase has stored procedures. I've heard that Britton-Lee has >a preoptimizing precompiler, as does Empress (?). But I'm not certain. > DB2 and SQL/DS have excellent static SQL optimizers. In fact, SQL plans and packages are actually turned into executable machine code in DB2 rather than some kind of intermediate optimized form. The actual optimization for DB2 takes place at "bind" time. During the precompile, the SQL statements are stripped from the program and placed into a DBRM (data base request module) file which is then bound into a compiled form with a BIND command. This allows the object code of a program to be distributed to a customer along with the DBRM. The customer can install the object code of the program and BIND the DBRM without getting their hands on the source code. Teradata's DBC/1012 optimizes at execution time but places the optimized code into a cache which eliminates subsequent optimization of identical statements as long as the cache doesn't fill up. I do not believe that Sybase's stored procedures are actually optimized (i.e., parsed) prior to execution. I may be wrong about this however. Oracle and Ingres merely convert static SQL at precompile time into strings which are passed to the DBMS at execution for parsing and optimization. Jon Rosen