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).