posted this before but never received a clear answer.
I'm working on an ETL project and am basically feeding JSON records of "tickets" into a MySQL database. (actually come to think of it, may be migrating to SQL server, but I'm not sure that will be a huge difference).
These JSON records are the "current data" for each ticket.
So it will say, for example:
Ticket_Id: 1 Ticket_Subject: Hi Ticket_Issue: Greeting Ticket_Status: Open
In the future I might get a record like:
Ticket_Id: 1 Ticket_Subject: Hi Ticket_Issue: Greeting Ticket_Status: Closed
These update records are stored in the order in which they are made ... so it's pretty easy to sweep through each day, parse the JSON, and perform an Insert/ Update into a SQL database/ table with Ticket records.
Here's the issue. There's currently 100k tickets in the database. In 6 months there may be 500k tickets.
The ETL process is already fairly slow, and I need to work on optimizing each step. For now, I want to focus on the SQL Insert/ Update.
Currently it's lookup against 100k rows ... soon it will be looking up against 500k ... eventually 2 million ... you get the idea. This is NOT scalable currently.
Luckily, I know that these tickets become "archived"/ uneditable after 30 days of inactivity. They become "closed" permanently and will never be changed or updated again.
Thus, there may be a logical solution that somehow transfers, locks, moves, partitions, ignores .... I simply don't know ... these "closed/ archived" tickets ... so I only have to perform daily lookups against 30k active tickets any given day. Not a million.
This seems like an extremely common issue (virtually ANY large insert/ update conundrum) .... but I have not found many simple ideas online.
I'm wondering what is a simple, maintainable solution to this problem. Even if the "simplest" is sadly two different tables or what have you. I have no problem doing a "union" select statements --- in actuality the 'select' query times are already super fast and not a problem. The problem is the insert/ update/ lookup/ write times.