Notes & articles

Getting better performance out of SQL Data Compare

SQL Data Compare can take a long time to compare and synchronize data sources. The speed of the comparison depends mostly on disk write speed, processor speed, memory, and the size of the databases. However, there are a number of things that you can do to get better performance out of SQL Data Compare.

This article explains how you can run faster comparisons and prevent your computer from running out of disk space. It also explains how to schedule the comparison and synchronization during periods of low database activity.

Running out of disk space

When you run a comparison, SQL Data Compare retrieves the data from the two data sources and copies the data to a temporary location on your local machine. If the SQL Servers are remote, the data is copied across the network to your local machine.

Although there is no limit on the size of the data sources that you can compare, the amount of available disk space will restrict the amount of data that SQL Data Compare can compare. You'll need roughly twice the size of the database for the comparison and up to four times the size of the database to generate the synchronization SQL.

If you do not have enough disk space, you can:

• change the location that SQL Data Compare uses for temporary files to a location with more disk space
• filter the data at table-level, column-level, or row-level so that the temporary files are smaller
• review the options for comparison behavior

Changing the location of the temporary files

By default, the location of the temporary files is defined by the TMP environment variable. You are not recommended to edit the TMP variable as this will affect all programs that use this variable. Instead, you create a new environment variable called RGTEMP, and specify the required location, for example a different hard disk with more disk space. The RGTEMP variable affects only Red Gate programs.

Note that you may need to log out of Windows® for the change to take effect.

Filtering the data

SQL Data Compare selects all tables, all columns, and all rows for comparison, so you'll be temporarily storing all the data that differs in the two data sources. By filtering the data, you compare only the data you're really interested in, and the size of the temporary files is reduced.

You filter data using the Tables & Views tab on the Project Configuration dialog box. The filtering techniques described here are useful when your tables contain a large amount of data that differs:

• Table filtering

  Exclude all the data for a table by clearing its check box:

  

• Column filtering (vertical filtering)

  Exclude all the data in particular columns by clicking in the Columns in Comparison box for the associated table, and then clearing the check boxes for the required columns:

  

• Row filtering (horizontal filtering)

  Exclude rows by clicking WHERE Clause, and then typing a T-SQL WHERE clause in the box:

  

You can apply the same WHERE clause to multiple tables, by highlighting the required tables before opening the WHERE Clause Editor.

You can apply a different WHERE clause for each data source by clearing the Use the same WHERE clause for both data sources check box.

Note that row filtering is not available if you are using a backup as a data source.

Reviewing the options for comparison behavior

By default, SQL Data Compare includes identical values in the comparison results. If your data sources always contain similar data, clear the Show identical values in results check box on the Options tab of the Project Configuration dialog box. When you do this, SQL Data Compare does not store data that is identical, reducing the disk space that is required.

If you are comparing tables with large amounts of data that changes infrequently, select the Use checksum comparison check box. You'll need to clear the Show identical values in results check box before you do this. If the checksums are equal, SQL Data Compare won't compare that table, and the data is not stored for that table, reducing the disk space that is required.

Note that if your table contains large data types such as text or images, these columns are not included in the checksum, and you are not recommended to use the Use checksum comparison option.

Scheduling the comparison and synchronization

If you still find the comparison or the synchronization is taking too long to run, you may want to schedule these tasks to run at a time when your SQL Server has low levels of activity. To do this, you'll need the Pro edition of SQL Data Compare.

In SQL Data Compare version 6, when you set up your comparison project, you can save the project file wherever you want. You can then create a script with SQL Data Compare commands to open the comparison project and run the synchronization.

For example, you could create a batch file (.bat) as follows:

 C:
 cd path_to_SQLDataCompare_installation_folder
 SQLDataCompare    

 /project:"D:\SQLDataCompare\Projects\Widgets.sdc"    
 /synchronize

You can then use the Microsoft® Windows® Scheduled Task wizard to run the batch file.

Summary

In this article, you've learned how to change the location of temporary files, filter the data before it is compared, and set the options that determine comparison behavior so that you don't run out of disk space. To find out more about setting up your comparison projects, see Selecting tables and views.

You've also learned how to schedule a comparison and synchronization using the command line. To see more examples using the command line, see Examples using the command line.