From: jcross@ec.uwa.oz.au (Jennifer Cross)
Newsgroups: comp.databases.ingres
Subject: Re: selecting a few rows from huge table
Date: 12 Oct 1992 06:35:33 GMT
Organization: The University of Westrn Australia
NNTP-Posting-Host: decel.ecel.uwa.edu.au

barr@hardy.u.washington.edu (David Barr) writes:

  Hi!

>At the hospital here, our users want to be able to scroll through the
>patient database (which contains approximately 900,000 records) using
>our ABF application.  They want to enter the first few letters of a
     ^^^-- not Vision? eg is this a "lookup" field/table?
>last name and have the matching names pop up in a table field.  Then
>they also want to be able to scroll up and down from there (possibly
>to names that don't match their original query).  The table field
>should be in order by last name.

>The problem is that selecting the entire table into a table field
>takes much too long.

  Why not use the ABF wildcard feature (*) and not load all of the table?
  eg: a field Name: CRO*
  would do a select like:
  select * from data where name like 'CRO%' and not return the whole
  (900k line table).  Of course if they get the inital few letters
  wrong they will have to reselect, not just keep scrolling.  Also
  if you made the name field manditory, you would reduce the load on
  the DBMS a lot.
  btw, in our installation ABF is using * for wildcard (match any number
  of characters) but ISQL still uses %    (going to have to fix that :-)

>One possible solution that I thought of (maybe not a very good one) is
>to have a separate table which acts as an index to the patient table.
>The only fields which that table would contain are last name and index
>number.  This table would contain only every hundredth record or so
>(alphabetically).  When the user enters a string to search for, first
>we would look it up in the index table, get the name which is closest
>to what they entered and use the index number to find out the next and
>previous entries in the index table.  This would give us a range for a
>much smaller select on the patient table.  In addition, we would still
>have the index numbers to get more records if they scrolled out of the
>region we originally selected.

  if you were going to do this, you could simply define a secondary
  index (with name as a key).  accessing an index, with the all the
  data required stored in the index is very quick.
  if you were to be using Vision and wanting this in the popup
  (scroll form), then definatly have Vision grab the external
  index (or just the fields specified in the modify,index key fields)

>This seems like it would work to me, but I still have one question.
>When I am selecting from my index table, what is the fastest way to
>select a single row which is greater than or equal to a string
>alphabetically?  I could use a select loop and only get the first
>record.  Is there a faster way?
  this depends on your index type... but in isam and btree tables,
  the LIKE operator does a good job)
  (See your Database Administrators Guide for lots of details about 
  each type of index)
>David

Yours, posting a lot today  :)
--    ___
     (   >                  /)                        (voice) +61 9 362 6680
      __/_/> ____  ____  o //  _  __            (home)  cjcross@DIALix.oz.au
     / / (__/ / <_/ / <_<_//__</_/ (_ @ Beautiful Perth, Western Australia
    <_/                  />                     (work) jcross@ecel.uwa.oz.au
                        </                            (voice) +61 9 380 3968