From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: QUEL and SQL in C.
Organization: 
Date: Tue, 10 Nov 1992 20:39:25 GMT

In article <1992Nov10.023910.26593@athena.mit.edu> thomasl@mtl.mit.edu
(Thomas J Lohman) writes:
> I am curious if anyone can answer this question for me.
> I was told by someone that Ingres [sic] used to translate all their
> ESQL into the EQUEL equivalent at runtime.  I guess this means
> that if you wrote an SQL application that eventually the QUEL
> package would be used.  This led people way back when not to
> transfer our software to ESQL.

This agrees with my understanding.  Prior to version 6, the INGRES
backend did not understand SQL directly.  It had to translate everything
into QUEL first.  This led to a few cases where you could express things
using non-ANSI SQL syntax, which would execute fine due to the greater
power of QUEL.  However, when we ported to version 6, the queries broke,
in the sense that INGRES would no longer execute them.  Of course this
immediately improved our understanding of ANSI syntax.

I'm not 100% sure about this, but I believe that the translation from
SQL to QUEL happened at runtime for queries entered at the terminal
monitor or in ESQL programs.  For ABF, there was a translator that
compiled an x.osq program (4GL with SQL) into x.osl (4GL with QUEL). 
This compile-time translation resulted in saving the cost of translation
at runtime.

> My question is, how are things done today?  Is it more efficient
> to have things in ESQL or EQUEL, or does it make a difference?

Today the version 6 server can talk both SQL and QUEL in native mode.
I'm not sure there would be a big difference between them in efficiency,
except in the sense that some queries are easier to write in QUEL than
in SQL (so QUEL is more efficient for the programmer to write).  I've
never written QUEL, so I can't back this up, but I've heard from several
QUEL fans who claims it is much more powerful than SQL.

The major determinants of good performance would be OPF (the optimizer),
QEF (query execution), and DMF (data manipulation--disk access and
cache).  These components of the server should act identically for SQL
and QUEL, because by the time a query hits OPF it has probably already
been translated into the query language-independent form of a query
tree.

The key component of good performance is still a good database design.

The ability to talk both SQL and QUEL is probably embodied in PSF (the
parser), which translates query text into a query tree and passes it on
to OPF.  In my experience, the parse phase is a trivial part of query
execution, and it's rare that I've ever even seen a session in PSF when
looking in iimonitor or IPM.  For most queries, execution time is
dominated by time spent in QEF (waiting for i/o or sorting), although a
rare query will spin its wheels in OPF for minutes on end (and lock
everyone else out if quantum is set properly).

It's only my opinion, but it appears to me that Ingres is _not_ devoting
a huge amount of energy to QUEL these days.  Standards being what they
are and influencing the market as they do, it seems to be a much bigger
priority for Ingres to meet full ANSI SQL conformance.  Ingres _has_
stated that they will continue to _support_ QUEL, but this is very
different from promising that they will add database procedures, rules,
and events to the QUEL language (which I don't think they have).

In spite of my personal distaste for the SQL language ("the COBOL of the
90's", in the words of my colleague Paul Ford), I think they've made the
right decision.

> Perhaps someone who is an expert on the low level optimization which
> takes place can help me...

What did you have in mind?

In article <1992Nov10.145305.29063@news.columbia.edu>,
alan@curta.cc.columbia.edu (Alan Crosswell) writes:

> I think the big performance difference these days has to do with using
> cursors vs. select loops -- which is documented in the release notes
> I believe.  Cursors are "standard" SQL. Select loops, while non-standard, are
> analagous to the QUEL retrieve loop (e.g. the only way to do it in QUEL
> prior to the addition of cursors to QUEL).  Select loops are faster
> than cursors -- but of course you can't do any other database operations
> within the body of a select loop.

Ingres claims that cursors run almost as fast as select loops if you use
prefetch rows (I think that's the default if you open it for readonly). 

BTW, you _can_ do other database operations inside a select loop if you
connect to another session, although care is required (to make sure you
don't deadlock against yourself).  See the "connect" and "set_sql(
session...)" statements for more information.  Our approach has been to
first load the data into memory in a fetch loop, and then cycle though
it in the host language for subsequent operations.  This eliminates many
problems having to do with concurrency and the size of transaction log
file.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.

Newsgroups: comp.databases.ingres,connect.audit