How to Fetch Large Volumes of Data in Bulk Using OpenDental API

[Ummehani]

I am currently working with the OpenDental API to retrieve patient records and I need to handle large datasets. Specifically I need to fetch potentially large volumes of data such as hundreds of thousands of records in a manageable way.

I have been using the offset parameter for pagination but I've encountered an issue: When I set the offset to 0 it returns all records at once rather than paging through the data in chunks. This is not ideal for handling large amounts of data and may cause performance issues.

Here are the details of what I've tried so far:

Using the offset parameter to retrieve data in batches, but the API returns the entire dataset when the offset is set to 0.
The OpenDental API documentation does not specify a limit parameter or a way to specify the number of records per request.
My questions are:

Is there a recommended approach or best practice for fetching large volumes of data in bulk using the OpenDental API?
Is there an undocumented way to paginate or limit the number of records returned per request?
Are there any specific API parameters or methods I should use to efficiently handle large datasets?

[SLeon]

Good morning,

First, I will link our documentation for pagination:

Pagination
For any method that returns a list of items, you will need to use pagination to get chunks of up to 100 items at a time. Use Limit and Offset. Limit is usually not specified, and it defaults to the hard limit of 100 items for any request. With no Limit or Offset specified, results will include items 0 through 99.

Example usage:
GET /appointments?Offset=400

This will return appointments 400 through 499. The risk of page drift, where another record is added to the database in the middle of a series of requests, is miniscule.

The general strategy you should use is to first perform an ordinary GET without any offset or limit. If your result is exactly 100 rows, then perform sequential GETs with offsets until your result is less than 100 rows. The Local API and API Service have an increased limit of 1000 items per request.

Setting the Offset parameter to 0 and not including it in the payload produces the same result; the first element in the returned dataset will be the zeroth item. When you say, "Entire dataset" I interpret that as "all 0-99 elements". If you want fewer elements returned (say 50), you would use the Limit parameter. You would then adjust the Offset parameter as you page through the dataset.

Example:

Code: Select all

https://api.opendental.com/api/v1/patients/Simple?Limit=50
https://api.opendental.com/api/v1/patients/Simple?Limit=50&Offset=50
https://api.opendental.com/api/v1/patients/Simple?Limit=50&Offset=100
https://api.opendental.com/api/v1/patients/Simple?Limit=50&Offset=150
...

As an aside, we recommend the Patients GET Simple endpoint over Patients GET (multiple) endpoint for most implementations. The latter functions identically to the Patient Select window within Open Dental, and the former uses a query that has been optimized specifically for the API.

Now to your questions:

Is there a recommended approach or best practice for fetching large volumes of data in bulk using the OpenDental API?

The answer to this will vary a bit depending on your implementation/application.

If you need a large amount of data on a large interval, say a weekly or monthly report that will grab a large portion of the patient table, you will probably want to use our Queries POST endpoint. This allows you to run a custom (read-only) query and receive the complete dataset (elements 0-n) in a file on your Sftp site. Your application could then digest this CSV file and not have to worry about paging at all.

For more timely use cases where you want to keep an up-to-date list of patient data, you can leverage the DateTStamp field. After obtaining the initial dataset, you can use the DateTStamp filter to only obtain elements that change since the last time you checked.

Example:

Code: Select all

https://api.opendental.com/api/v1/patients/Simple?DateTStamp=2024-08-29 08:00:00 //ran at 08:05:00
https://api.opendental.com/api/v1/patients/Simple?DateTStamp=2024-08-29 08:05:00 //ran at 08:10:00
https://api.opendental.com/api/v1/patients/Simple?DateTStamp=2024-08-29 08:10:00 //ran at 08:15:00
...

These subsequent requests will only return the handful of elements that changed within the interval, so the dataset will be smaller. You can also utilize Api Events to do this in a slightly more automated fashioned. They are further described in API Guide – Subscriptions and Events.

Is there an undocumented way to paginate or limit the number of records returned per request?

I think this is addressed at the beginning of my post, but please reply if you have further questions.

Are there any specific API parameters or methods I should use to efficiently handle large datasets?

In addition to the information above, my general advice is to utilize the DateTStamp parameter for the endpoints you want data from. I would also encourage you to examine your application's need for keeping large datasets in memory to begin with. The Open Dental software itself will pull some initial data, but doesn't keep a large amount of data cached and does not keep those caches critically up-to-date. In most cases, we query the database by PK or with a PatNum parameter at the time a form is launched. This allows us to not worry with large datasets in most cases. However, I understand that might not be as feasible for your application.

[Ummehani]

Hello SLeon,

Thank you for the detailed response and the helpful insights on pagination and handling large datasets with the Open Dental API.

I have another related query: I'm looking to fetch patient records between two specific dates using the Open Dental API. While I understand the pagination and DateTStamp parameters you mentioned, I'm interested in whether there is a way to directly query for records between two arbitrary dates (e.g., from Date1 to Date2) using the API.

Given the current API documentation and the details you've shared, it seems there might not be a built-in way to filter by a range of dates directly. However, if there are any additional parameters or methods that could support this, or if anyone has implemented a similar solution or workaround, I would appreciate your guidance.

Thank you once again for your assistance!

[SLeon]

Good morning,

...
it seems there might not be a built-in way to filter by a range of dates directly.
...

This is correct. You will need to write such a query and use the Queries POST or Queries PUT ShortQuery endpoint to obtain this data.