cohend@roadkill.rtp.dg.com (Dave Cohen) (08/08/90)
Just to clarify a few things -
I meant to stick something in to the
effect that "database restructuring results in reoptimization
on-the-fly, such as the next time the statement is stored.
However, 'restructuring' must be defined in terms that
don't result in reoptimization every time, thus reducing
performance to the same level as dynamic execution. Thus,
a new index would cause reoptimization while the insertion/deletion
of 200 rows would not."
Also, the actual database engine optimizes and stores statements.
The responses I've got are: NCR offers an SQL precompiler
(not sure about platform), Data General offers an SQL precompiler
on its proprietary minis, and CA offers one for VAX/VMS and UNIX.
A few more comments - (heck, I'm losing net access, so flame away!) :
Also, dynamic SQL calls definitely must be supported, but can never
compete performance-wise with precompiled SQL. Currently, they can't
compete in portability either, since every vendor's call level
is completely different while an ANSI standard exists for embedded
SQL in C, COBOL, ADA, FORTRAN, and other languages. I'm not
sure how successful the effort to standardize call level interfaces
will be.
It will always be easier to build an embedded application than a
function call application, since you only have to know SQL syntax,
as opposed to SQL syntax and function call syntax. But I would
agree that debugging is harder for precompiled applications unless
the debugger recognizes the embedded source and not the generated
calls.
David Cohen | "There's nothin' wrong with goin'
cohend@dg-rtp.dg.com | nowhere, baby, but we should be
{world}!mcnc!rti!dg-rtp!cohend | should be goin' nowhere fast."
Data General Corporation, RTP, NC| - Streets of Fire mao@eden (Mike Olson) (08/09/90)
In <1990Aug8.153529.12164@dg-rtp.dg.com>, cohend@roadkill.rtp.dg.com (Dave Cohen) writes (regarding storing compiled query plans for later execution): > I meant to stick something in to the > effect that "database restructuring results in reoptimization > on-the-fly, such as the next time the statement is stored. > However, 'restructuring' must be defined in terms that > don't result in reoptimization every time, thus reducing > performance to the same level as dynamic execution. Thus, > a new index would cause reoptimization while the insertion/deletion > of 200 rows would not." the insertion or deletion of two hundred rows (or one row, for that matter) can change the selectivity of an index dramatically. consider what happens if the rows added or deleted contain columns used in joins in, say, a nestloop plan. the addition of a single row can force you to execute the nestloop again for every tuple in the inner loop. i agree that precompilation makes canned apps run faster, and that the speed win is critical in high-throughput environments, like airline reservations. but the database engine has to understand what changes invalidate the compiled plan, and has to do the right thing. this is a lot harder than it looks. to do it right, you need perfect information on database state. perfect information is very expensive. a reasonable compromise is to have a daemon that rifles your database nightly, while it's on-line, if you like. this daemon updates statistics on extant relations (key ranges, number of tuples and pages, index selectivity, and so on), and recompiles all your canned queries just for kicks. that way, a bad plan isn't likely to stay around for more than one day. remarkably enough, postgres takes exactly this approach to keeping db statistics current. mike olson postgres research group uc berkeley mao@postgres.berkeley.edu
davidm@uunet.UU.NET (David S. Masterson) (08/10/90)
In article <26852@pasteur.Berkeley.EDU> mao@eden (Mike Olson) writes:
i agree that precompilation makes canned apps run faster, and that the
speed win is critical in high-throughput environments, like airline
reservations. but the database engine has to understand what changes
invalidate the compiled plan, and has to do the right thing. this is a lot
harder than it looks. to do it right, you need perfect information on
database state. perfect information is very expensive.
I just wanted to point out something with respect to this thread. You said
"precompilation makes canned apps run faster" which is something altogether
different than "embedded SQL makes canned apps run faster". The two have
nothing to do with one another, agreed?
--
====================================================================
David Masterson Consilium, Inc.
uunet!cimshop!davidm Mtn. View, CA 94043
====================================================================
"If someone thinks they know what I said, then I didn't say it!"mao@eden (Mike Olson) (08/10/90)
In <CIMSHOP!DAVIDM.90Aug9200751@uunet.UU.NET>, cimshop!davidm@uunet.UU.NET (David S. Masterson) writes: > I just wanted to point out something with respect to this thread. You said > "precompilation makes canned apps run faster" which is something altogether > different than "embedded SQL makes canned apps run faster". The two have > nothing to do with one another, agreed? agreed. mike olson postgres research group uc berkeley mao@postgres.berkeley.edu