White papers > Data Management performance guidelines white paper > Improving performance in the OnNextCase event
 
Improving performance in the OnNextCase event
Introduction
This topic provides some general scripting guidelines for improving performance in the OnNextCase event. While many other guidelines could have been included for improving the reuse and maintainability of scripts, the focus is techniques that will improve performance.
Use temporary variables
When a series of statements uses the same expression, for example, you are comparing the result of a function call several times, use a temporary variable to store the expression result instead of repeating the expression.
In the following example, the defined categories are being requested for the Desired variable in each expression:
If BrandAttr[{ABC}].Attribs.ContainsAny(Desired.DefinedCategories()) Then
DesiredBrands = DesiredBrands + {ABC}
End If
If BrandAttr[{Green}].Attribs.ContainsAny(Desired.DefinedCategories()) Then
DesiredBrands = DesiredBrands + {Green}
End If
Given that the DefinedCategories function involves a look-up into metadata, it would be more efficient to call the function once and store the result in a temporary variable.
Dim DesiredAttributes DesiredAttributes = Desired.DefinedCategories()If BrandAttr[{ABC}].Attribs.ContainsAny(DesiredAttributes) Then
DesiredBrands = DesiredBrands + {ABC}
End If
If BrandAttr[{Green}].Attribs.ContainsAny(DesiredAttributes) Then
DesiredBrands = DesiredBrands + {ABC}
End If
Similarly, if the expression was used within a For...Next statement, consider using a temporary variable. For example:
Dim Brand, DesiredAttributes DesiredAttributes = Desired.DefinedCategories()For Each Brand in BrandAttr
If Brand.Attribs.ContainsAny(DesiredAttributes) Then
DesiredBrands = DesiredBrands + CCategorical(Brand.QuestionName)
End If
Next
Benefits of using temporary variables
Potentially less script needs to be written; as the expression does not need to be repeated. Declaring and assigning the temporary variable might, however, result in more lines of script.
Improved performance as the expression is only executed once. Converting the bench-mark script to use temporary variables where expressions were repeated more than twice resulted in an improvement of approximately 8%.
Use the With statement
When a series of statements applies to the same object, for example, you are comparing the value of different nested questions on the same class, use a With statement to define the portion of code that is common to all statements rather than typing each statement in full.
For example, in the following script questions from a nested loop are being summarized:
If BrandAttr[{ABC}].Model[{X}].Rating = {_1} Then
ABC_X_Summary = ABC_X_Summary + {Rating}
ElseIf BrandAttr[{ABC}].Model[{X}].Quality = {_1} Then
ABC_X_Summary = ABC_X_Summary + {Quality}
ElseIf BrandAttr[{ABC}].Model[{X}].Price = {_1} Then
ABC_X_Summary = ABC_X_Summary + {Price}
End If
Using With reduces the script to the following:
With BrandAttr[{ABC}].Model[{X}] If .Rating = {_1} Then
ABC_X_Summary = ABC_X_Summary + {Rating}
ElseIf .Quality = {_1} Then
ABC_X_Summary = ABC_X_Summary + {Quality}
ElseIf .Price = {_1} Then
ABC_X_Summary = ABC_X_Summary + {Price}
End If
End With
As another example, this time from an interview script, use With when assigning several properties on the same object:
Sports.Label.Style.Font.Family = "'Palatino', 'Times New Roman'"Sports.Label.Style.Font.Size = 16Sports.Label.Style.Font.Effects = FontEffects.feBold
Use With to reduce the script to the following:
With Sports.Label.Style.Font .Family = "'Palatino', 'Times New Roman'"
.Size = 16
.Effects = FontEffects.feBold
End With
Benefits of using the With statement
Calls to the object are grouped within the With statement making the script easier to read and maintain.
Less script needs to be written; the full object name does not need to be repeated.
Improved performance when the object being accessed is from a child collection in the object hierarchy. Using the With statement causes the object look-up to only occur once. Converting the benchmark script to use the With statement gave a performance improvement of approximately 8%.
Use Select Case to simplify If...Then...Else logic
When a series of conditional statements perform comparisons on the same variable, use a Select Case statement instead of If...Then...Else logic.
For example, in the following example If...Then...Else logic is used to band a numeric variable:
If Brands[{Green}].Quantity = 0 Then
GreenQuantity = {Band1}
ElseIf Brands[{Green}].Quantity >= 1 And Brands[{Green}].Quantity <= 5
GreenQuantity = {Band2}
ElseIf Brands[{Green}].Quantity > 5
GreenQuantity = {Band3}
End If
Using Select Case reduces the script to the following:
Select Case Brands[{Green}].Quantity
Case 0
GreenQuantity = {Band1}
Case 1 To 5
GreenQuantity = {Band2}
Case > 5
GreenQuantity = {Band3}
End Select
Note The comparison operators perform set comparisons when used with categorical variables. Set comparisons can be especially useful when used in the Select Case statement. For example, the following case statement will be selected if the BrandsQ contains the Red category:
Select Case BrandsQ
Case >= {Red}
As another example, when the variable being compared is a single response categorical, known to contain 1 answer, the following syntax can be used:
Select Case Country
Case <= {Brazil,Chile,Colombia,Ecuador,Paraguay,Peru,Uruguay,Venezuela}
Continent={South_America}
This case statement would also be selected if Country was NULL or empty {}. The script would also need to check that the question was answered.
Benefits of using the Select Case statement
Less script needs to be written; the variable being compared does not need to be repeated. The script is also easier to read and maintain.
Improved performance when the object being compared is from a child collection in the object hierarchy. Using the Select Case statement causes the object lookup to only occur once.
Use For Each...Next when setting multiple properties
Use a single For Each statement rather than [..] when setting more than one property on objects in a collection. Each instance of [..] is equivalent to a For Each statement and it is more efficient to only iterate through the collection once.
For example, in the following script each of the nested questions in a loop are initialized using [..]:
BrandsLoop[..].Price = NULL
BrandsLoop[..].Rating = NULL
BrandsLoop[..].Quantity = 0
For better performance, the script should instead be written as the following:
Dim Brand
For Each Brand in BrandsLoop
With Brand
.Price = NULL
.Rating = NULL
.Quantity = 0
End With
Next
Note The [..] should still be used in cases where just one property is being set on the objects of the collection being iterated.
Benefits of using the For Each...Next statement
Calls to the object can be grouped within a With statement making the script easier to read and maintain.
Improved performance as the collection is only iterated once.
Use categorical literals
Use categorical literals instead of string literals when performing categorical operations. Categorical literals are required when the category names are only available at runtime (as in the case of database questions).
For example, in the following a string literal is used to simplify the lookup of key attributes in the attributes loop:
Dim Attribute
For Each Attribute in Split("Price,Quality,Appearance", ",") KeyBrands = KeyBrands + Attribs[Attribute].Brands
Next
Instead of using the string literal, a categorical literal should be used as follows:
Dim Attribute
For Each Attribute in {Price, Quality, Appearance} KeyBrands = KeyBrands + Attribs[CCategorical(Attribute)].Brands
Next
Note When using For Each...Next to iterate over a categorical list, the category value is stored in the ‘each’ variable as a long. The CCategorical function needs to be used to convert the category value into a categorical so that it can be used for lookup into question or category collections. If the value is not converted to a categorical, the collection interprets the value as a numeric index and attempts to retrieve the item by its position in the collection, not by its category value.
Benefits of using categorical literals
Categorical literals are converted to category values at parse time, so the category names are validated when the script is parsed, not when the script is run. This can help identify mistyped names earlier in the DMS process.
Performance can be improved when categorical literal is being used in categorical expressions.
Use categorical set logic
Use categorical set logic to combine, filter, and remove answers. The operators that can be used with categorical sets are as follows:
Union
The union (+) operator performs a union of two category lists. The union operator should be used when combining the responses from two different questions.
In the following example, the total awareness question is assigned the union of the spontaneous and prompted questions:
TotalAwareness = Spontaneous + Prompted
Intersection
The intersection (*) operator performs an intersection of two category lists. The intersection operator should be used when filtering one category list by another.
In the following example, the answers from Q4Tot are being filtered by the categories in Q5:
For Each Code in Q5.Categories
If Q4Tot.ContainsAny(Code) Then Q5 = Q5 + CCategorical(Code)
Next
By using the intersection operator, the script can be simplified as follows:
Q5 = Q4Tot * Q5.DefinedCategories()
Difference
The difference (-) operator returns the categories that are in the category list on the left but not in the category list on the right. The difference operator should be used when removing responses.
In the following example, special responses are being removed from the analysis variable:
AnalysisBrands = BrandsQ - {DK, NA}
XUnion
The exclusive union returns the categories that are in either category list, but not in both category lists. The exclusive union operator should be used to remove categories that are answered in both questions.
Inn the following example, brands selected for price or quality are returned, but brands selected for both are not.
BrandsPriceOrQuality = Attribs[{Price}].Brands / Attribs[{Quality}].Brands
Each of the categorical operators also has a corresponding function that can be used when the operators are not well understood. The set operation names are the names given to each the functions (instead of using the intersection (*) operator, the function can be used).
Q5 = Intersection(Q4Tot, Q5.DefinedCategories())
The category order from the left-hand side is maintained when performing categorical set operations. If the order of mention is important, the question where the order of mention needs to be preserved should be on the left-hand side of the expression.
In the following example, the answered categories are being filtered, but the order of mention is preserved from the brands question.
AnalysisBrands = BrandsQ * {Red, Green, Blue, Yellow}
As another example, the following script adds categories to the banner variable using a specific order:
If Segments.ContainsAny({Seg8}) Then Banner=Banner+{Seg8}
If Segments.ContainsAny({Seg5}) Then Banner=Banner+{Seg5}
If Segments.ContainsAny({Seg3}) Then Banner=Banner+{Seg3}
If Segments.ContainsAny({Seg2}) Then Banner=Banner+{Seg2}
If Segments.ContainsAny({Seg4}) Then Banner=Banner+{Seg4}
If Segments.ContainsAny({Seg6}) Then Banner=Banner+{Seg6}
If Segments.ContainsAny({Seg1}) Then Banner=Banner+{Seg1}
If Segments.ContainsAny({Seg7}) Then Banner=Banner+{Seg7}
Using the intersection operator, the script could be simplified as follows:
Banner = {Seg8,Seg5,Seg3,Seg2,Seg4,Seg6,Seg1,Seg7} * Segments
Benefits of using categorical set logic
Less script needs to be written and once the operators are understood, the script is easier to read and maintain.
Improved performance when compared to the style of script listed for the intersection operator. Converting the benchmark script to use the intersection operator instead of For Each...Next when filtering category lists gave a performance improvement of approximately 5%.
Loop logic
When implementing loop logic, place any script that only needs to execute once outside of the loop. This is particularly important for scripts that are consume an inordinate amount of time to execute, such as scripts that create a database connection or retrieve a file from a web server. For example, instead of using the following:
Dim category
For Each category in Q1.Categories
' The database connection is recreated for every category
Dim connection_string, connection
connection_string = "Provider=MSOLEDBSQL.1; Data Source=" + _
DATASOURCE + ";Initial Catalog=" + DBNAME + _
";Integrated Security=SSPI;Persist Security Info=False"
Set connection = CreateObject("ADODB.Connection")
connection.Open(connection_string)

' Use the connection for this category
...
Next
Use this instead:
' Create the database connection once for all categories
Dim connection_string, connection
connection_string = "Provider=MSOLEDBSQL.1; Data Source=" + _
DATASOURCE + ";Initial Catalog=" + DBNAME + _
";Integrated Security=SSPI;Persist Security Info=False"
Set connection = CreateObject("ADODB.Connection")
connection.Open(connection_string)

Dim category
For Each category in Q1.Categories

' Use the connection for this category
' ...
Next
Removing redundant script
Take care when modifying script to remove redundant script. When removing questions from an interview script, the tendency is often to remove just the Ask statement without removing the logic used to initialize the question.
See
Data Management performance guidelines white paper