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?

  • Can you cache the data and compare in-memory before committing to the database? – Brad Thomas Feb 21 '17 at 13:41
  • Do you have anything triggering on this data changing in the DB? An update to a row where the new data is the same as the old data might well be faster than checking if there is a change – Caleth Feb 21 '17 at 16:12
  • 1
    @BradThomas - Thanks for sharing your idea. I wandered around the cache concept for a while, but was bit hesitant on the performance with million records. But given that there are no way to know which records changed and where they are located in the incoming feed, caching is the only way. Perhaps "Ehcache" like something is needed. – andromeda Feb 22 '17 at 15:40
  • @Caleth - Thanks for sharing. Unfortunately the changes are not happening in same application. Somewhere else data is generated and just sent across. – andromeda Feb 22 '17 at 15:44
  • I mean you can bulk load the whole dataset into a staging table, then do an update table set value = bi.value ... from table join bi on table.key = bi.key, where 95% of the changes are no change. It only becomes more difficult if you want logging of "what changed" – Caleth Feb 22 '17 at 15:50

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.

| improve this answer | |
  • Delta import is the best, provided there is an option for it. Else, cache compare with checksum or Hash (based on important fields) on RAM is the best trade-off for faster solution. – andromeda Feb 22 '17 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.