Path: utzoo!attcan!uunet!cimshop!davidm From: cimshop!davidm@uunet.UU.NET (David S. Masterson) Newsgroups: comp.databases Subject: Re: SQL Precompilers Message-ID: Date: 27 Jul 90 18:17:45 GMT References: <1990Jul26.141643.6361@dg-rtp.dg.com> Sender: davidm@cimshop.UUCP Distribution: comp Organization: Consilium Inc., Mountain View, California. Lines: 77 In-reply-to: cohend@dg-rtp.dg.com's message of 26 Jul 90 14:16:43 GMT In article <1990Jul26.141643.6361@dg-rtp.dg.com> cohend@dg-rtp.dg.com (Dave Cohen) writes: I would like to find out from experienced users/developers which SQL RDBMs offer true preoptimization. I'm referring to precompilers that process host languages with embedded SQL statements, optimize the statements based on database statistics and indexes, and generate calls that refer to some statement identifier. Simply massaging the SQL into calls that pass the actual statement text is definitely not good enough. Neither is forcing users to actually make calls that store that statement in the database (ie, Sybase stored statements). It appears that most of the database vendor hype ignores precompilation, but precompiled applications run faster and are easier to code than call level SQL. Why would you want the embedded approach to SQL? Regardless of how well the precompiler is implemented, the support for it by other products will never be good. Consider how many utilities in Unix are screwed up with embedded SQL in a C program (for instance, FCREF, INDENT and VGRIND). In my book, the function call approach for sending SQL statements to the database is the correct one (ashame I haven't been given the chance to use it). It breaks far less utility routines that are very important to the programmer. Also, if you shift back and forth between languages (like C and C++), there is no new syntax to learn or tricks to apply (like faking an SQL precompiler for C to not mess up C++ code). I believe that the statement "precompiled applications run faster and are easier to code than call level SQL" may be true with what is often supplied, but is wrong as a general statement. In order to optimize a precompiled statement the way that is suggested, certain aspects of the SQL must be fixed for that statement (relations to deal with, columns within the relations, operators to be applied). This cuts out any dynamic SQL capability. A very good precompiler might determine the difference between a statement that will be treated dynamically and one that will not and, therefore, make appropriate optimizations. However, that is not always possible (for instance): SELECT col FROM table WHERE col = :var; A precompiler might make the assumption that everything is known and, therefore, optimizable, but what if the user chose to fill "var" in with "1 or col = 2" at run-time. It changes the whole complexion of the statement (especially from the optimizer's perspective). This can get far more complex, but it shows the problems that can happen when mixing potentially dynamic statements with statements that can be optimized. The decision of what is optimizable and what isn't can be put in the hands of the user (he should know, right?) by either limiting what they can do to only optimizable statements or by extending the SQL statements to have some sentinel to inform the precompiler to perform optimizations. In doing this, though, what has been bought? (decreased flexibility or increased user confusion) A function call approach will generally tend toward the sentinel approach (although the sentinel is now the name of function, not a change to the SQL syntax), so there might be some programmer confusion, but it is just in learning new function calls, not a change in the language itself. Also, precompilers that worked with database engines typically embedded a set up call in the program to inform the database of an optimized function (after all, the compiled program might run against a copy of the database that it was compiled against which would not know about any statements stored in the original database). This could certainly be done by the programmer using a function call approach, its just not as automatic. In particular, I'd like to hear about the 'biggies' : DB2 (pretty sure this does), Oracle, Ingres, Informix, Sybase, Empress, etc. Britton-Lee did it with there early IDL/C precompiler, but I'm not sure if they do it anymore (there was more flexibility in not doing it). DEC's RDB/VMS precompiles SQL statements into macro assembler code that it then calls with a generated function call (that's what it looks like anyway). I don't believe Ingres did this in v5, have no idea about v6. Sybase did use a function call approach (no precompiler), but they may have expanded their repitoire since last I saw. Unsure about the others. -- =================================================================== David Masterson Consilium, Inc. uunet!cimshop!davidm Mt. View, CA 94043 =================================================================== "If someone thinks they know what I said, then I didn't say it!"