From: Pit Capitain <pit@...>
Date: 2007-02-09T01:26:47+09:00
Subject: Re: General Approach to Data Validation

Drew Olson schrieb:
> This does make sense, but I'm using ruby in this situation for a reason. 
> I had previously written some validation scripts that ran on .csv dumps 
> from the database and I leveraged this previous work to get these 
> scripts up and running quickly by introducing ActiveRecord. Also, I'm 
> writing an error report to .csv using FasterCSV and doing quite a bit of 
> data manipulation during these compares. In short, I'd really like to 
> continue using ruby/ActiveRecord here. However, I want to make sure that 
> the way I'm going about it is as "efficiently as possible". It's not a 
> __huge__ deal, however if I'm make some massive error that would save 
> 50% when running my scripts, it would be nice to change them.
> 
> As far as record size, we're talking close to 1 million records, more in 
> some cases.

Drew, I don't think doing it on the client is the right tool for this 
job. But if you really want to, I'd try one of the following approaches. 
Note that I've never used ActiveRecord before, so I don't know whether 
you actually can do this.

One way would be to read all the records at once and then use Ruby to 
compare the two datasets. This requires lots of RAM, and it doesn't 
scale well if you'll get more records. You could try to split the 
datasets into smaller disjoint parts and then compare only those parts 
in order to reduce the memory needed.

The other way would be to read the records one after the other (using 
cursors in db terminology) in an appropriate sort order. If ActiveRecord 
allows you to do this in parallel with both tables, you could compare 
the tables like this:

   table1 = open_cursor_for "table1"
   table2 = open_cursor_for "table2"

   record1 = table1.next_record
   record2 = table2.next_record

   until record1.nil? and record2.nil?
     case relevant_fields(record1) <=> relevant_fields(record2)
     when -1
       puts "#{record1} is missing in table2"
       record1 = table1.next_record
     when +1
       puts "#{record2} is missing in table1"
       record2 = table2.next_record
     else
       puts "#{record1} exists in both tables"
       record1 = table1.next_record
       record2 = table2.next_record
     end
   end

   table1.close
   table2.close

(I see that Brian suggested the same algorithm.)

The code you have shown in your first post performs roughly 2 million 
database queries. Try this with 100, 1000, 10000 queries, and then 
estimate how long it would take for the real job. If this is no problem 
for you, your code should be fine.

Regards,
Pit