Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!rutgers!sunybcs!canisius!elgie
From: elgie@canisius.UUCP (Bill Elgie)
Newsgroups: comp.databases
Subject: Re: Do you like your UNIX RDBMS?
Message-ID: <2375@canisius.UUCP>
Date: 31 May 89 14:19:11 GMT
References: <9703@dasys1.UUCP> <3378@fp.sei.cmu.edu> <2907@osiris.UUCP> <May.30.18.32.22.1989.943@yes.rutgers.edu>
Organization: Canisius College, Buffalo N.Y. 14208
Lines: 41

In article <May.30.18.32.22.1989.943@yes.rutgers.edu>, emuleomo@yes.rutgers.edu (Emuleomo) writes:
> The problem with Embedded SQL is that is super lousy for browsing thru' a 
> database.  Suppose you issue a query and there are 1000 matching records.
> Are you aware that ALL the current ESQL implementations require that you
> WAIT FOR THE 1000 RECORDS TO BE FOUND EVEN BEFORE YOU CAN VIEW THE 1ST ONE?
> Thus your client will be FORCED to wait for up to 5 minutes before he/she 
> gets a response from a simple query!

  This sounds somewhat exaggerated, unless one is running on an extremely slow
  machine.  

  Example using INGRES; times are elapsed time in seconds from start of query
                        to end of all output: 

   select apx. 1000 records from a table containing apx. 35000 records.
   selection produced by a join to a similarly-sized table and a restriction
   by the value of an attribute in the second table.

   VAX 8550 -    9 secs;
   MIPS M/120 -  4 secs. 

  I don't know whether "all ESQL implementations require that you wait..." is
  true or not (I haven't had the privilege of trying all ESQL implementations)
  but several things should be kept in mind:

   1. in many cases, all records may have to be examined first to satisfy the 
      conditions of the query (one example is "sort by"..);
   2. depending on how many records are involved, their "size", organiza-
      tion of the physical files containing the data, and the configuration of
      the system(s) supporting the database, it may be more optimal to extract 
      the entire selection first, place it in a buffer, and then process it 
      record-at-a-time;
   3. (1) and (2) naturally lead to (3), which is that the quality of the query
      optimizer can have a major impact on all of this.  Automatically grabbing
      and displaying the "first record" is not necessarily the best strategy
      (or the correct one). 

   - and there are a "few" other details that may enter into this, such as the
     volatility of the data base, concurrency, etc.....

  greg pavlov (under borrowed account), fstrf, amherst, ny.