The column is a very generic and random text of comments so I don't believe
the IHASH would work in this case, although now that you have given me a good lesson on it, I may be able to apply it to some other searching routines I have.
Thank you all.
I will take Davids advice and do some deep research and testing on this. I was
just wondering what others have done.
Again, thank you all Jim Limburg
MikeB wrote:
Well, you might drag out the IHash function. I can't find IHash Documented in 65, but it's in the 7.0 docs.
IHASH
(IHASH(arg,n))
R:BASE includes a function that can be used to create an integer value from a text value. The function was designed to create effective integer keys from long text columns. The function, IHASH, converts the entire text value, or just a specified number of characters.
Using this method is more complex that just indexing the LASTNAME column. First you need to add a computed column to your table using the IHASH function on the LASTNAME column to convert its text to integer values. You can modify your table through the Object Manager or use the ALTER TABLE command:
ALTER TABLE employee ADD Hash_Lname=(IHASH(lastname,0)) INTEGER
The IHASH function converts the entire name to integer when used with the parameter 0. A different parameter converts the specified number of characters from the name, starting at the first character. For example, the parameter 7 will convert the first 7 characters of the lastname to an integer value. Deciding on the number of characters to convert can be one of the hardest things about using this method. Consider the relationships expressed in the following chart:
Convert FEW characters Convert MORE characters
PROS Lless input required Lless duplicate values
CONS Greater duplicate values More input required
After adding the computed column to your table, you need to use some programming commands as shown below to query that column. Using the IHASH function directly in a WHERE clause won't use indexes. First set a variable equal to IHASH of the value you're searching for, then use the variable in the WHERE clause. When using an IHASH column for searching, you won't be able to do ad hoc queries from the R:BASE main menu .
SET VAR vname = (IHASH('Smith',0)) SEL * FROM employee WHERE Hash_Lname = .vname
This method does provide greater flexibility in that you can have users enter anywhere from 1 character to the entire name based on the number of characters you specify in the IHASH function. For example, add a computed column to the table that will IHASH the first four characters of the name. Then, in your program, check the length that the user enters and if it's greater than four characters use an extra condition on your WHERE clause.
FILLIN vname USING 'Enter lastname (at least 4 characters): ' SET VAR vlen1=(SLEN(.vname)),vname1=(SGET(.VNAME,4,1)),+ vhash=(IHASH(.vname1,4)) IF vlen1 > 4 THEN CHOOSE vchoice FROM #VALUES FOR (firstname & lastname) + FROM employee WHERE Hash_Lname=.vhash AND + (SGET(lastname,.vlen1,1))=.vname ELSE CHOOSE vchoice FROM #VALUES FOR (firstname & lastname) + FROM employee WHERE Hash_Lname=.vhash ENDIF
A user can enter any number of letters for use with an IHASH computation, but must enter at least as many characters as specified in the IHASH column definition or enter the full name. If the entry less than the specified number of characters and less than the full length of the name, the correct data is not found. For example, with a column defined as (IHASH(lastname,7)), entering "WILL" will not find "WILLIAMS", it will only find "WILL".
The advantages of using a computed column with the IHASH function are that you can turn an inefficient TEXT index into an efficient INTEGER index and you can provide flexibility in searching. Users will have a larger selection of names to choose from and can select the appropriate person from the list. For example, entering WILLIAM will find WILLIAMSON, WILLIAM, and WILLIAMS if you use IHASH(lastname,7).
A disadvantage of this method is that you need to add columns to your database. An extra computed column can slow down data entry. If you are tight on disk space this may not be an option. To determine how much additional disk space you'll need for an IHASH column, take the number of rows in the table and multiply by 4. The answer is the number of bytes of disk space you'll need for the additional column.
----- Original Message ----- From: "Jim Limburg" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
Sent: Tuesday, September 09, 2003 3:04 PM
Subject: [RBASE-L] - RBG65 - To Project of not to Project
G-Day all
Which situation by you all's experience would be faster. I have a table which had about 30 columns, most of which are small - integers,1-8 size in text and so on. One of the columns is text 100. This table has about 80,000 rows in it. The text 100 column is indexed, but searching is somewhat slow. The table has a PK - integer. Would it be faster to project a temp table to a user who searches if frequently, of just the PK and the text 100 column. The user who does this searching wouldn't need it updated each time. I could put a refresh data button on to reload the temp table, so the user could do that a few times a day.
Or is it just faster to search on the table that has the 30 columns. I know indexing is suppose to "put things in ram" so to speak, or at least that's how I've figured it, so I'm not sure at this point what would be best. I'm curious to find out some of you guru's findings in areas like this.
Jim Limburg
