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;
}
Comments
Anonymous
April 27, 2011
Is it possible to obtain a sample of this please ?Anonymous
April 09, 2013
Hi Sreedhar, Can u provide us a sample application please. It would be really helpful for us. Email: anand21cen@gmail.comAnonymous
September 27, 2013
Please provide sample code for this. bipinsk_2006@yahoo.comAnonymous
October 05, 2013
Hi its possible to send sample of SQLExpress codeAnonymous
May 27, 2015
I need to create a Win Mobile 6.5 application (C#) in which I need to access a database synchronization. Because not all mobile clients are required on all data ....... Is it somehow possible only the data of a particular client to sync? I mean for each client! I've already tried it with Synchronization Services for ADO .NET for Devices, but unfortunately could not achieve success. Unfortunately, I lack the filtered synchronization. Unfortunately, so far I could find no intelligible to me how to do this and hope for your support. Perhaps it would be possible to send me a description or an example of a filtered synchronization? EMail: meggi74@hotmail.com