Compare Performance

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.

How to improve Performance for large tables

When running against large amounts of data:

select Only show rows than match. By default, this option is not selected; AQT will then write the full contents of both tables to the result grids. For large tables, this is slow and will use a lot of memory.
even when Only show rows that match is selected, AQT will write mis-matched rows to the grids. If you have a large number of rows which are different, this will slow down the compare. To avoid this:
- if you don't expect there to be many differences, set Stop after x Differences to a low value. This will terminate the compare if it hits a lot of differences, otherwise it can take a long time to finish.
- run the compare as a batch script. This does no screen processing, so is the fastest way of doing a compare. You can run the compare script from the Run SQL window (eg. can be done from interactive AQT).
limit the columns being compared to just the ones that need to be compared. The more columns being compared, the slower the compare. Large text columns are slower than small text columns.
one of the bottlenecks in the compare is network performance. If feasible, run the compare on the server that holds the database, or a machine with a small network path to it.
within AQT the bottleneck is CPU speed. Running the compare on a fast machine will make a lot of difference to the speed of the compare.

Slow time for the compare to start

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

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.

Advanced Query Tool

https://www.querytool.com