API 504 Error For Large POST Bodies

[mandmdiet]

We have a dilemma we're trying to solve with the API. We list unsent and sent claims in a grid in our application for our users. Once a claim has reached a received status, we stop listing it. If a claim gets deleted, though, we want to stop showing the claim in the list. For some of our clients, there are ~3k claims in this grid. Because Open Dental removes deleted claims from the claim table we don't have any way of querying the OD database for claims that have been deleted. Instead we're left with the option of pulling a list of all claims in our application and checking each one to see if they exist in OD. We have a few ways we can do this but the way we attempted first was by creating an in-memory set of claims in a POST query to the OD API using UNION ALL. Like this:

Code: Select all

SELECT c2.ClaimNum 
FROM Claim c1
RIGHT JOIN (
    SELECT 1234 AS ClaimNum
    UNION ALL
    SELECT 1235 
) AS c2 ON c1.ClaimNum = c2.ClaimNum
WHERE c1.ClaimNum IS NULL

This works very well even with a large number of claims directly against the OD database and returns in a matter of milliseconds. However, if we try to send this query to the OD API it only works fine for cases where we have ~2,000 or fewer ClaimNums to test. If we send more than ~2,000 ClaimNums in the query, we get a 504 from the API. We can just split these queries up into groups of 1,000 or less (for example) but we're wondering if there's a simple way to get this working as is. In the documentation it states that "the JSON body can have a maximum content length of 16.8M characters" (https://www.opendental.com/site/apiimplementation.html) and our message was nowhere near that limit.

We're of course open to doing this a different way. The temp table option ends up taking more characters to pull off and doing a SELECT against the OD table with an IN, e.g.

Code: Select all

SELECT ClaimNum FROM Claim WHERE ClaimNum IN (1234, 1235, ...)

is significantly more taxing on the database than the UNION route. This query takes ~30 seconds to complete and of course returns a ton more data. Besides the fact that once we have the result of this query we have to then do another query against our database to find the missing ClaimNums.

So, we're just hoping this is something we can do in a single query and the 504 is something that we can address or, again, if there's another option we're missing we'd be happy to look at that as well. It probably should be stated that we are needing to do this for several other tables as well so a solution that only works for Claim won't work either.

Thank you!

[SLeon]

The 504 code corresponds to a "Gateway Timeout" error. By your description, the query is taking too long to execute. As mentioned in other places on our forum, the API servers have a 60 second timeout. If the execution of the query does not complete before the API server closes the connection, the client (your application) will receive this 504 error. You are not hitting the 16.8M character maximum, or else you would see a 400 error code, with the message, "Request exceeds maximum content length."

Breaking the query into two or three batches is a great way to address this problem. Another strategy, that does not rely so heavily on a synched in-memory list, is to leverage Claims GET (single). When a user attempts to open/view a claim that you have in your in-memory list, you can call Claims GET (single). This will not just return up-to-the-moment accurate data, but if it has been deleted, you will receive a 400 BadRequest error with message, "Claim not found.". Then you could remove it from your list. You could also use Claims GET (multiple) in a similar manner, if your list is grouped by PatNum.

[mandmdiet]

Yeah, that's the strange thing about the 504 for us. If we run the query directly against the database it returns in milliseconds, so a 60 second timeout seems like an issue unrelated to our query. I will ask my developer to give me how long he waits before he receives the 504 and how long it takes to do ~2000 IDs in the request and get back to you.

I like the idea of querying when the user tries to open the claim but since this is a usability-related concern I will have to see if the client is OK with that user experience.

Thank you!

[mandmdiet]

Hi SLeon, here's some more information for you:

Query execution time when run through MySQL Workbench: 0.047 seconds
Query execution time when run through Open Dental API: > 60 seconds, timeout
Payload size sent to the Open Dental API: 178 KB
Query response size: 139 Bytes

Given the nature of the query, although the query text is large (relative to a typical SQL query, but still well below the 16.8 Megabytes allowed by the OD API (178 KB)), the response is actually tiny. Here is the entire result from the query:

Code: Select all

'518044,518046,518048,518062,518090,518097,718179,718348,718568,718773,719245,719252,719602,719780,719965,720704,720766,720833,721046,721637'

If we reduce the query size to ~2,000 claim IDs then we get a response from the Open Dental API, but it still takes around 20 seconds. It seems like something else is happening here that is fixable. We'd like to see if we can figure it out because even if we split the query to ensure that it will be successful, that's #1 increasing the total queries we make against the OD API (and we're trying to budget those carefully) and #2 we should be able to see something on the order of a 3,000% performance increase if we can figure it out.

We'd also be happy to provide you with the Customer API Key and query we're sending if you guys want to test it on your end. This is just a test OD instance.

[SLeon]

It makes sense that the result of the query is small, as you are seeing a 504 timeout error and not a 400 Bad Request (with message) error.

It also makes sense that limiting the scope of your query reduces the time it takes your query to execute.

Any query made through the API will take longer to execute than directly to the database for two main reasons:

• The API request must travel across the internet, to our API servers, undergo validation, travel via eConnector to the office, execute, and return back through the chain to the client (you). While this all happens quickly, it does add milliseconds to the overall time. The further away the dental office, the longer the travel time is likely to take.
• Once at the dental office, the query itself is screened. All queries made via the API must be read-only, replication safe, etc. But again, this happens very very quickly, and adds nearly zero time to the overall time.

Other factors that also impact execution time:

• Database size. Queries to larger tables can just take longer to execute. This is particularly true if the table isn't indexed by one of your parameters. I do not believe this is the case for the query you shared.
• Database stress. It is possible your query is executing at a busy time for the office, the MySql.exe might be significantly taxing the dental office's server, etc.
• API request throttling can theoretically impact this time, but I do not believe is applicable in this case. For example, if you are continuously hitting the 1 request/second threshold with this query, the dental office's database is likely over taxed and again their server's performance is the limiting factor you are experiencing.

I do not think any of the above are necessarily the root cause of your long query execution times, but they are worth mentioning.

Some questions:

• Are you using the Queries POST or the Queries PUT ShortQuery endpoint?
• Do all API requests for this customer take longer, or is it just the Queries endpoint?
• Do you experience timeouts (or very long execution times) for other queries, or is it just this specific one?

I do think that batching requests is probably the simplest route for you to pursue, but I understand you wanting to understand how all the pieces fit together.

[mandmdiet]

Thanks for the response SLeon,

First let me clear up a misunderstanding.

When I say the result of the query is small, I mean, we know what the result is because we can use MySQL Workbench to run the query. In MySQL workbench it takes .047 seconds and the result is 139 bytes so that's the same amount of data that would be downloaded when sent to the PUT ShortQuery API. The same query on the same database with the same data at the same time of day takes longer than 60 seconds to execute, times out and returns a gateway timeout result against the OD PUT ShortQuery API.

I have been taking into account additional expected latency. For that I mentally added 2 seconds to my estimates, but certainly I think we agree that in terms of latency, any query that the database can execute in .047 seconds should be able to make it all the way back to the original caller before timeout assuming the result is small enough.

Answers to your questions:

We're using the PUT ShortQuery endpoint.

Running this query:

Code: Select all

SELECT PrefNum FROM Preference LIMIT 1;

Has the following results:

Against the DB directly: 0.000 Seconds
Through the OD PUT ShortQuery API: 1537 milliseconds

So it looks like those extra latency concerns add up to about 1.5 seconds for our specific use case.

We don't experience timeouts in the API like this for other queries that perform very quickly against the database directly. The only time in the past we have experienced timeouts through the ShortQuery PUT API were in cases where our query also performed badly against the database directly and needed to be optimized.

The only differentiating factor here seems to be that our query request size is relatively large at 178 Kilobytes. If I had to guess, with the information that I have before me, I would say there is something undocumented (or maybe even unintentional) in the OD public API servers (or software) throttling traffic from consumers sending API requests. That throttle is causing the 178 KB to be uploaded very slowly and it is timing out.

[SLeon]

Thank you for answers to these questions.

Because you are receiving a 504 error and not a 400 Bad Request "Result exceeds maximum content length" error, I can assure you that the response isn't failing because it is too large.

As for the difference in size, there a couple reasons an API response will always be larger than the results of a query ran directly.

• Depending on how you are evaluating the size of the API response, make sure you are only looking at the data for the Body. Postman does a great job of showing the size of the Body, the Header, and then the total response.
• Depending on the number of rows resulting for your query, I would also make sure your workbench is not invisibly truncating your query. SqlYog, for example, will covertly append "LIMIT 0, 1000" to queries ran through its interface.

The format returned via SQL and via the API are vastly different.

• The JSON body contains double quote, comma, colon, bracket, and brace characters that are likely absent in you direct SQL result
• The title of each column is repeated for each "row" of a query result. For example, if I run "SELECT * FROM preference LIMIT 50", I will see the column name 'PrefName' 50 times, 'ValueString' 50 times, etc.

With a test database, I ran Queries PUT ShortQuery with "SqlCommand":"SELECT * FROM patient". The Body of the result was 1.48MB and had a successful 200 OK response code. It executed in 2.29 seconds.

Similar to my other questions:

• Can you please clarify if you query produces a 504 for all of your customers, or is it just the one?
• Do you have other queries that are also return results of this size (>=178kB)? Do they return 504 or 200?

[mandmdiet]

Thanks SLeon

Unfortunately it looks like there's still some miscommunication occurring.

The request size (not response size) is 178 KB. I am not receiving an error saying "Result exceeds maximum content length" I am getting a "504 Gateway Time-out". At the end of my last message I did not posit that I am receiving a failure due to response size. I suggested I am receiving a timeout due to my request size as that is the only differentiating factor between this request and successful requests. Let me try to restate the entirety of the issue because it looks like there are several points where we're not communicating well.

I have a query that when run against the database directly using MySQL Workbench returns a result 139 Bytes in size and completes in .047 seconds. The text of that query itself, e.g.: SELECT * FROM .... is 178 KB. I do not have any questions or concerns about different sizes in responses from hitting the database directly or about the different format of the responses from hitting the database directly versus from the API.

If I send that same query to the same database during the same time frame to the OD public API PUT ShortQuery endpoint, the endpoint does not return a result from the database. Instead it returns a "504 Gateway Time-out". My initial and still current point here is that since we know the database query only returns a result 139 bytes in length that this timeout is not going to be caused by the size of the response.

The below was also in my last post, but I'm putting it here again to round out my current thoughts on what's happening now that some things have been clarified. I've also tried to add some additional clarifying text.

The only differentiating factor here seems to be that our query request size is relatively large at 178 Kilobytes. If I had to guess, with the information that I have before me, I would say there is something undocumented (or maybe even unintentional) in the OD public API servers (or software) throttling traffic from consumers sending API requests and since our request size is so large it's becoming noticeable in this case. That throttle is causing the 178 KB to be uploaded (meaning us sending the request to the API via Postman, this is not referring to the response) very slowly and it is timing out.

To answer your two questions at the bottom of your last message:

Similar to my other questions:
Can you please clarify if you query produces a 504 for all of your customers, or is it just the one?
Do you have other queries that are also return results of this size (>=178kB)? Do they return 504 or 200?

This is a test instance of Open Dental and we do not have this code released to customers yet so it has only been run against this server.

Our query isn't returning a 178 KB result in this case which is hopefully a bit more clear now after this post, so I'll consider this question as not needing an answer anymore.

[mandmdiet]

I think this diagram represents the flow of data and all of the things that could be causing the speed issues. The items in green have been eliminated as possible problems. The items in yellow show places we can't rule out as causes of the problem. Is this missing something or do you find something inaccurate here?



Enumerating the possibilities:

1. Potential performance issues uploading the 75 KB payload to the Open Dental Public API
2. Potential performance issues when the Open Dental Public API runs its validation
3. Potential performance issues uploading the 75 KB payload FROM the Open Dental Public API to the Dental Practice Machine (eConnector)
4. Potential performance issues when the eConnector validates that the SQL is Read-only and replication safe

Is there a way, using logs or other tools on the Dental Practice Machine, to see when the eConnector receives the request? If so I'd be able to eliminate more possible causes of the issue.

Thanks again SLeon

[mandmdiet]

Just posting to see if we can reach some sort of conclusion on this one. I'm hoping my last two messages would serve to clear up some previous miscommunications. I'm really just trying to understand if the timeout bottleneck is in one of the yellow objects in my diagram above.

[SLeon]

I've started a reply to this previously, but my response really boils down to two things:

Early in the thread we discussed and agreed that large queries should be either 1) batched, or 2) replaced a lazy-loaded cache (leveraging Claims GET single/multiple). The rest of this discussion is either speculative or informational, which is more than fine, but won't change the recommendation of the two above workflows.

