Is gr.getLimit() better for performance?

Marion de Groo1 · ‎01-26-2022

If I set the limit on query at 1 result with gr.getLimit(1), will it be faster than without limit?

Edit: is should be setLimit(1) that was a typo

Hitoshi Ozawa · ‎01-26-2022

As others have replied, it's setLimit().

In short, not always. Setting .setLimit() does not always imply performance improvement.

ServiceNow by default is using MySQL database. "setLimit()" probably translates to "LIMIT" in sql select statement.

In general, including LIMIT will improve performance but as can be found from reading the following threads, setting LIMIT may actually decrease performance in some circumstances. It's best practice to actually benchmark performance.

https://stackoverflow.com/questions/62814115/why-does-order-by-and-limit-1-slow-down-a-mysql-query-s...

https://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down

https://stackoverflow.com/questions/34488/does-limiting-a-query-to-one-record-improve-performance

Also, check the documentation.

For example, there is a warning on using setLimit() with deleteMultiple().

Also, do not use this method with the chooseWindow() or setLimit() methods when working with large tables.

https://developer.servicenow.com/dev.do#!/reference/api/paris/server/no-namespace/c_GlideRecordScope...

View solution in original post

Allen Andreas · ‎01-26-2022

Hi,

It's "setLimit()", and it may be very slightly faster, the main point here is that you're helping limit the amount of records retrieved, whereas not using it, even if using "if (gr.next()) {" it's still going through all the records and queuing them up...but the if limits it to 1.

So it's an extra precaution to also use setLimit and helps the system, even if in a small way.

Technically, in best practice methodologies, it does say to use setLimit(1) even when using if, but most don't as they feel it's a bit unnecessary.

Please mark reply as Helpful/Correct, if applicable. Thanks!

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

Allen Andreas · ‎02-02-2022

Hi,

I'm glad you found a correct answer that works for you.

I believe for the main lesson, the same thing was said several times.

So that's good to hear.

If any other reply was at least helpful to you, please also mark it as helpful.

Take care! 🙂

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

-O- · ‎01-26-2022

First of all you probably mean setLimit, not getLimit. Assuming gr is an object of type GlideRecord, it has no getLimit member.

That really, really, really depends on what you are querying. E.g. querying a table with 100000 records for a single record by sys_id, there will be no difference at all. Querying the same table without filter, but limiting it to 1 will make a huge difference. But if the goal is to query the same table for 100 records all at once or the same 100 records 1 by 1 using setLimit, there'll be a big impact again, but in the direction of performance loss when using setLimit.

A situation where it does pay to use setLimit is if one wants to know if at least one record exists. In that case setLimit(1) does help. And, of course, if you need to paginate for reasons not under your control and thus have no other option.

Marion de Groo1 · ‎01-27-2022

"E.g. querying a table with 100000 records for a single record by sys_id, there will be no difference at all."

So for instance when I query cmdb_ci by sys_id, setting the limit to 1 will not stop the scanning of the rest of the table when the sys_id is found?