Subject Re: large data compare app - TIBOQuery or other component choice?
Author Svein Erling Tysvær
Hi Nick!

I'm doing something pretty similar at the moment, although I'm only
comparing one table in a database to one table in another database.
The databases should be more or less identical. I do it this way:

I use two TIB_Cursors, with indentical SELECT clause and ORDER BY.
Then I step through these row by row, and react if there is any
difference (I figured this would be quicker than to use a cursor on
one of the databases and then lookup the row in the other database).
If I decide to do an update on a row, I use a TIB_DSQL that I prepared
before opening the cursors.

I started such a comparison 15 minutes ago. So far, 1.2m rows have
been compared and 750.000 records updated (unknown to me, one of the
databases had lots of 00 in a field where the other database had 0000,
normally there would be far less updates). I would expect you to
obtain better throughput, since the Firebird server is not located on
my machine, and I have to go through a network.

I recommend using a separate TIB_Transaction for the TIB_DSQL.


--- In, nick irons wrote:
> Hi,
> I'm a newbie to IBO (have used IBX 5ish years) and would like help
> pointing me to the most efficient component for a large compare task
> to be run periodically.
> I'm trying to find differences between two databases, and have
> approximately 1.7 million records of interest spread between 12
> tables.
> I am using two TIBOQuerys, pointing the first (unidirectional) to
> the whole of the data of interest per table, then dynamically
> building a select in the second per row. For each row I then
> compare every field until I find a difference or have compared all
> fields. Upon finding a difference, some other code handles the data
> change. My compare code as follows:
> (qResyncSourceData.Fields[j].Value <>
> qResyncCompareData.FieldByName(
> qResyncSourceData.Fields[j].FullName).Value)
> If anyone can point me to a faster way to do this I'd be very
> grateful! currently yields about 200,000 rows per hour on a 1.6mhz
> laptop.
> Thanks in advance,
> Nick