Millions of position data is coming in a system which needs to be stored in a database. The data comes in pipe delimited formats in flat files,on a regular basis say twice a day. And most importantly out of a million records only 5% would have any change. Other records remains same as in earlier feed. Suggest an optimal strategy to put this data into database fast. Consider only 5% of incoming data would have any change in it compared to its previous version in database.I checked a similar question as discussed here. But its about Bulk insert operation, which can be done by first chunking and then bulk insert to DB. But the idea here is to somehow find out if some record has really changed, if yes, then only insert or update, otherwise just leave that record. This way it might save lot of time.Any pointers?
Optimal strategy for pushing data to DB for repeat updates, considering only a fraction of records change values
database;data;enterprise architecture;dao
To get a solution that is faster than the obvious one, you have to trade something in for the gained speed.It might be memory (caching the existing records and making the comparison in RAM rather than on disk), it might be programming complexity (computing a checksum and comparing that instead of comparing entire records) or a combination thereof. It might even be correctness (don't scoff - many, many real-life scenarios exist in which sacrificing a few % of correctness for twice the processing speed is a very good trade-off!).In real life, I'd expect the trade-off to be against the definition of the data interface. No organization in their right mind would keep exchanging their entire data  regularly. Any collaborator that regularly makes changes to a huge data asset already knows which records changed and which didn't, so they should be charged with the detection!In other words, a perodic full import is almost always a bad and unnecessary solution. Implement a delta import instead.