Find Filthy Inefficient Single-Record Queries FAST

I made a thing.

The script at the bottom of this article scans all scripts in a given table/field for GlideRecord queries that use if (gr.next()) {} WITHOUT a preceding .setLimit(1) before .query().

Unfortunately, since there is no way (to my knowledge) to filter out-of-box records, you're probably going to find far more massively inefficient queries in ServiceNow's out-of-box code, than in your own. In my OOB PDI with only a few SN apps installed, this script finds about 1,450 violations in OOB code (ಠ_ಠ).

But still, this should help you to identify some of the major performance-impacting scripts running in your instance, at least by this particular metric.

Why is this important?

Every time you query a table for a single record, especially if you're not querying by a well-indexed field, you're doing a "full table scan". This means that the database server is going to scan the entire table looking for ANY records which match your query, and helpfully return all of those records to the application server... but if your script running on the app server only uses the first record returned and throws the rest away, then all the rest of that work done by the database server was pointless!

Even if there is only one record matching your query in the entire table, you're still wasting about HALF of your query performance on average.

Think about it - if you tell the database server that you actually only need ONE record, then it isn't going to bother continuing to search after it's found one, right?
You can use .setLimit(1) to tell the database to stop searching after the first record is found. Otherwise, this would be like continuing to search for your keys, after you've found them.

If there are 100 places where your keys might be found, assuming they're all equally likely, then every time you lose your keys you may find them in the first place you look, or the last place you look, but on average you're going to find your keys in about the middle - about the 50th place you look.
Which means that continuing to search after you've found your keys would sometimes cost you 100x as much time as if you stopped after finding them, or if may not cost you anything extra if you found them only in the last place you looked... but on avreage, it'll cost you about DOUBLE the amount of time you would've spent if you'd stopped looking after you found them.

That's what .setLimit(1) is for.

Usage

All you have to do is copy/paste this script into a Background Script in your instance, give it a couple minutes to run (it'll take a bit), and check out the output. This script can print out the details of where each violation can be found, including a link to the specific script file, line number, and even each variable name with an inefficient single-record query violation. To print these details, set the boolean argument on line 14 to true. To print only a summary of findings per table, set that boolean to false.