Self Join

[Robert Ninness]

Seems like I'm on a bit of an optimisation binge at the moment. A lot of my recent ServiceNow coding has been to do with schema and relationships so I've been looking for the fastest and simplest ways to query these structures.

One of my recent problems was: given a table, find all the other tables referenced in sys_app_modules which share a parent sys_app_application.

Thinking through the problem the steps are:

1. Query the sys_app_module table using a given table as a filter and find all the associated applications
2. Query the sys_app_module table a second time with the applications from step 1 as the filter and find all the other tables from child modules

This is a very common "two pass" scenario that happens all the time when coding in ServiceNow. I came up with three solutions to this problem and each time I tried to optimise a different aspect of the code.

Solution 1

In this solution I did the traditional 2 pass approach.

function getSysAppApplications(table_name) {
    var modules = new GlideRecord("sys_app_module");
    modules.addQuery("link_type", "LIST");
    modules.addQuery("name", table_name);

    modules.query();

    var applications = [];
    while(modules.next()) {
        applications.push(modules.getValue("application"));
    }

    return new ArrayUtil().unique(applications);
}

function getSiblingModuleTables(table_name) {
    var parent_applications = getSysAppApplications(table_name);
    var modules = new GlideRecord("sys_app_module");
    modules.addQuery("application", "IN", parent_applications);
    modules.addQuery("link_type", "LIST");

    modules.query();

    var tables = [];
    while (modules.next()) {
        tables.push(modules.getValue("name"));
    }

    return new ArrayUtil().unique(tables);
}

gs.print(JSON.stringify(getSiblingModuleTables('incident')));

One of the biggest issues I have with 2 pass queries is the IN clause. If you are using an IN clause on a reference field, this should be a little nudge for you to see if you can use a join. It is possible to overflow the value side of the IN clause too so watch out when trying to filter with large results sets.

Solution 2

In the second iteration of this script I tried to remove one of the database queries and offload some of the processing onto the server. Which is a bit of an anti pattern with the way resources for our app servers compare to the database servers. The rule of thumb is, where possible, make the db do most of the work.

function getSiblingModuleTables(table_name) {
    var application_groups = new GlideAggregate("sys_app_module");
    application_groups.addQuery("link_type", "LIST");
    application_groups.addAggregate("GROUP_CONCAT_DISTINCT", "name");
    application_groups.groupBy("application.sys_id");

    application_groups.query();

    var sibling_tables = [];
    while (application_groups.next()) {
        var tables = application_groups
        .getAggregate("GROUP_CONCAT_DISTINCT", "name")
        .split(",");

        if(tables.indexOf(table_name) > -1)
            sibling_tables = sibling_tables.concat(application_groups
            .getAggregate("GROUP_CONCAT_DISTINCT", "name")
            .split(","));
    }
    var arrayUtil = new ArrayUtil();
    return arrayUtil.unique(sibling_tables);
}

gs.print(JSON.stringify(getSiblingModuleTables('incident')));

Here I reduce the number of queries, but the results set is much larger and I have to do all the filtering on the app node. Group each set of modules by application and check if the given table is in a list of tables from the module records. Afterwards do another pass and remove all the duplicate entires.

Solution 3

The last solution I came up with was to go back to the DB and try to get my results set in a single pass. I had a suspicion that using a join would be possible somewhere but I initially couldn't think how until I had iterated a bit on the first two solutions.

Joins are powerful tools when querying data in ServiceNow but joining a table back onto itself is not always an obvious thing to do.

function getSiblingModuleTables(table_name) {
    var modules = new GlideAggregate("sys_app_module");
    var selfJoin = modules.addJoinQuery("sys_app_module", "application", "application");
    selfJoin.addCondition("name", table_name);

    modules.addQuery("link_type", "LIST");
    modules.addAggregate("GROUP_CONCAT_DISTINCT", "name");
    modules.groupBy("link_type");

    modules.query();

    if(modules.next())
        return modules
        .getAggregate("GROUP_CONCAT_DISTINCT", "name")
        .split(",");

    return [];
}

gs.print(JSON.stringify(getSiblingModuleTables('incident')));

Less lines of code, no loops, less post processing, nice!