4

Every day, we receive huge files from various vendors in different formats (CSV, XML, custom) which we need to upload into a database for further processing.

The problem is that these vendors will send the full dump of their data and not just the updates. We have some applications where we need only send the updates (that is, the changed records only). What we do currently is to load the data into a staging table and then compare it against previous data. This is painfully slow as the data set is huge and we are occasionally missing SLAs.

Is there a quicker way to resolve this issue? Any suggestions or help greatly appreciated. Our programmers are running out of ideas..

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
SRaj
  • 1,168
  • 1
  • 14
  • 36
  • What database are you using? And is the slow part uploading the data to the temp table or doing the compare to move to a regular table?And how large is "huge"? How many records are merging? – Shawn Feb 15 '19 at 21:39
  • Database is MariaDB. Usually contains nearly 7 million records and it increases daily as more data comes in – SRaj Feb 15 '19 at 22:01
  • Slowest part is to compare the data with previous to identify the delta – SRaj Feb 15 '19 at 22:08
  • 7M is a lot of records, but not _**A LOT**_ of records. Honestly, if your vendors are sending you their entire datasets, why not just nuke your data and load the data the vendor sent to you? Do these come at the same time or all different times? Are you also dealing with Deleted Records that need to be removed from your data? How many columns are you comparing and how large is the database? Can you provide an example of the query statement you're currently using for this update? – Shawn Feb 16 '19 at 01:59
  • We can't nuke the data because it's not just loading into another database. We have to find the difference and then call another services which create and update data. If you call the service again with old data, it will create new entry. I will try to get the query after masking few parameters as their are strict confidentiality clauses. – SRaj Feb 16 '19 at 18:19

2 Answers2

7

There are a number of patterns for detecting deltas, i.e. changed records, new records, and deleted records, in full dump data sets.

One of the more efficient ways I've seen is to create hash values of the rows of data you already have, create hashes of the import once it's in the database, then compare the existing hashes to the incoming hashes.

Primary key match + hash match = Unchanged row

Primary key match + hash mismatch = Updated row

Primary key in incoming data but missing from existing data set = New row

Primary key not in incoming data but in existing data set = Deleted row

How to hash varies by database product, but all of the major providers have some sort of hashing available in them.

The advantage comes from only having to compare a small number of fields (the primary key column(s) and the hash) rather than doing a field by field analysis. Even pretty long hashes can be analyzed pretty fast.

It'll require a little rework of your import processing, but the time spent will pay off over and over again in increased processing speed.

Community
  • 1
  • 1
Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
5

The standard solution to this is hash functions. What you do is have the ability to take each row, and calculate an identifier + a hash of its contents. Now you compare hashes, and if the hashes are the same then you assume that the row is the same. This is imperfect - it is theoretically possible that different values will give the same hash value. But in practice you have more to worry about from cosmic rays causing random bit flips in your computer than you do about hash functions failing to work as promised.

Both rsync and git are examples of widely used software that use hashes in this way.

In general calculating a hash before you put it in the database is faster than performing a series of comparisons inside of the database. Furthermore it allows processing to be spread out across multiple machines, rather than bottlenecked in the database. And comparing hashes is less work than comparing many fields, whether you do it in the database or out.

There are many hash functions that you can use. Depending on your application, you might want to use a cryptographic hash though you probably don't have to. More bits is better than fewer, but a 64 bit hash should be fine for the application that you describe. After processing a trillion deltas you would still have less than 1 chance in 10 million of having made an accidental mistake.

btilly
  • 43,296
  • 3
  • 59
  • 88
  • I've been ignoring the collision risk for years, so +1 from me for pointing it out. – Eric Brandt Feb 15 '19 at 22:00
  • Thank you very much btilly. I will explore this and update. – SRaj Feb 15 '19 at 22:05
  • 2
    `But in practice you have more to worry about from cosmic rays causing random bit flips in your computer than you do about hash functions failing to work as promised.` <<< Poetry. Pure poetry. :-) – Shawn Feb 16 '19 at 02:03
  • @Shawn I wish it was original, but it is based on a comment that I saw years ago about trusting the [Rabin Miller primality test](https://en.wikipedia.org/wiki/Miller%E2%80%93Rabin_primality_test) after enough rounds. – btilly Feb 17 '19 at 22:25