Path: utzoo!utgpu!news-server.csri.toronto.edu!mailrus!cs.utexas.edu!know!zaphod.mps.ohio-state.edu!sdd.hp.com!decwrl!ucbvax!mtxinu!rtech!ingres!llama!jas
From: jas@llama.Ingres.COM (Jim Shankland)
Newsgroups: comp.databases
Subject: Re: SQL Precompilers
Message-ID: <1990Jul31.014419.5294@ingres.Ingres.COM>
Date: 31 Jul 90 01:44:19 GMT
References: <1990Jul26.141643.6361@dg-rtp.dg.com> <4907@infmx.UUCP>
Reply-To: jas@llama.Ingres.COM (Jim Shankland)
Organization: The Eddie Group
Lines: 28

In article <4907@infmx.UUCP> davek@infmx.UUCP (David Kosenko) writes:
>	Are you sure you want optimization of embedded SQL statements
>to occur at compile time?  Consider what would happen if your schema
>changed (adding/dropping indexes) or the profile of your database
>changed (e.g. a previously small table grew very large).  That implies
>having to recompile your application for optimal performance for
>the new situation.

All true.  Life is hard.  You have to weigh this against the cost of
re-compiling the query every blessed time you run it.  Also, it's not
too hard to invalidate stored query plans when, e.g., indexes are added
or dropped.  Finally, queries can be recompiled by the DBA from time
to time (nightly is easy if it's not a 24-hour shop).

>	Informix embedded languages will pass the SQL string to the
>engine process via standard function calls.  The engine will then
>optimize the statement for processing.  Why is this not "good enough"?
>Doesn't that provide the most dynamic and optimal possibility for
>statement optimization?

It's not good enough because the cost of optimization may dwarf the cost
of execution.  If the query is going to be executed thousands of times,
this can be a real lose.  It's analogous to recompiling all C programs
every time you run them because a system header file may have changed.

jas

These are my opinions; Ingres Corp. neither knows nor cares about them.