Queries

When performing queries in Axxerion in scripting, or when debugging performance issues, be aware of the following aspects that negatively effect performance.

Client specific fields

Do not use JUST client specific fields in queries if you can. For doing this, Workplace will have to always combine two data tables, which will require the system to always search a larger table (the one with client specific fields) than the core objects. Always try to add a Workplace core field (most commonly createTime, updateTime, externalReference, closeTime, categoryFolderId).

If you do need to use client specific fields, try to limit them. The more fields you add, the more data that will need to be integrated, which in turn might take more time to run. So if you have a slow query, check if removing client specific fields has a positive effect (or vice versa, adding them has an adverse effect).

Empty query parameters

When using variables in a query, make sure they have values added to them. See the below script.

contact = this;
manager = contact.managerContactId;
colleagues = find Contact by managerContactId == manager;

What you expect the script to do is to get the manager from the current contact. Then you use that manager to find all the other contacts assigned to that manager. There is a catch though: if this script runs on a contact that does not have a manager, the script will then search for ALL contacts. A better solution is to do the following.

contact = this;
manager = contact.managerContactId;
if (!isEmpty manager) {
  colleagues = find Contact by managerContactId == manager;
}

The above will check if the value for manager is empty. If that is not the case, then you start the rest of the logic. A better option is to actually make break clauses in your scripting, which stops the script if it is not necessary anymore. See below.

contact = this;
manager = contact.managerContactId;
if (isEmpty manager) {
  return;
}
colleagues = find Contact by managerContactId == manager;

This will make the script more readable and “safe”, as it terminates the logic once it should stop.

Find versus Get

In scripting, when using a “Find”, all items that match the parameters will be returned. When using a “Get”, only one item is returned. Even though only one item is returned, the “Get”-query is not quicker. Behind the screens, Workplace does a “Find” and just returns 1 item. So be aware that using this is not quicker.

Combine queries

In Workplace, it is possible to “link through” in queries. See the below scripting.

contact = this;
manager = contact.managerContactId;
if (isEmpty manager) {
  return;
}
colleagues = find Contact by managerContactId == manager;
properties = [];
for each colleague in colleagues {
  properties = properties + find Property by ownerContactId == colleague;
}

Assuming the query on line 6 returns 10 colleagues, the “for each” will execute the properties query 10 times. A more optimal way is to combine the separate queries.

contact = this;
manager = contact.managerContactId;
if (isEmpty manager) {
  return;
}
properties = find Property by ownerContactId == NULL, Contact.managerContactId == manager;

What the scripting does here is to go from the object Property to the ownerContactId and then searches all the Contact who belong to a certain manager. This effectively lowers the total query amount from 11 to 1.

Queries with client specific fields/attributes

When performing a query using a client-specific field, this would potentially be quite small when using large numbers of objects (i.e. CodeTypes). In some cases, it might be quicker to collect all items and check on the specific value that you are interested in, instead of directly searching for the client-specific field value. This should be decided on a case-by-case basis.

reference = "REF-12345";
category = get Folder by reference == "CAT-12345";
var oneSpecificObject;

# Normally, you would do something like this:
if (!isEmpty reference) {
  oneSpecificObject = get CodeType by categoryFolderId == category, "acme-ExternalReference" == reference;
}

# But sometimes, this is quicker:
if (!isEmpty reference) {
  objects = find CodeType by categoryFolderId == category;
  for each object in objects {
    if (object."acme-ExternalReference" != reference) {
      continue;
    }
    oneSpecificObject = object;
    break;
  }
}

Echo’s in scripting

It turns out that echo’s in scripting add to the used heap space on the server. So removing echo’s after you are done debugging is a great way to prevent issues on the server. If want a big bang to clean this up inside a client environment, you can run the following script in that environment:

client = user.clientId;
scripts = find Script by clientId == client, scriptVersionId == NULL, ScriptVersion.scriptText contains "echo";
activeScriptVersions = [];
for each script in scripts {
    activeScriptVersions = activeScriptVersions + script.scriptVersionId;
}

for each scriptVersion in activeScriptVersions {
    scriptText = scriptVersion.scriptText;
    newScriptText = scriptText ~r/\s*echo.*//;
    newScriptVersion = copy scriptVersion;
    set newScriptVersion.scriptText = newScriptText;
    set newScriptVersion.description = "Automatic echo removal";
    set newScriptVersion.scriptStatusCode = Enums.ScriptStatus.active;
    script = newScriptVersion.scriptId;
    set script.scriptVersionId = newScriptVersion;
}

The above scripts does the following:

It gets all the scripts in which the latest script version creates the word “echo”
From that, it retrieves the active script versions
For each active version, it searches for all the echo’s in the script through a regular expression (line 10)
It then copies the existing version and replaces it with the echo-free text
It adds a description to this version and activates the latest version

Prestaties in scripting