6
$\begingroup$

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

$\endgroup$
4
$\begingroup$

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

| improve this answer | |
$\endgroup$
  • $\begingroup$ This works well in Oracle, but not on SQL Server (at least some older versions). For some reason I see bad hash comparisons (false positives, unflagged mismatches). Pulling the data into an external environment should alleviate that. $\endgroup$ – Steve Kallestad Jun 13 '14 at 18:32
  • $\begingroup$ Even with false matches this will reduce the number of rows to be compared. $\endgroup$ – Michael Green Oct 18 '15 at 8:50
3
$\begingroup$

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.

| improve this answer | |
$\endgroup$
  • 1
    $\begingroup$ That's pretty close to my initial thought. (Which was Export, hadoop m/r the merge, import to temp, atomically replace table). Export/Import/Indexing will still be very slow, but it will scale beyond 100M rows in 24hrs on everything but DB2. DB2s indexing speed is a bear. I'm wondering if there might be a high speed algorithm for finding a changed column. Right now I'm thinking of hashing the row and hashing some row segments to at least narrow down changes without having to iterate each column for comparison. I'm also wondering about in-memory caching of the production dataset. $\endgroup$ – Steve Kallestad Jun 13 '14 at 12:46
  • $\begingroup$ future questions for when I get moving on it, I suppose :). $\endgroup$ – Steve Kallestad Jun 13 '14 at 12:47
  • 1
    $\begingroup$ Yeah. If you go down this route then pre-process step after exporting is probably where you wrangle it to a good state for some kind of comparison algorithm. Sounds like a really fun project tbh :) $\endgroup$ – LauriK Jun 13 '14 at 13:20

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.