Performance problems

I am attempting to carry out some extensive data cleaning in the OnNextCase event section of my DMS script. Because of the large number of records and variables in the data source, the script takes an excessive amount of time to complete. How can I improve the performance of my script?

If your script includes code that removes erroneous characters from text variables, make sure that you do not repeatedly look up the Response object. The following example looks up the Response object eight times for each variable and is therefore inefficient:

Sub CheckText(dmgrJob, Q)
  ' Remove all newline characters, parentheses, and
  ' hyphens from the response text...
  Q.Response.Value = Replace(CText(Q.Response.Value), mr.NewLine, " ")
  Q.Response.Value = Replace(CText(Q.Response.Value),"(","")
  Q.Response.Value = Replace(CText(Q.Response.Value),")","")
  Q.Response.Value = Replace(CText(Q.Response.Value),"-","")
End Sub

A better approach would be as shown in the following example. The Response object is now looked up only once or twice for each variable:

Sub CheckText(dmgrJob, Q)
  ' Remove all newline characters, parentheses, and
  ' hyphens from the response text...
  Dim strOriginal, strVal
  strOriginal = CText(Q.Response.Value)
  strVal = Replace(strOriginal, mr.NewLine, " ")
  strVal = Replace(strVal, "(", "")
  strVal = Replace(strVal, ")", "")
  strVal = Replace(strVal, "-", "")
  If strVal <> strOriginal Then
    Q.Response.Value = strVal
  End If
End Sub

It is also more efficient to check the responses to a categorical variable using an intersection operator instead of the ContainsAny function. For example, the following code sets a Don't Know special response if no categories were selected:

If (Q.Response.Value Is Not NULL) And Not
      (Q.Response.Value.ContainsAny({High, Medium, Low,
      DontKnow})) Then
  Q.Response.Value = {DontKnow}
End If

However, by rewriting the code to use an intersection operator (*), the number of look ups on the Response object is reduced by one:

If Q.Response.Value * {High, Medium, Low, DontKnow} = {} Then
Q.Response.Value = {DontKnow}
End If

Null variables will be handled correctly, because when Q.Response.Value is Null, the intersection will evaluate to Null, the comparison of Null with {} will be False, and a Don't Know response will not be set.