Please enable JavaScript to view this site.

R:BASE 11 Help

Navigation: Reference Index > Indexes

Indexing Computed Columns

Scroll Prev Top Next More

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.