Comparison and synchronization of SQL Server database data from different data sources can be very time and resource demanding. It is highly dependant on hard drive writing speed, network bandwidth, memory size and speed, CPU speed, and the size of the data. Therefore, ApexSQL Data Diff, a SQL tool for data comparison, incorporates some options that could help you get better performance and increase comparison speed.
During the SQL Server database comparison process, ApexSQL Data Diff retrieves the data from both data sources and stores it to a local temporary folder. The application will behave the same regardless of whether you are using a data source that is on a local or on a remote machine. ApexSQL Data Diff does not impose any limitations regarding the data source size but in some scenarios, you need to have free disc space approximately equal to both database sizes and twice that much to generate the synchronization script. That said, in most cases ApexSQL Data Diff will not require such large amounts of free space on your local drive and it further offers additional options that may help you handle this problem and achieve better performance.
Change the data cache location
First thing you can do is to change the location where application stores temporary files. By default, the temporary file location is on your system disk:
C:\Users\{User}\AppData\Local\ApexSQL\ApexSQLDataDiff2014\Temp\
It quite often happens that you have one or more hard drives with larger amount of free space than the system drive. In order to change the location of temporary files, check the Use this folder radio button in the Options dialog and enter the temporary folder path manually or use the ellipsis button on the right side to browse and select the temporary folder destination.
Leverage Data filtering
Furthermore, you can increase the database comparison speed by selecting and comparing only the data you are interested in. Using the Object filter could significantly speed up the comparison process. If you want to exclude all the data for tables that you do not want to compare, just clear a check box in front of such tables.
Additionally, you can refine your filtering by using the Filter editor to set up a more advanced granular filtering. The filter editor can be accessed by clicking the Edit filter in the Object filter tab.
Stay tuned for more in the next article that will include the vertical filtering and Comparison options tuning.
July 9, 2013
