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

[Chris Sanford1]

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 · Accepted solution]

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

[Chris Sanford1]

Thanks for the info. I had read similar examples like this on the community. The problem is I don't know how to set that up easily within the Power BI interface. I was just creating a simple web data source. I suppose it could be done in an R script data source from Power BI but it would be much more work to build and support that. I'm really just wondering for a table of our size, 250,000 records is that too much for it to be practical to query the whole table at once? An XML file for the entire table comes out to less than 2 GB. So how much is too much to be practical, is it 10,000 records as you provide in your example?

Also, I see there is a Power BI connector for ServiceNow, but it strictly queries the incident table and I need task SLA as well. How do most people get ServiceNow data into Power BI? I tried the ODBC driver but it is so incredibly slow I gave up on that idea.

[Chris Sanford1]

Thank you for your help. I ended up using the example here: https://community.powerbi.com/t5/Desktop/Iterating-over-start-and-end-dates-for-multiple-API-calls/t... and tweaking it so I could iterate month to month. Loaded the data in about ten minutes, much better than the ODBC driver.

[Daniel Draes]

I just created an article about how to do that in the ServiceNow space with Table API:

https://community.servicenow.com/community?id=community_article&sys_id=80ec3bb7db13c890414eeeb5ca961...

I know, a bit late to the game but thought I still share it here.