=======================================================================
PUTTING IHASH TO WORK
=======================================================================
PRODUCT: R:BASE VERSION : 3.1A Or Higher
=======================================================================
AREA : PROGRAMMING CATEGORY: TEXT SEARCHES DOCUMENT#: 668
=======================================================================
From: Royal E. Harris, C.N.E.
Crowley Maritime Corp.
14018 4th Ave. East
Tacoma, WA 98445
(206) 537-8977
Did you ever have a problem with the execution speed of an
application, and it wasn't that the speed was too fast? You set up
indexes, verified uniqueness, tinkered with memory, upgraded CPU's,
installed the latest network OS, and still weren't satisfied with the
performance? As a last resort did you look through past issues of
the R:BASE Exchange and you actually find something that might solve
the problem?
The problem
-----------
Here was just such a situation. A customer table search had run
amuck. My mission? Speed the search up. As the network manager,
transporting a multi user database, I desired to allow users (armed
with only the customer's name) to select and edit a particular
customer file. No tacked up lists of customer identification numbers
allowed. All that our users need to know is the alphabet well enough
to partially spell a name.
The customer table has two indexed columns: FINDNAME column (TEXT 50)
and a CUSTNUM (TEXT 10). As the names suggest, the FINDNAME column
is made up of last & first names, and the CUSTNUM column is a
customer number. The application instructs the user to enter one or
more letters of the customer name; then, using the CHOOSE command to
pop-up a menu, pick which customer to edit.
Here is the original code:
SET MANY=*
SET VAR vsearch TEXT vcustnum TEXT
FILLIN vsearch USING "Enter customer search text: " at 9 6 REVERSE
SET VAR vsearch = .vsearch + "*"
CHOOSE shown FROM #VALUES FOR FINDNAME FROM CUSTOMER WHERE +
FINDNAME LIKE .vsearch ORDER BY FINDNAME ASC CLEAR
CLEAR VAR vsearch
SET VAR vcustnum = CUSTNUM IN CUSTOMER WHERE FINDNAME = .shown
EDIT USING FORMNAME WHERE CUSTNUM = .vcustnum
This worked well. An "H" entered displayed all customers with a
FINDNAME beginning with "H", and "HA" displayed only those customers
with FINDNAMES beginning "HA", and so on. The user need only know
names, no customer numbers. It was a good trick until the customer
list grew, and grew, and grew out of control.
Now it only worked, not necessarily well. The search began to
consume full seconds of user time. It was annoying to wait six
seconds for each and every customer edited to appear in the menu. We
had to find a way to speed up the application or to eliminate
customers. Shareholders tend to frown on the latter.
The solution
------------
We are now at the moment of desperation. I find the July/August 1991
issue of the "R:BASE Exchange" (v. 8 n. 4), and see the article
'Indexes & IHASH Add Speed'. Every word on pages 14 and 15 of that
issue is correct, but the article has no direct use to us because our
WHERE clause contained the LIKE condition. LIKE and integer
comparisons do not mix; never have, never will.
We can't be telling our users that they must key in the exact
customer name (FINDNAME) all the way to 50 characters, if neccessary,
in the cause of "increased performance", now can we. That is
exactly what we'd have to do if we intended to use an indexed IHASH
column with an '=' condition. The users were already used to keying
an "H", hitting enter, and magically getting all the "Harris"'s in
the customer file on their screen.
So I wondered how the IHASH function could possibly be of
assistance. It is a simple case of having it both ways, IHASH
(integer based performance) and FINDNAME (text based ease of use).
The idea may not appear to be extremely elegant, but it is extremely
robust. After implementation, the execution of the search is more
than ten times as fast.
Two simple steps are involved in implementing the idea. First, add
an additional column to the customer table named HFN for 'hashed
findname'. The HFN column is a computed integer column,
(IHASH(FINDNAME,3)), and it is indexed.
Yes, the idea was as simple as 1,2,3. Courtesy of R:BASE, the first
three letters of the FINDNAME column would be hashed. But didn't
that yield duplicates in the HFN column, you know, many "Harris"'s?
Of course it did, but remember, we're using the CHOOSE command to
provide a choice. But what if the user doesn't know the first three
letters of the FINDNAME he wants to edit? What if he only knows one
letter; ie "H"?
We solved this dilemna with a little R:BASE slight of hand. We let
the one letter users find the customers they want to edit in the usual
six second fashion, the users who know three letters, get the speed.
This brings us to the second of the two steps, the revised
application code:
SET MANY=*
SET VAR vsearch TEXT vcustnum TEXT
FILLIN vsearch USING "Enter customer search text: " at 9 6 REVERSE
IF_(SLEN(.vsearch)) = 3 THEN
SET VAR vsearch = (IHASH(.vsearch,3))
CHOOSE shown FROM #VALUES FOR FINDNAME FROM CUSTOMER WHERE +
HFN = .vsearch ORDER BY FINDNAME ASC CLEAR
ELSE
SET VAR vsearch = .vsearch + "*"
CHOOSE shown FROM #VALUES FOR FINDNAME FROM CUSTOMER WHERE +
FINDNAME LIKE .vsearch ORDER BY FINDNAME ASC CLEAR
ENDIF
CLEAR VAR vsearch
SET VAR vcustnum = CUSTNUM IN CUSTOMER WHERE FINDNAME = .shown
EDIT USING FORMNAME WHERE CUSTNUM = .vcustnum
The users are delighted with the gain in performance, and they can
still be as precise or as vague as they please with the customer
name. They are mostly using the first three alpha characters to
identify which customer to edit, however, because of the speed. Here
is a pleasant R:BASE solution to a speed dilema.