The yellow/green coding of your diagram is flawed. You color the query itself green because you run it on your MySQL workbench and it executes very quickly. This is not an apples-to-apples comparison to the API's Queries PUT ShortQuery endpoint. A better comparison would be for you to time the execution of the query in Open Dental's User Query window. It has the overhead I mentioned earlier, with screening the contents of the queries prior to them running. The greater the number of words/elements/terms in the query, the longer it will take to loop through/screen. As part of the original reply I was typing to this thread, I created many queries, larger and shorter than you the one you mentioned, and ran them via SQLYog, the User Query window, and the API. The former executed in fractions of a second, and the latter two took far far longer. However, both the UI and the API times were within a second or two of each other in every trial. If you are curious to this end, I would encourage you to time these three yourself as well.

Finally, my last point is philosophical. The Queries PUT ShortQuery endpoint is called "short query" for three reasons. Queries ran via this endpoint are meant to be short in request size, short in response size, and short in execution time. This thread has focused on the third point (execution time) which is fair, as it is timing out. However, I posit that your query fails the first point; it is too long. A query size of 178kB is not short, and it is not unreasonable for it to not execute within the 60 second timeout threshold.

For context, the first four paragraphs of this post are exactly 2000 characters. The query you are trying to run is 89-fold longer than this.

