Contents

Improve the comparison performance

The speed of Data Compare for SQL Server depends mostly on:

• SQL server load
• Size of the databases
• Network connection speed
• Processor speed of the local PC
• Disk read/write speed of the local PC
• Amount of memory on the local PC

In certain situations, Data Compare can take quite a long time to compare and synchronize data sources. However, you can get better performance out of the tool by:

• Change the location of temporary files
• Filter the comparison
• Review comparison options
• Schedule comparison and synchronization

Change the location of temporary files

When performing database comparison, Data Compare for SQL Server uses temporary files. You can change the location of these files to avoid running out of disk space.

By default, Data Compare stores its cache files in the Windows Temporary Folder. You can select to store cache in a different folder, on a different hard disk, for instance.

To do this:

1. Select the Options command from the Tools menu on the toolbar.
2. Go to the General tab under the Data Comparison options.
3. Select the Custom Folder radio button.
4. Specify a path to the folder you want to store cash files to. Alternatively, click the button in the combo-box and browse for the desired cache folder.
5. Click OK.

Filter the comparison

By default, all tables, columns, and rows are selected for comparison. Consequently, all the data that differs in the data sources will be stored temporarily on your computer. However, you can reduce the size of the temporary files by comparing only the data you’re interested in. To achieve that, you need to filter the data.

Table and view filtering

With Data Compare for SQL Server, you can exclude all the tables from the comparison. The views will remain intact. To achieve that, unselect the Compare tables option when configuring comparison options in the New Data Comparison Wizard.

Column filtering

To exclude columns from the comparison:

1. On the Mapping tab of the New Data Comparison Wizard, click the button in the Columns in Comparison column of the table you’re interested in.

2. In the Column Mapping window that opens, indicate the comparison key column. The difference between the columns will be ignored during comparison if the checkboxes in the Compare column are clear. The column will be excluded from the UPDATE statement and will not be synchronized if the checkboxes in the Sync column are clear.

Row filtering

With Data Compare for SQL Server, you can exclude rows by applying a WHERE clause to the comparison.

Note

You can only filter rows if a database is selected as the data source. You can not use WHERE clauses for backups and Scripts folders.

To filter the comparison with a WHERE clause:

1. On the Mapping tab, select the object to apply the filter to.

2. Click the Filter button from the toolbar.

3. In the WHERE Filter window, that opens, type a valid Transact-SQL WHERE clause.

4. If you want to apply the expression to both the Source and the Target, select the Use the same expression for Source and Target check-box.

5. Click OK to apply the filtering options.

6. The filtered object will be marked with a filter icon in the grid on the Mapping tab.

Review comparison options

By default, identical records are included in comparison results and Data Compare for SQL Server stores identical data temporarily.

In case your data sources always contain similar data, it is recommended to unselect the Identical records option on the Options tab of the New Data Comparison Wizard. By doing that, you’ll reduce the disk space required as identical records will not be stored and won’t appear in the comparison result.

Schedule comparison and synchronization

If your comparison and/or synchronization tasks still take much time, you may want to schedule these tasks to run at a time when your SQL Server is less busy.

You can schedule or automate a comparison or synchronization with the help of Data Compare and an available task scheduler tool.

First, you need to create a script for comparing and synchronizing the data sources.

Create a data comparison and/or synchronization script

1. Having created and configured new project, click the Save Command Line button at the bottom of your New Data Comparison window.

2. In the window that opens, check and change the configurations if necessary.

Note

If you are interested in the comparison only, clear the Synchronization option.

3. Click the Validate button to check if the settings are correct.

4. Click the Save button to save the command line settings to a .bat file.

Note

You can reach the Command line execution file settings window from the Data Synchronization Wizard as well.

Schedule the execution of the script

To schedule a comparison and\or synchronization of the two data sources, you can use a Windows Task Scheduler or any other task scheduler tool available.

In fact, you just need to set the required time or schedule the *.bat file execution using the convenient tool.

To create a task with the Task Scheduler:

1. Run Windows Task Scheduler.

2. In the Actions menu, select Create Basic Task.

3. Type a name for the task and an optional description, and then click Next.

4. Select a trigger for the task and click Next.

5. On the Action tab, click Start a program.

6. Click Browse to enter a path to the *.bat file you saved earlier.

7. Click Next.

8. Check Summary for the task to be created.

9. Click Finish.

To discover how to schedule and automate the comparison or synchronization processes, see Schedule database synchronization.

To learn how to organize the comparison and synchronization process for tables containing 1 million records, watch this video.

Want to Find out More?

• Previous
• Next