1

what would be the best option for exposing 220k records to third party applications?

  • SF style 'bulk API' - independent of the standard API to maintain availability
  • server-side pagination
  • call back to a ftp generated file?
  • webhooks?

This bulk will have to happen once a day or so. ANY OTHER SUGGESTIONS WELCOME!

Community
  • 1
  • 1
Daniel M
  • 13
  • 2
  • may be closed as too broad and off topic :/ declare a more specifc question! – cramopy Oct 16 '15 at 19:03
  • really? the question is very specific. I need to know what would be best, a dedicated BULK API that may get tied up for 10 minutes at a time or a file export or a server side pagination! why don't you tell me how it needs to be more specific... – Daniel M Oct 16 '15 at 19:26
  • 1
    "ANY OTHER SUGGESTIONS WELCOME!" meaning you are open to suggestions. This isn't a suggestion site, this is a Q/A site. Which makes it too broad. Second, it is hard without knowing almost everything to answer with "best option". – Emz Oct 16 '15 at 20:10
  • @Emz you can just say "I don't know" next time... – Daniel M Oct 16 '15 at 20:18

2 Answers2

2

How are the 220k records being used?

Must serve it all at once

Not ideal for human consumers of this endpoint without special GUI considerations and communication.

A. I think that using a 'bulk API' would be marginally better than reading a file of the same data. (Not 100% sure on this.) Opening and interpreting a file might take a little bit more time than directly accessing data provided in an endpoint's response body.

Can send it in pieces

B. If only a small amount of data is needed at once, then server-side pagination should be used and allows the consumer to request new batches of data as desired. This reduces unnecessary server load by not sending data without it being specifically requested.

C. If all of it needs to be received during a user-session, then find a way to send the consumer partial information along the way. Often users can be temporarily satisfied with partial data while the rest loads, so update the client periodically with information as it arrives. Consider AJAX Long-Polling, HTML5 Server Sent Events (SSE), HTML5 Websockets as described here: What are Long-Polling, Websockets, Server-Sent Events (SSE) and Comet?. Tech stack details and third party requirements will likely limit your options. Make sure to communicate to users that the application is still working on the request until it is finished.

Can send less data

D. If the third party applications only need to show updated records, could a different endpoint be created for exposing this more manageable (hopefully) subset of records?

E. If the end-result is displaying this data in a user-centric application, then maybe a manageable amount of summary data could be sent instead? Are there user-centric applications that show 220k records at once, instead of fetching individual ones (or small batches)?

Community
  • 1
  • 1
always more to learn
  • 41
  • 6
  • Thanks for taking the time to answer this bud! much appreciated! Unfortunately, this has to be available for third parties to refresh / validate accounts and thus is a requirement. I agree with your logic though and appreciate the input. – Daniel M Oct 16 '15 at 20:06
  • @DanielMeacham, I'm not sure what technical requirement you are hinting at above with "refresh/validate accounts". Can you please clarify? – always more to learn Oct 19 '15 at 13:45
1

I would use a streaming API. This is an API that does a "select * from table" and then streams the results to the consumer. You do this using a for loop to fetch and output the records. This way you never use much memory and as long as you frequently flush the output the webserver will not close the connection and you will support any size of result set.

I know this works as I (shameless plug) wrote the mysql-crud-api that actually does this.

mevdschee
  • 1,625
  • 19
  • 16