I have a REST API, and everything works just fine for retrieval and creation of data. However, an important aspect of this API is the ability to send a batch of data to create.
Short version: Wondering what’s the most appropriate way (performance wise) to handle a dynamic delete - insert query.
Long version: A user may send a big JSON with 100 rows of data to insert. I do not want to rely on active record for this, as this API is meant to be used for integration with third parties and the overhead of using AR will slowly but surely kill my server, when enough users make the call. What are my alternatives? I plan on limiting each call to 100 items, so that’s one big relief already.
Of course, there’s the AR solution, iterating through each object and delete / insert. The problem is that the way the API works, it needs to delete previous data (if it exists), and then insert the new one, causing 2 queries to happen. Like I mentioned above, I’m trying to stay away from AR in this case.
Example of the above:
foreach($dataInJSON as $d){
//Delete and insert for each active record needed to use.
}
The second solution is to build my query dynamically. From what I can gather, something like this should work:
DELETE FROM tab WHERE (col1,col2) IN ((1,2),(3,4),(5,6));
INSERT INTO table (col1,col2) VALUES (1,2),(3,4),(5,6)
In the above, the values in the IN clause are the parameters sent in the JSON. This means that this could potentially generate a huge query. There’s no other good solution that I can think of right now, other than this one. It does generate the problem of validation, as I need to be sure that the data that the user inputs is valid and/or is using owned data, instead of another user’s data. And that’s the most important reason why I’m not sure how to proceed with this one.
I have an overall good idea of how to make it work, but killing my server in the process is what’s worrying me, as I’m not sure which approach demands less performance. Is there something that the framework does for me in this case? Something that manages multiple deletions and insertions with ease, instead of opening and closing the database connection multiple times.