ApexSQL constantly improves its tools by revising and optimizing their code and introducing new features/options. Regarding ApexSQL Data Diff 2012 vs ApexSQL Data Diff 2011, there have been significant performance improvements while using both approaches. Source code was modified and no major changes in behavior/GUI/options have been made. Also, new options were introduced that changed the tool’s behavior. The newly added options are Show equal rows, Use checksum for large values comparison and selective database re-comparison logic; their impact on ApexSQL Data Diff performance will be described later
The improvements were made in three major areas:
- General
- Live database comparison/synchronization and
- Backup to live database comparison/synchronization performance
In the general area, performance improvements were made in:
- Data comparison
- Results display
- Selecting/deselecting comparison results
- Object filtering
- Display object filtering, and
- HDD space usage (MB)
Performed tests on live databases or synchronizing backups to live databases also show improvements in these three cases:
- Comparison process
- Synchronization process, and
- The complete process which includes both comparison and synchronization
ApexSQL Data Diff 2012 performance was analyzed in four environments:
- Comparison of databases with a large number of “small size” values (columns without long varchar / BLOB values were used)
- Comparison of databases with “medium size” values (each row was about 32KB in size)
- Comparison of databases with “large size” values (each row was about 1.5MB in size)
- Comparison of databases with “very large” values (rows with BLOB values that were more than 1.5MB in size)
All tests were performed in local, network and mixed environments
Before we take you to the performance improvement results, let us also explain how the new features used in ApexSQL Data Diff 2012 improved performance. The added options are Show equal rows, Use checksum for large values comparison, and the selective database re-comparison logic
Show equal rows option
This option defines if equal data rows should be stored on a local file system of the client machine and displayed in the result grid of the GUI. Assuming that the average user compares databases on a regular basis, it means that in most cases, compared databases have a small percentage of differences and most of the data is equal. In previous versions of ApexSQL Data Diff, all equal data was processed and stored on a local file system of a client PC, leading to excessive data storage; space usage is comparable to space that compared databases occupy which was quite critical for large databases
The new Show equal rows option allows the user to prevent ApexSQL Data Diff from storing equal data on the local file system resulting in more free space available. At the same time, an increase in comparison speed was achieved
Use checksum for large values comparison option
This option defines if long values should be compared locally using their values, or remotely using server-side functions applied to values. If enabled, this option significantly decreases time necessary to compare large values (more that 1MB in size), especially if used in a network environment
Selective database re-comparison
Selective database re-comparison logic is a new functionality in ApexSQL Data Diff 2012. This feature allows the tool to analyze which options were changed after the comparison (e.g. changes in the Object filter or Object mapping), and find which tables could be affected by these changes – only those affected are re-compared. In previous versions, all tables were re-compared, regardless of whether the changed option affected the comparison result or not
The results review
Here are the top improvements:
- The Show equal rows option itself improves performance up to 120%
- The data comparison process is up to 300% faster
- Results display up to 310%
- Object filtering up to 340%
- HDD space usage (MB) is improved by up to 450%
Check out our infographic for the exact performance improvement numbers
March 11, 2013
