User reports receiving numerous 502 bad gateway messages on API end point

[dorothyyeager]

From an email to the Information Division:

I am getting a bunch of 502 Bad Gateway: Registered endpoint failed to handle the request.failures when issuing this request https://api.open.fec.gov/v1/schedules/schedule_a/?per_page=100&last_index=4041720181525999608&two_year_transaction_period=2018&sort=contribution_receipt_date&min_amount=99999&is_individual=true&last_contribution_receipt_date=2018-03-29T00:00:00&api_key=LL3hCoVQFFtpdF4aHLJfMe2NsJAtVEfpGOTrcIll Request resolves successfully when resubmitted at a later date. This has been cropping up for a while but is new as of the last year. It took me a while to isolate. I also rarely get a 504 Gateway Time-out

We'll let him know it's been reported to the website team for investigation.

[pkfec]

This API call took more than 2 minutes to load (2.40 sec exactly). The user is trying to load the results based on the columnlast_index which is used for pagination. When last_index column is used usually it takes a while to load the results.

I am going to research more on the last_index column and see how it impacts the API results.

[pkfec]

In SA table there are 230+million records. Since Schedule A consists of large number of transactions paginating using last_index makes the query a little complex.

I tried to paginate thru next page of results by providing the last_index=4082420181586122237 and the last_contribution_receipt_date=2018-04-19T00:00:00. The API returned results using next page in under a minute.

Here is the API call:
https://api.open.fec.gov/v1/schedules/schedule_a/?per_page=100&last_index=4082420181586122237&two_year_transaction_period=2018&sort=contribution_receipt_date&min_amount=99999&is_individual=true&last_contribution_receipt_date=2018-04-19T00:00:00&api_key=LL3hCoVQFFtpdF4aHLJfMe2NsJAtVEfpGOTrcIll

Emailed the User above details and waiting for the feedback.

[lbeaufort]

At PI planning, we decided to take a comprehensive approach to Schedule A query optimization: #3939