At ApexSQL, we understand that performance is a critical factor for customers using our software. For this reason, we recently conducted thorough performance testing of our SQL comparison and synchronization tool ApexSQL Diff 2012 and its predecessor, ApexSQL Diff 2011. Although the performance of ApexSQL Diff 2011 was good, there was also plenty of room for improvement
The test procedures and ApexSQL Diff 2012 test results achieved by careful optimization of critical bottlenecks found in ApexSQL Diff 2011 will be explained below
Test setup
ApexSQL Diff application performance was researched in three main areas: objects comparison, objects synchronization, and GUI. For testing purposes, several different databases were created, but the one with 100,000 objects was used as the main testing database. The number of objects within the database was based on two main criteria:
- Most ApexSQL Diff users have databases with no more than 100,000 objects. In case the application works well and fast with a given number of objects, fewer objects will not present a problem
- This number of objects allows measuring the real working time in each test and discarding all pre-requisite and post-requisite actions that could be performed by any part of the comparison and/or synchronization processes
We used 20 object types and 5,000 objects of each type: Stored Procedures, Tables, Views, Types, Schemas, Functions, Assemblies, Rules, Xml Schema Collections, Message Types, Contracts, Queues, Services, Routes, Remote Service Bindings, Full Text Catalogs, Partition Functions, Partition Schemes, Roles, and Database Triggers
Databases used for more specific tests are one with 10,000 schema objects, and a smaller database with 20,000 objects (1,000 for each of 20 object types)
All tests were done on a local PC. No concurrent sessions or applications were running. MSSQL Server was installed on the local machine. Therefore, no network traffic usage was affecting the performance test
For each main test category, the following subtests were performed:
- General tests
- Schema comparison
- Object comparison
- Results display
- Select all/deselect all comparison results
- Object filtering
- Dependencies processing
- Live database
- Complete Comparison
- Complete synchronization
- Complete process
- Backup to live database
- Complete Comparison
- Complete synchronization
- Complete process
- Script folder to live database
- Complete Comparison
- Complete synchronization
- Complete process
- Source control to live database
- Complete Comparison – Source control
- Complete synchronization
- Complete process
Each test was performed three times, and the average was used as the final result
General tests
- Schema comparison
Loading of schema objects was the bottleneck discovered in ApexSQL Diff 2011 and is mainly related to handling object owners. For the purpose of this test, we used a database with 10,000 schema objects. It was compared against itself, which resulted in 20,000 schema objects loaded and compared
- Object comparison
The object comparison test involved comparing loaded objects. This process was already well optimized in ApexSQL Diff 2011, but a few bottlenecks had been identified and fixed
For the purpose of this test, a database with 100,000 objects was used against itself, which resulted in 200,000 objects loaded and compared
Stay tuned for more performance results in part 2 that will include: results display, select all/deselect all comparison results, dependencies processing, object filtering, live databases, backup to live databases, script folder to live databases, and source control to live databases
April 10, 2013
