Fact or Fiction? The order of your query matters.

[jarodm]

Over the past year I've been in a handful of platform engineering discussions where the question of "Does query order matter?" is asked. I've listened to claims and opinions by my peers, but no one had any evidence or documentation to support those claims.

>> Fact! Yes, the order of the query matters. <<

On a very large, un-optimized un-indexed set of records the proper order can save up to 90%. Without clearing the cache, subsequent queries were near identical, so the data below applies to only the first query after clearing the cache.

Here are the steps I went through...

Hypothesis

• Yes, the order of the query matters
• The method of scripted GlideRecord would not matter (AddEncodedQuery vs. AddQuery)

Setup

In Global Scope, on Kingston Personal Dev Instance

• Added field RandomStuff [u_random] to `ticket` table (string, 256 char, not indexed)
• Created 10M records each with random 256 characters placed in RandomStuff
• Manually changed 10 records to Closed (active=false)
• Copied a portion ("d8qwoxtwpa") from the random string on one of the active=false records.
• Ran the query both ways, changing order of the (Active = False) and (RandomStuff Contains "d8qwoxtwpa")
• Cleared cache between each test

Results

The version with (Active = False) placed first ran much faster in both tests. The differences between AddEncodedQuery and AddQuery was not noticeable.

Average over 10 tests

Time (in ms)	Test 1	Test 2
Active first	1.1	1.0
Contains first	11.9	12.2
Savings	90.8%	91.8%

Reference Scripts

Create 10M records

var numLoops = 10000000;
var passLen = 256;
for (var i=0; i<numLoops; i++) {
     var ticket = new GlideRecord('ticket');
     ticket.newRecord();
     ticket.setValue('short_description','Auto created number '+i);
     ticket.setValue('u_random',makePass(passLen));
     ticket.insert();
}
function makePass(length){
       var string_length = length;
       var randomstring = '';
       for (var i=0; i<string_length; i++) {
               randomstring += Math.random().toString(36).slice(-1);
       }
       return randomstring;
}

Test 1: Encoded Query Method (Fix script with 'Flush cache' checked)

var tableName = 'ticket';
var findThis = 'd8qwoxtwpa'; //From Ticket 0003
var query1 = 'u_randomLIKE'+findThis+'^active=false';
timedSearch(tableName,query1);
var query2 = 'active=false^u_randomLIKE'+findThis;
timedSearch(tableName,query2);
function timedSearch(tableName,tableQuery){
     var stopwatch = new GlideStopWatch();
     var ticket = new GlideRecord(tableName);
     ticket.addEncodedQuery(tableQuery);
     ticket.query();
     var recordsFound = ticket.getRowCount();
     gs.print('Timer:'+stopwatch);
}

Test 2: AddQuery Method (Fix script with 'Flush cache' checked)

var tableName = 'ticket';
var findThis = 'd8qwoxtwpa'; //From Ticket 0003
//var query1 = 'u_randomLIKE'+findThis+'^active=false';
var stopwatch = new GlideStopWatch();
var ticket = new GlideRecord(tableName);
ticket.addQuery('u_random', "CONTAINS", findThis);
ticket.addQuery('active',false);
ticket.query();
var recordsFound = ticket.getRowCount();
gs.print('Query1 Timer:'+stopwatch);
//var query2 = 'active=false^u_randomLIKE'+findThis;
var stopwatch2 = new GlideStopWatch();
var ticket2 = new GlideRecord(tableName);
ticket2.addQuery('active',false);
ticket2.addQuery('u_random', "CONTAINS", findThis);
ticket2.query();
var recordsFound2 = ticket2.getRowCount();
gs.print('Query2 Timer:'+stopwatch2);

[jarodm]

Post a comment (or helpful+like this one) if you'd like to see some deeper diving into this subject.

[Goran WitchDoc]

Great read

I guess this will effect how you build through the condition builder as well in a list view and reports?

[joey_mart]

Interesting observations, but I can't help be skeptical, mostly because of this statement: "Without clearing the cache, subsequent queries were near identical, so the data below applies to only the first query after clearing the cache." I think the difference you're actually seeing may be a combination of lazy cache initialization after a cache flush and possibly mysql buffer pool churn (assuming your team is in a smaller sized test instance?). As an extra test (on a sub prod or Dev instance) you could wrap the query calls in trace statements to see exactly what the generated sql looks like and how long it takes: gs.trace(true); gr.query(); gs.trace(false); Would be very interested to see that output!