How to best accomplish high speed comparison of like data?

Question

I attack this problem frequently with inefficiency because it's always pretty low on the priority list and my clients are resistant to change until things break. I would like some input on how to speed things up.

I have multiple datasets of information in a SQL database. The database is vendor-designed, so I have little control over the structure. It's a sql representation of a class-based structure. It looks a little bit like this:

Main-class table
 -sub-class table 1
 -sub-class table 2
  -sub-sub-class table
 ...
 -sub-class table n

Each table contains fields for each attribute of the class. A join exists which contains all of the fields for each of the sub-classes which contains all of the fields in the class table and all of the fields in each parent class' table, joined by a unique identifier.

There are hundreds of classes. which means thousands of views and tens of thousands of columns.

Beyond that, there are multiple datasets, indicated by a field value in the Main-class table. There is the production dataset, visible to all end users, and there are several other datasets comprised of the most current version of the same data from various integration sources.

Daily, we run jobs that compare the production dataset to the live datasets and based on a set of rules we merge the data, purge the live datasets, then start all over again. The rules are in place because we might trust one source of data more than another for a particular value of a particular class.

The jobs are essentially a series of SQL statements that go row-by-row through each dataset, and field by field within each row. The common changes are limited to a handful of fields in each row, but since anything can change we compare each value.

There are 10s of millions of rows of data and in some environments the merge jobs can take longer than 24 hours. We resolve that problem generally, by throwing more hardware at it, but this isn't a hadoop environment currently so there's a pretty finite limit to what can be done in that regard.

How would you go about scaling a solution to this problem such that there were no limitations? And how would you go about accomplishing the most efficient data-merge? (currently it is field by field comparisons... painfully slow).

Answer 1

Can't you create a hash for each classes, and then merge rows by rows, field by field only the classes where the hash changed ? It should be faster if most of the classes don't change..

Or a hash of each rows or maybe columns.. depending on how the data normally change..

Answer 2

Sounds interesting. Could the solution be to dump the data out, build a fast custom processing thingie to run it through and then import it back to the database? I've seen some blazing fast Java-based text processing tools for topic modeling that handle millions of lines of text per second.

If it's an option then you can build a shell script to first dump the data in as good as format as possible. Then some pre-processing to separate the datasets, then real processing of comparison and changes. Lastly something that writes it back to a good format for input into database.

Definately not a one-afternoon project, but you could probably get it to work in a couple of weeks.