Is it Practical to Query around 250,000 Records in a Single REST Transaction?

Chris Sanford1 · ‎02-12-2019

I have someone trying to import ServiceNow data into Power BI. I see that I can pull the data in with a simple web data source and use the REST API. This would work great for small chunks of data, but what if they wanted to pull a lot of data? We have approximately 250,000 task SLA records which I would imagine comes out to a few gigabytes of data. Not really big data by enterprise standards, but still bigger than a typical REST transaction. I know there's plenty of third party tools out there for processing big data, breaking it down into multiple transactions, etc. But if I wanted to query an entire table of that size using REST, say once daily, I am wondering: Would it be possible? If so, what properties would need to be modified to prevent timeouts, approximately how long would it take to get that quantity of data and would it have a noticeable performance impact on a ServiceNow instance?

sachin_namjoshi · ‎02-12-2019

I will suggest to retrieve large amounts of records is to use pagination. That is, get sub-sets of records in different calls, for example, first you get records from 1 to 10000 in a call, then you get from 10001 to 20000, then from 20001 to 30000, etc.

To do so, you can make use of the parameters

- sysparm_limit: to define how many records you get in one call (10000 by default)

- sysparm_offset: to define the records to exclude from the query

In this case, the first call would be something like

/api/now/table/incident?sysparm_limit=10000&sysparm_offset=0

the next one

/api/now/table/incident?sysparm_limit=10000&sysparm_offset=10000

the next one

/api/now/table/incident?sysparm_limit=10000&sysparm_offset=20000

...

You can put that in a loop to get all the records using pagination.

For more info you can take a look to Table API - GET /now/table/{tableName}

Regards,

Sachin

View solution in original post

vb28 · ‎02-04-2023

Does it work if records are deleted in between? Records will move to previous pages thereby missing the records.

Daniel Draes · ‎02-06-2023

True, that is one of the reasons why i used a little different approach in my blog post some time ago:
https://community.servicenow.com/community?id=community_article&sys_id=80ec3bb7db13c890414eeeb5ca961...

dvp · ‎02-12-2019

I would only query entire table for initial set up and moving forward I will only query the records that are updated yesterday.

Chris Sanford1 · ‎02-12-2019

Do you know if that can be done easily in Power BI? If I just create a data source for the whole table initially and update the sysparm_query on the URL, will it automatically just add on to the existing data source?

Also, should I go this route what changes need to be done in ServiceNow to allow for that initial set up? I am getting timeouts if I try to query even more than 1 month of data.