Best practice for iterating through a table with the Table API

[Tomav]

Hello everyone,

I’m working on an integration where I need to iterate through a large table (for example, incident) using the REST Table API. My goals are:

To get responses as quickly as possible (low latency per page).

To avoid over-consuming instance resources (not put unnecessary load on the DB or the node).

Right now, I’m paging through the records with sysparm_limit and either sysparm_offset or keyset style queries (e.g., ORDERBYsys_id + sys_id>{last} watermark).

Before I settle on an approach, I’d like to ask the community:

* What’s considered best practice for iterating through large ServiceNow tables via API?

* Are there recommendations on page size (sysparm_limit) that balance speed with instance impact?

* Is keyset pagination (using sys_id or sys_updated_on) preferred over using sysparm_offset?

* Are there any other tips to ensure I’m getting the data efficiently without overloading the instance?

Thanks in advance for any guidance!

[Community Alums · Accepted solution]

Hi @Tomav, 

For iterating through large ServiceNow tables via the REST Table API, the definitive best practice is to use keyset pagination instead of sysparm_offset. Keyset pagination involves sorting the results by an indexed, unique field (like sys_id or sys_updated_on) and using the last record's value from one page to create the query for the next page (e.g., sysparm_query=sys_id>last_sys_id_from_previous_page). This is far more efficient as it allows the database to start the next query exactly where the last one ended, whereas sysparm_offset forces the database to re-query and discard all preceding records, leading to progressively slower response times as the offset increases.

For page size (sysparm_limit), a value between 500 and 2,000 is typically a good balance, but it's best to test and monitor instance performance. To further optimize, always use sysparm_fields to request only the necessary columns, apply a specific sysparm_query to filter the dataset as much as possible, and use sysparm_exclude_reference_link=true to reduce the payload size.

Hope this helps!

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution and helpful so others can benefit as well.

[Tomav]

Hi Muhammad,

Thanks a ton for the clear guidance on keyset pagination and tuning the Table API—super helpful! 🙏

Two quick follow-ups:

* How does setting sysparm_display_value=true affect performance at scale (e.g., added server work for reference resolution, noticeable latency per page)?

* While iterating in real time, what’s the best way to monitor how much load we’re placing on the instance/database (any recommended logs, dashboards, or headers to watch)?

Thanks & regards,

[Tomav]

Hi Muhammad,

Thanks a ton for the clear guidance on keyset pagination and tuning the Table API—super helpful! 🙏

Two quick follow-ups:

* How does setting sysparm_display_value=true affect performance at scale (e.g., added server work for reference resolution, noticeable latency per page)?

* While iterating in real time, what’s the best way to monitor how much load we’re placing on the instance/database (any recommended logs, dashboards, or headers to watch)?

Thanks & regards,

[Community Alums]

Hi @Tomav, 

Sorry for the late reply!

Performance of sysparm_display_value=true: You should avoid using sysparm_display_value=true for large-scale data iteration. This parameter forces the server to perform extra database lookups for every reference field on every record in your result set to resolve the sys_id to its display value. This adds significant server-side processing overhead, which directly translates to higher latency per page and increased load on the instance. The best practice is to retrieve the raw sys_id values and have your client application perform separate, cached lookups for display values as needed.

and for monitoring the load, you can use:

• Transaction Logs: Just navigate to System Logs > Transactions and filter by the API user and the URL path (e.g., /api/now/table/incident). You can see the Response time and SQL time for each of your API calls to directly measure their impact.

• HTTP Response Headers: Check the response headers from your API calls. The X-Transaction-Time-MS header tells you the server-side processing time for that specific request, giving you immediate feedback.

• Instance Monitoring: For a real-time view of instance health during your import, you can use the Semaphores module to check for resource contention or the classic stats.do page to see a snapshot of the node's memory and CPU usage.

Hope this helps!

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution and helpful so others can benefit as well.