The Data Compare is designed to run against very large tables. It is commonly run against tables with millions of rows.
AQT will read the data from the database in primary-key sequence, and will do a merge-match of the results. This is the most efficient way of doing a compare. AQT does not hold the data in-memory or sort the data itself.
The compare scales linearly. This means that a compare of 100,000 rows will generally take about 10 times longer than a compare of 10,000 rows.
When running against large amounts of data:
AQT runs the compare by running a statement such as the following against both tables:
select * from table order by primary-key-columns
Some databases, such as DB2, will process this SQL by fetching the table to a temporary tablespace and sorting it before returning the first row to AQT. Depending on the size of the table, this can take a long time and the compare will appear to be "stuck" and not doing much. Once DB2 starts to return the data, the compare will run fast.
Two-Pass Compare will compare the rows using a different method; it will read the rows one at a time from each of the tables. This is a more reliable method of comparing in some circumstances, such as when there is a difference of collating order between the two tables.
This method of comparing is very slow. As a result, we do not recommend using Two-Pass Compare for large tables, or for tables which will have a large number of differences.