Improve performance for HTTP Request with huge body

Question

I'm struggling to understand how to improve the performance for an HTTP Request that comes with a huge body.

Now, let me explain better what I mean with performance!
Imagine I have a DB with the table "User" that contains 100k elements: if the client wants to visualize all of them, we can:

1. Send 100k elements all together;
2. Paginating the requests, so for instance sends 1k elements and 1k more when the user will scroll.

Easy to say, we would rather use the second option: sending 100k elements will have a huge impact. So far so good!

Now, how can thing works the other way around?
Imagine the web-client is able to edit the records he just downloaded and he wants to save the changes on the DB: he will call something like https://mysite/api/update and pass a body with the update elements.
Let's also suppose the client has scrolled all the way down and he downloaded all 100k elements; the first and easiest improvements is to send back to the server ONLY the elements that the web-client actually edited.

Now, imagine the web-client has edited all 100k elements (not very common, but still.. This is more a "I want to know" question).
What are the possibilities here? I can send one HTTP request to https://mysite/api/update with the body containing 100k, but that seems really.. "bad". I mean, it will take forever for the webclient to send it, and the request itself may fail due to timeout.

First thing I thought was: well, just split the single request in multiple ones! So send 100 requests with 1k elements.
This may work, but.. all the requests should modify the database "once", meaning, if the last element update throws an error, NO element should be updated. So, I should perform all the requests and update in the DB as a transaction, but.. We have 100 different request.

The problem with the first "half solution" is that.. If the server is stateful, I might manage to perform the 100 different request in one go, but what if the server is stateless? Each request is completely standalone, so.. How this could work?

The only thing I think it may work is signalR: I open the connection and send elements split in different signalR message, but.. is this a god solution?

Finally, I also thought: well, maybe we can improve this by compressing the HTTP request body! I could use https://www.npmjs.com/package/pako to compress from my JavaScript web-client, but.. I've read that web-client shouldn't really compress the HTTP request body (possible attacks like GZIP bomb).

Answer 1

For the vast majority of developers, you'll never encounter a situation where the user needs to update 100K records at once from a web client, as noted in Doc Brown's answer.

If you're one of the astronomically unlucky (or just curious about the theory), one way to implement this involves a staging table and some endpoints to handle the transaction. Example:

### DB Schema
Table: Person
- ID : GUID (PK)
- Name : String
- Phone Number : String

Table : Person_Staging
- Person_ID : GUID (PK)
- Batch_ID : GUID (PK)
- Name : String
- Phone_Number : String

Table : Batch
- ID : GUID (PK)
- Expires_After : Timestamp
- Completion_Time : Timestamp

### HTTP Endpoints
POST /api/batch => returns GUID with a new transaction ID
POST /api/batch/{GUID} => adds request data to the staging table
POST /api/batch/{GUID}/commit => moves data from staging table to

A few notes about this implementation.

• When you actually make the /commit call, you can wrap the staging-to-live changes in a transaction to ensure data integrity.
• The Expires_After field is useful to prevent unfinished transactions from clogging the database. A scheduled task can delete uncommitted staging changes after this expires.
• Although it's possible to skip the first step if you want and allow users to define arbitrary transaction IDs, this makes it possible for users to reuse the same ID after a batch is complete, or worse—for two users to commit to the same ID accidentally.

Answer 2

I think it is pretty normal that unrealistic use cases lead to unrealistic issues.

Lets says you have 100k edited records, and different edits were done in some more or less complex or arbitrary manner. Then I think it is unrealistic to expect one wants the changes to be applied in one transaction. If only one of the changes fails, and that rolls all other changes back, way too much information would get lost. Such batches of modifications are usually applied for each record where possible, and where the modification fails, it is logged for which records the change failed, and maybe why. The log information is then kept, send back to the client for further processing, or used elsewhere.

A different case is when those 100K records are edited in a systematic manner, and it is really important that the change must be applied "all-or-nothing". Most of such changes I can imagine won't be initiated by a user-facing web client, they will usually be internal system changes. For example, a change of the internal date/time representation of the "last-logged-in" date of a user could be such a scenario. Such kind of change can and should be executed directly on the server, initiated by an administrator, and, for example, implemented by an UPDATE sql which affects all records at once.

If you really have a regular operation for a web-client which modifies several hundred thousand records, it might be possible to extend the web UI with certain specific "batch" operations. Those operations can be implemented as special server operations which don't suffer from the necessity to transfer all records first to the client and then all back to the server.

Finally, most operations for which it seems to be important that they are done in one transaction can be implemented in a way which mitigates this necessity. Lets take the "last-logged-in-date" example. By adding an extra attribute to the data model which saves the data format in which the date is stored, updating the data format can be applied partially, or in multiple runs in an idempotent manner. Processes which require the "new" data format can check if it is available and react accordingly when not.