Ordering REST Api results by `updated_at` causes 500 Internal Server Error on large result sets.

Tourist
5 0 1

I am running queries against the REST API and have the need to order the results by `updated_at`. Our platform we are building pulls the latest order, product and customer information based on when it was last updated. 

We have no issues on smaller Shopify stores but our larger clients with 500k+ customers/orders/products, the API 99% of the time returns a `500 {"errors": "Internal Server Error"}`exactly after 20 seconds, I assume due to a timeout.  This happens even when adding a `limit=1`. I assume the `updated_at` column may not be indexed properly so when a sort is applied to such a large set, it takes too long and times out. 

Any help/insights will be greatly appreciated. We have tried to switch our implementation over to GraphQL however with the amount of data we're pulling out of the API and with GraphQLs limitations w/ query cost, its unfortunately too slow for our needs.

0 Likes
Highlighted
Shopify Expert
3991 13 316

I am confused. Shopify does not return 500 errors, that is your App. So if your App is blowing chunks when querying a large dataset, maybe your App needs a tuning? 

 

Anyway, if you can show off a request you make where Shopify returns a 500 error (as in you broke Shopify), that would be sweet... hehehe.

 

 

Custom Shopify Apps built just for you! hunkybill@gmail.com http://www.resistorsoftware.com
0 Likes
Tourist
5 0 1

Hi HunkyBill,

 

It's not our app, the myshopify endpoint returns a 500 response code. 

To prove that its not our app, I am simply curling our client's shop endpoint. This store has 900k+ customers.

 

curl -X GET -H "Content-Type: application/json" -H "X-Shopify-Access-Token: [token]" 'https://[store].myshopify.com/admin/api/2019-04/customers.json?limit=2&fields=id,updated_at'
Response 200:
{"customers":[{"id":xxxxxx,"updated_at":"2019-06-12T16:17:55-06:00"},{"id":xxxxxx,"updated_at":"2019-06-12T16:18:09-06:00"}]} curl -X GET -H "Content-Type: application/json" -H "X-Shopify-Access-Token: [token]" 'https://[store].myshopify.com/admin/api/2019-04/customers.json?limit=2&fields=id,updated_at&order=updated_at%20desc' Response 500:
{"errors":"Internal Server Error"}


I ran the same curls on a smaller store that has about 22k customers and its successful:

 

I am however noticing that the `order` does not actually sort my results by flipping desc/asc... That may be a different issue.

0 Likes
Shopify Expert
3991 13 316

Cool... carry on then... I am sure they will eventually sort you out!

Custom Shopify Apps built just for you! hunkybill@gmail.com http://www.resistorsoftware.com
1 Like