Newsgroups: comp.databases.informix
Subject: Find Next row - Performance questions
From: lebrun@admins.com (John LeBrun)
Date: Fri, 8 Jan 1993 17:10:55 GMT
Organization: Admins, Inc.


	I am in the process of porting our 4GL Development Product to
INFORMIX, our first port to a Standard SQL RDBMS.  Since our current
file system uses a variation of the standard index file method.  I
have found it difficult to support all of our existing operations, and
provide reasonable performance.

	The largest performance problem occurs when executing a
logical next/prev record request.  (To explain: a logical next record,
given a key of N columns and a value, find the next record in key
order) I currently have an implementation that is flexible enough to
return the correct request for any table or key.  I am looking for
comments and suggestions on ways to improve performance.


Algorithm:

	Next record is the minimum value of key for all rows
with key values greater than the current value of key.

	For a key of one column:

	select min(lname) from maillist where lname > 'lebrun'


	However; in a real application the key is usually multiple
columns.

	For a key of two columns:

	select lname, fname from maillist where lname = 'LeBrun' 
		and fname = (select min(fname) from maillist 
                             where lname = 'LeBrun' and fname > 'John')

        select lname,fname from maillist  
        	where lname = (select min(lname) from maillist 
                               where lname > 'LeBrun') 
               and fname = (select min(fname) from maillist 
		   where lname = (select min(lname) from maillist 
			          where lname > 'LeBrun'))


	In the above, lname is the primary column and fname is
secondary column of the key.  This example also assumes that
(lname,fname) is a unique key, otherwise a third column "rowid" would
be needed to determine uniqueness.  For each column in key there is a need to
select for each possible break level.  With the possibility of up to 9
columns in a key, this method can get very costly.

	On way to increase the performance of the above query is to OR
each break level query together and add an ORDER BY statement.  The
first record in the requesting table is the correct one, however; the
rest of the resulting table would not necessary be of value.  If all
the break level queries are OR'ed together, would the string exceed
the INFORMIX buffer size?  Or any other INFORMIX limits?

	In the above example would INFORMIX buffer the information
from each sub-query?  Should I (is it possible) to remove the
sub-queries?  

	How many and on which columns should indexes be placed?

	create unique index on lname,fname

	OR

	create unique index on lname,fname
	create index on lname
	create index on fname

	OR

	???
	

	What if I generate a query which selects all records greater
than the current one, ORDER BY 'key'.  Will INFORMIX determine that a
sort is not necessary?  What about this approach, if the table is very
large millions of rows?

	I have also needed to implement pervious, first and last.
Thank you for reading this posting.  If you can provide any
assistance, please respond either by e-mail or post.  I will post a
summary of answers, if such can be done with this type of question.



--
John LeBrun                           lebrun@admins.com
Admins, Inc.                          (617) 494-5100
432 Columbia Ave.                     "Providing 4GL development tools
Cambridge, MA                                since 1972"