Applies to
ApexSQL Data Diff and ApexSQL Data Diff for MySQL
Summary
This article explains the benefits of using the Row filter in SQL database data comparison.
Description
If there is a need to compare a predefined range of data in two SQL or MySQL tables located in different databases and on different servers, but these tables have more than 100 million records, a full table comparison would be too time-consuming and could deteriorate the network and server performance.
In order to avoid any potential issues, boost the comparison performance, and shorten the time required to complete the process, the ApexSQL Data Diff’s and ApexSQL Data Diff for MySQL’s Row filter feature can be used to compare just the defined range of data.
To set up the Row filter condition, set up the data sources in the New project window, expand the Advanced options tab and click the Object filter tab:
In the Object filter grid, select the desired table and in the Columns column, click the ellipse (…) button to open the Columns filter window, to check out the columns used for the comparison process for a selected table:
In the SQL filter following example, the Customers table will be filtered by the CustomerID column. To set up the row filter condition, navigate to the Row filter column of the selected table, and then click the three dots button:
The Row filter for the Customer table window will be shown where a condition needs to be entered that will fulfill the comparison requirements. For example:
CustomerID >=1000 and CustomerID <=20000
In this SQL filter example, the condition filters the customers with IDs from 1000 up to 20000.
Below the field where the condition has been entered, the current Row count is shown. Currently, it is 19920:
When the Apply button in the bottom-left corner is clicked, the Row count number will show the number of rows according to the entered condition. In this example, it is 9001:
If needed, several columns can be used in a single condition applying AND and OR operators, like in the standard T-SQL.
This condition is what ApexSQL Data Diff will inject as the WHERE condition, while selecting from the database. So, everything that can be used in the WHERE clause of a T-SQL query, can be used here as well.
The outcome of this filtering is that the number of rows is reduced from 19820 to 9001, which will speed up the comparison and only rows specified by the condition will be compared and synchronized. If the Row filter feature can/needs to be applied to other tables as well, this will provide a significant boost in the comparison speed.
Q: Can I apply different conditions for the source and destination table?
A: Yes. Below the Row count is the Use the same filter for both objects option, which can be unchecked when there is a need to specify different conditions for source and destination table. You can read more about it from here.
Q: Can I use a different column as a filter condition, which is not a primary key?
A: Yes. You can use any column for the selected table as a filter condition, but appropriate values in the Filter row window need to be entered for that column.
