Synchronization Services for ADO .NET for Devices: Improving performance by skipping tables that don’t need synchronization
Performance is an important factor when you synchronize databases that consist of large number of tables. One way to improve performance is to identify tables that have no data to synchronize and exclude them from the synchronization process.
SqlCeClientProvider performs some processing for every table you request to be synchronized irrespective of whether the table has changed data or not. For a table full of changed data, this processing time is minimal compared to the time required for the actual synchronization. However, for a table with nothing to synchronize, the processing time is an overhead because it is the time that does not need to be spent. In fact, this overhead could affect performance of overall synchronization process significantly if there are large numbers of tables that have no changed data. Therefore, you should identify tables that have no data to synchronize and exclude them from the synchronization process to improve performance.
In a test we conducted in our labs, we used a database with 100 tables, out of which only 10 contained the changed data (200 rows inserted into each) that need to be synchronized. By skipping the other 90 tables during synchronization process, the overall synchronization time was reduced by 75-80%.
There are many ways to detect the changed tables on both server side and the client side. The following example code demonstrates one way to do this.
Construct sync agent's sync group with tables that have changed data:
private SyncAgent PrepareAgent(List<SyncTable> clientTables)
{
SyncGroup agentGroup = new SyncGroup("ChangedGroup");
SyncAgent changedAgent = new SyncAgent();
foreach (SyncTable table in clientTables)
{
table.SyncGroup = agentGroup;
changedAgent.Configuration.SyncTables.Add(table);
}
return changedAgent;
}
Get changed tables from local client database:
private List<SyncTable> GetChangedTablesOnClient()
{
// Initialize the list of tables to synchronize
List<SyncTable> tables = new List<SyncTable>();
// Open connection to the database
if (clientSyncProvider.Connection.State == System.Data.ConnectionState.Closed)
{
clientSyncProvider.Connection.Open();
}
// Prepare Command
SqlCeCommand command = new SqlCeCommand();
command.Connection = (SqlCeConnection)clientSyncProvider.Connection;
command.Parameters.Add(new SqlCeParameter("@LCSN", System.Data.SqlDbType.BigInt));
// Retreive changed tables
foreach (string tableName in this.clientTables.Keys)
{
// Build a command for this table
command.CommandText = GetQueryString("[" + tableName + "]");
// Execute the command
long lcsn = GetLastSyncCsn(command.Connection, tableName);
command.Parameters["@LCSN"].Value = lcsn;
int result = (int)command.ExecuteScalar();
// If the table contains changed data
if (result > 0)
{
// then add it to the synchronization list
tables.Add(this.clientTables[tableName]);
}
}
command.Dispose();
return tables;
}
private string GetQueryString(string tableName)
{
return String.Format(@"select count(*) from {0} Tbl where (((Tbl.__sysInsertTxBsn IS NOT NULL) AND ((Tbl.__sysInsertTxBsn NOT IN (select __sysTxBsn from __sysTxCommitSequence) AND Tbl.__sysInsertTxBsn > @LCSN) OR (exists(select __sysTxBsn from __sysTxCommitSequence where Tbl.__sysInsertTxBsn = __sysTxBsn AND __sysTxCsn > @LCSN))))
OR ((Tbl.__sysChangeTxBsn IS NOT NULL) AND ((Tbl.__sysChangeTxBsn NOT IN (select __sysTxBsn from __sysTxCommitSequence) AND Tbl.__sysChangeTxBsn > @LCSN) OR (exists(select __sysTxBsn from __sysTxCommitSequence where Tbl.__sysChangeTxBsn = __sysTxBsn AND __sysTxCsn > @LCSN)))))", tableName);
}
private long GetLastSyncCsn(SqlCeConnection connection, string tableName)
{
SqlCeCommand command = null;
SqlCeDataReader reader = null;
long num3;
long num = 0L;
try
{
command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = string.Format("select SentAnchor from {0} Where TableName = @tablename", "__sysSyncArticles");
command.Parameters.AddWithValue("@tablename", tableName);
reader = command.ExecuteReader();
bool flag = true;
byte[] buffer = new byte[60];
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
reader.GetBytes(0, 0L, buffer, 0, 8);
long num2 = BitConverter.ToInt64(buffer, 0);
if (flag)
{
num = num2;
}
flag = false;
if (num2 < num)
{
num = num2;
}
}
}
num3 = num;
}
finally
{
if (command != null)
{
command.Dispose();
}
if (reader != null)
{
reader.Dispose();
}
}
return num3;
}
Get the changed tables from the server side
// service method
public Collection<string> GetServerChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
{
Collection<string> tables = new Collection<string>();
SyncContext changes = _serverSyncProvider.GetChanges(groupMetadata, syncSession);
if (changes.DataSet.Tables.Count > 0)
{
foreach (DataTable table in changes.DataSet.Tables)
{
if (table.Rows.Count > 0)
{
tables.Add(table.TableName);
}
}
}
return tables;
}
// client side methods to get the server changes by calling
// the service proxy method
private string[] GetServerChanges()
{
SyncGroupMetadata metaData = this.GetMetadata();
SyncSession session = new SyncSession();
session.ClientId = clientSyncProvider.ClientId;
return this.proxyExtension.GetServerChanges(metaData, session);
}
private SyncGroupMetadata GetMetadata(Collection<string> clientTables)
{
SyncGroupMetadata groupMetadata = new SyncGroupMetadata();
foreach (string tableName in clientTables)
{
SyncTableMetadata tableMetadata = new SyncTableMetadata();
tableMetadata.LastReceivedAnchor = clientSyncProvider.GetTableReceivedAnchor(tableName);
tableMetadata.TableName = tableName;
groupMetadata.TablesMetadata.Add(tableMetadata);
}
return groupMetadata;
}