Help
Join the Community
-
Documentation
Find detailed info about ServiceNow products, apps, features, and releases.
-
Impact
Drive a faster ROI and amplify your expertise with ServiceNow Impact.
-
Partner
Grow your business with promotions, news, and marketing tools for partners.
-
Store
Download certified apps and integrations that complement ServiceNow.
on - edited on by
Lisa Latour
| < Previous Article | Next Article > | |
| Best Practice for Building Efficient Queries | Outbound Integrations Using SOAP / REST: Performance Best Practices |
Introduction
This guide is written by the ServiceNow Technical Support Performance team (All Articles). We are a global group of experts that help our customers with performance issues. If you have questions about the content of this article we will try to answer them here. However, if you have urgent questions or specific issues, please see the list of resources on our profile page: ServiceNowPerformanceGTS
This document is targeted at advanced developer level audience. I will discuss various methods to solve two problems. The first problem is when there is a query against a single table with conditions against multiple fields joined by an OR. The second problem is when a query requires a JOIN between two tables. Both solutions involve running multiple simple queries instead of a single complex query and splicing the results via script - effectively emulating a SQL sub-query.
NOTE: Beware, some of these solutions require increased code complexity and potentially requires dealing with large datasets in memory! Most of the time you can fix slow a query through optimizing at the database level using indexes or simply avoiding complex queries in the first place. However, the strategies described in this article might be worth considering if you have exhausted all other options. TRY SIMPLER SOLUTIONS FIRST.
Also, perhaps more importantly, the scripted solutions listed here include code that builds lists of sys_id's that will be used in filters. This is very memory intensive and we have included a hard limit of 1,000 records in our code to avoid end-user impact. If you are considering a script that will build a filter by returning a list of sys_id's from code, please write protective code to ensure you do not build too big of a list. Even a list as small as 10,000 sys_id's can severely impact system-wide performance. Your mileage may vary!
PROBLEM#1 - Queries with Multiple OR Operations on One Table
Sometimes MariaDB can't do an efficient query when using OR operations. Consider the following query:
SELECT … FROM u_table WHERE (a = X OR b = X OR c = X) AND d = 5
MariaDB tries to use indexes to optimize the execution path of each query. An index is like a phone book; it is a listing of records ordered by one field and perhaps sub-ordered by a additional fields. With three OR conditions (like above), the optimizer has a hard time because it needs to be able to search each of three fields and see if any of them match at all. Going back to our phone book analogy, to do a search like this you would need to have three phone books and then merge the results together. This can be done with something called an Index Merge* (see below), but in some cases, the same results can be achieved much faster by doing 3 separate queries and then merging the results within ServiceNow's javascript code.
SELECT sys_id FROM u_table WHERE a = X AND d = 5
SELECT sys_id FROM u_table WHERE b = X AND d = 5
SELECT sys_id FROM u_table WHERE c = X AND d = 5
SELECT … FROM u_table WHERE sys_id IN (<results of three queries above>) <- MUCH BETTER!
*Index Merge
Some complex OR queries are better served by an Index Merge. For example, in the scenario above, suppose that 300,000 of the records in u_table have d = 5. The optimizer cannot sufficiently narrow down the result set by using an index on d. It cannot use a compound index on a, b, c because they are joined by OR statements. But if there were three indexes, one index where each of the three columns (a, b, and c) are listed as the first field in the index, then the MariaDB optimizer can use an Index Merge algorithm. This is equivalent to having three phone books, searching each of them individually for matching results, and then merging the results. Sometimes using an Index Merge is a more efficient way of dealing with inefficient queries that contain multiple OR conditions. The most common use case for this type of query is in the Auto-complete for reference fields functionality (also see here) or in complex user created reports (especially ones that use the dreaded über OR - where two parenthetical clauses are joined by an OR). To see if an index merge will work for your query you would need to ensure that you Create a table index for each field in your query that is joined by an OR condition.
MySQL :: MySQL 5.5 Reference Manual :: 8.2.1.4 Index Merge Optimization
SOLUTION #1 - Just Split It Into Two Queries
In some cases, you may be able to simply split the behavior into separate queries with separate results and still satisfy your end user use case. If you are creating a very complex query that is running slowly ask yourself if the customer would accept a solution that has multiple request/results instead of one merged result. If the performance is much faster with separate results, customer might be willing to bend on the requirements.
For example:
1 big report takes 35 seconds:
(Closed today) OR (Opened today) OR (Active=true AND Assigned To=Service Desk)
3 little reports take 4.5 seconds (even less if you can multi-thread them, as is done for reports on a home page, for example).
- Closed Today (1.3 seconds)
- Opened Today (1.2 seconds)
- Active=true AND Assigned To=Service Desk (2 seconds)
SOLUTION #2 - Merge Two Separate Query Results in Javascript
NOTE: Read note above regarding using complex coding solutions rather than optimizing at the database
Most queries in ServiceNow are done through the UI using the Condition Builder tool. In order to leverage a scripted sub-query method in the Condition Builder you need a piece of script that can be referenced anywhere. The standard way to do this is with a global script include.
1.Create a global Script Include called "eitherOr" with the following script:
var eitherOr = function(value, table, fields, active) {
var value = value || gs.getUserID();
var active = (typeof active == "undefined") ? true : Boolean(active);
var result = [];
var queryTable = function(table, field, qValue, resultAr) {
var grQuery = new GlideRecord(table);
grQuery.addQuery(field, qValue);
if (active) {
grQuery.addActiveQuery();
}
grQuery.setLimit(1001);
grQuery.query();
//gs.print(grQuery.getEncodedQuery());
var count = 0;
while(grQuery._next()) {
resultAr.push(grQuery.sys_id+"");
if (count++ > 1000){
gs.addErrorMessage("SubQueryFilter: " + table +
"." + field + " = " + qValue +
" returned over 1,000 results. Results of this list are trimmed.");
break;
}
}
return resultAr;
}
for (var ia = 0; ia < fields.length; ia++){
result = queryTable(table, fields[ia], value, result);
}
//gs.print("result: " + result.join(","));
return result.join(",");
}
2. In the Condition Builder where you want to leverage the eitherOr method, create a scripted condition like the one below
NOTE: It is bad practice to hard code a sys_id. A better option would be to programmatically return the sys_id of the current logged in user, like below.
javascript:eitherOr(javascript:gs.getUserID(), 'indident', ['assigned_to', 'caller_id']);
I'm just too lazy to take another screenshot.
Once you realize how much sub-queries can improve the speed of complex OR queries you will see how this principle can be applied in other places as well. Another common area is with JOIN queries in which the WHERE clause includes conditions from two different tables.
PROBLEM #2 - Queries with slow WHERE Conditions from Two Separate Tables
MariaDB has an internal optimizer that tries to find the most efficient way to query data. The main way that MariaDB improves query retrieval time is by using indexes. An index is like a phone book that orders all the results from some table in a sequence by a specified field or fields.
Suppose you have a query like this:
SELECT COUNT(*) FROM item LEFT JOIN model ON item.model = model.id
WHERE item.active = 1
AND item.customer = 'Acme'
AND model.end_of_life >= '2017-01-01'
AND model.end_of_life <= '2017-02-01';
Now suppose there is an index on model.end_of_life and another one on model.id. Suppose further that there are millions of items and there are hundreds of thousands of items that have the customer of Acme. This means the results of the JOIN before applying the end_of_life query (i.e. the intermediary result set) will be hundreds of thousands of records. We need to use the index for model.id to do the JOIN efficiently and we need to use an index on item(active, customer) to make the WHERE clause operate efficiently. But now we cannot also use the index on model.end_of_life and we are stuck doing a range scan on hundreds of thousands of model.end_of_life values. Not pretty.
SOLUTION #1 - Splice Results using GlideRecord.addJoinQuery(table, field, field)
See GlideRecord.addJoinQuery(table, field, field) for our official dev documentation on this little known GlideRecord method. Counter-intuitively named, this method does a SQL sub-query not a JOIN. Sub-queries can help MySQL choose the optimum execution plan by essentially breaking a complex query into two simpler queries. It does have some major limitation however.
How it works:
- All you can do is this: mainQuery.addJoinQuery('subtable', 'field_from_maintable', 'field_from_subtable');
-
- subtable is the name of the table that has a reference to a value against which we can splice with our main table
- field_from_maintable is a field on the main query with values that will match field_from_subtable
- field_from_subtable is a field on the sub-table that has values that will match field_from_maintable
- To add conditions in your sub-query use addCondition(field,value)
e.g. you CAN do this: mainQuery.addJoinQuery('subtable', 'field_from_maintable', 'field_from_subtable').addCondition('sys_created_on', '>', '2020-10-15 00:00:00'); - You can't dot-walk to the fields in the sub-query using conditions in your main query
e.g. you can NOT do this: mainQuery.addQuery('subtable_reference.sys_created_on', '>', '2020-10-15 00:00:00');
In regards to the example above, we can get what we want by doing a sub-query like this:
SELECT COUNT(*) FROM item
WHERE item.active = 1
AND item.customer = 'Acme'
AND item.model IN (
SELECT model.id FROM model
WHERE model.end_of_life >= '2017-01-01'
AND model.end_of_life <= '2017-02-01'
);
To do this in the code it would look like this:
var mainQuery = new GlideRecord('item');
mainQuery.addQuery('active', 1);
mainQuery.addQuery('customer', 'Acme');
var subQuery = mainQuery.addJoinQuery('model', 'model', 'id');
subQuery.addCondition("end_of_life", ">=", "2017-01-01");
subQuery.addCondition("end_of_life", "<", "2017-02-01");
mainQuery.query();
while(mainQuery.next()) {
...
}SOLUTION #2 - Splice Results in JavaScript
NOTE: Read note above regarding using complex coding solutions rather than optimizing at the database
If addJoinQuery() just can't give you what you need then you might try a kind of scripted sub-query.
Here's an example:
var subQIsBetween = function(table, field, after, before, orEquals) {
var subQ = new GlideRecord(table);
var results = "";//Not sure if delimited string is better than array here? Memory usage? What happens in GlideRecord when you pass a delimited string? Anyone with evidence care to comment?
orEquals = (typeof orEquals == "undefined") ? true : Boolean(orEquals);
var beforeOp = (orEquals) ? "<=" : "<";
var afterOp = (orEquals) ? ">=" : ">";
subQ.addQuery(field, beforeOp, before);
subQ.addQuery(field, afterOp, after);
subQ.setLimit(1001);
subQ.query();
var count = 0;
while(subQ._next()) {
if (count++ > 1000){
gs.addErrorMessage("subQIsBetween: " + table +
"." + field + " returned over 1,000 results. Results of this list are trimmed.");
break;
}
results += subQ.getValue("sys_id")+",";
}
return results.substring(0, results.length-1);
}
From the List View of the Items table:
Customer "is" Acme
Active "is" True
Model "is one of" javascript:subQIsBetween('model', 'end_of_life', '2017-01-01', '2017-02-01')
Obviously you would want to fancy this up with the GlideDateTime API to ensure valid dates were being used and to handle dynamic values like "today" or "next month".
Union Replacement*
Union replacement is a "maint-only" feature that only ServiceNow Support can help you implement. Union Replacement works by matching a problematic query pattern and breaking the complex WHERE clause results into two separate WHERE clauses that are then merged at the SQL level. If you've exhausted other ideas, contact support to see if Union Replacement might be viable. In order to use Union Replacement your query must be such that the conditions in the WHERE clause could be split in a way that isolates the tables you are trying to split. For example, consider the following complex query:
SELECT ... FROM A JOIN B WHERE (A.field1=1 AND B.field1=2) OR (A.field1=3 AND B.field1=4);
This could be represented with a Union Replacement as:
(SELECT ... FROM A JOIN B WHERE A.field1=1 AND B.field1=2) UNION (SELECT ... FROM A JOIN B WHERE A.field1=3 AND B.field1 = 4);
| < Previous Article | Next Article > | |
| Best Practice for Building Efficient Queries | Outbound Integrations Using SOAP / REST: Performance Best Practices |
