SQL Server data comparison customized for performance – vertical filtering and Comparison options

As the previous post indicated, we will focus now on vertical filtering and comparison options.

ApexSQL Data Diff also provides the ability to choose only specific columns to be analyzed aka vertical filtering. By default, all columns are analyzed. You can choose which columns to analyze by selecting the object containing those columns clicking on the appropriate Columns field, and deselecting the columns you want to exclude.

Choose only specific columns to be analyzed, using ApexSQL Data Diff

Using row filtering, aka horizontal filtering, is another effective method for reducing the size of a temporary file and increasing the comparison speed. You can find a detailed explanation in the article Benefits of using the Row filter in ApexSQL Data Diff.

The comparison options

Sometimes, comparison sources could have just minor differences and most of the results upon comparison would show identical values. To avoid storing identical data values in temporary folder, be sure that the Show equal rows option is not checked. By default, this option in ApexSQL Data Diff is not checked.

If you are comparing tables with large data file types (new or old BLOB types), you can select the Use checksum for large values comparison option. ApexSQL Data Diff will compare the checksums first without any real data transferring to a temporary folder. If the checksums are equal, it won’t compare that data. The option is not checked by default.

Use checksum for large values comparison option

If the comparison and synchronization are still taking too long, you have an option to schedule these tasks using the ApexSQL Data Diff CLI and to perform these in low server activity hours. This way you can achieve better performance as ApexSQL Data Diff will be able to utilize maximum server resources, such as CPU, memory and hard drive utilization. Read more details on the benefits of scheduling: Automatically compare and synchronize SQL Server data.

Even though the performance of ApexSQL Data Diff 2014 is significantly increased compared to the previous version, you are able to greatly affect the performance even more, by utilizing custom SQL comparison and synchronization options.


July 11, 2013