[mandmdiet]

Thank you for the reply, I'll put my comments inline below.

Early in the thread we discussed and agreed that large queries should be either 1) batched, or 2) replaced a lazy-loaded cache (leveraging Claims GET single/multiple). The rest of this discussion is either speculative or informational, which is more than fine, but won't change the recommendation of the two above workflows.

In the beginning, short of any other option available to us we were planning on batching, but since that is a workaround I was hoping there was a better option that addressed the cause of the delay if possible. In the very least I wanted to learn what was causing the issue so I could 1. rule out anything we can fix on our end and 2. better understand the API since we make heavy use of the API and will continue to in the future.

The yellow/green coding of your diagram is flawed. You color the query itself green because you run it on your MySQL workbench and it executes very quickly. This is not an apples-to-apples comparison to the API's Queries PUT ShortQuery endpoint. A better comparison would be for you to time the execution of the query in Open Dental's User Query window. It has the overhead I mentioned earlier, with screening the contents of the queries prior to them running. The greater the number of words/elements/terms in the query, the longer it will take to loop through/screen. As part of the original reply I was typing to this thread, I created many queries, larger and shorter than you the one you mentioned, and ran them via SQLYog, the User Query window, and the API. The former executed in fractions of a second, and the latter two took far far longer. However, both the UI and the API times were within a second or two of each other in every trial. If you are curious to this end, I would encourage you to time these three yourself as well.

The green there is just to indicate that the query runs fast through My SQL Workbench. It's not meant to represent overhead in the Open Dental query pre-validation process. In any case though, that's great information about things we can do to test these different areas to help determine where the slow-down is occurring. We will follow these recommendations to gather additional information here and report back.

Thanks again SLeon!