I have a calculated field that calls a script. That script retrieves a lookup value from the database based on the value in another field. This is quite slow because every record in the result set causes the database to be queried a second time to retrieve the lookup value. What can I do to improve the performance?

Rather than hitting the database for every record to retrieve the lookup value for that record, create an in-memory cursor the first time the script is called. On subsequent calls to the script, look for the value in the in-memory cursor instead of retrieving it from the database.

Here's an example. In this case, the script expects to be passed the ID value to look up in a table. The first time this script is called, the CategoryCursor cursor does not exist, so all records from the lookup table are retrieved into a cursor with this name, and an index is created on the cursor. The second and subsequent times the script is called, the CategoryCursor cursor does exist, so the ID value is looked up in the cursor, which is many times faster than retrieving the record from the database.

lparameters tiID
local lnSelect, loDatabase as Database, lcSelect, lcReturn
lnSelect = select()
loDatabase = SQApplication.DataEngine.GetMainDatabase()
if not used('CategoryCursor')
  lcSelect = 'select ID, CategoryName from Categories'
  loDatabase.ExecuteSQLStatement(lcSelect, .NULL., ;
    'CategoryCursor')
  index on ID tag ID
endif
if seek(tiID, 'CategoryCursor', 'ID')
  lcReturn = CategoryCursor.CategoryName
else
  lcReturn = transform(tiID)
endif
select (lnSelect)
return lcReturn

If you support multiple data sources, you likely want to close the cursor when the user chooses a different data source (or else you may use the wrong cursor full of lookup values), so create a script for the DataEngine.AfterDataEnvironmentCleared event and put something like this into it:

use in select('CategoryCursor')

That way, it's recreated the next time the user needs it.