Adding indexes can speed up your applications in several ways. One method that allows very quick data retrieval involves creating a computed column of unique values based on one or more columns.
For example, here is part of a program that helps in the scheduling of flight simulators for pilot training. A simulator code (scode) in combination with a date (sdate) uniquely identifies each row in the table. To make WHERE clauses fast, the following indexed computed INTEGER column (sindex) contains this expression:
sindex = (JDATE(sdate) + (scode * 100000)) INTEGER
The application prompts for a simulator code and date, which it puts in two variables: vscode (INTEGER) and vsdate (DATE). Then to quickly find the row, the application uses the following code (replace "command" with any command that uses a WHERE clause):
SET VAR vsindex = (JDATE(.vsdate) + (.vscode * 100000))
command ... WHERE sindex = .vsindex
The above code proved twice as fast as this slower alternative:
command ... WHERE scode = .vscode AND sdate = .vsdate
The more rows you have, the greater the efficiency.
