Microsoft SyncFramework - Sync different tables into one
- by evnu
Hello,
we are trying to get the Microsoft SyncFramework running in our application to synchronize an oracle db with a mobile device.
Problem
The queries that we need to gather the data on the oracle db take much time (and we haven't found a way to speed them up yet), so we try to split them up in as much portions as possible. One big part of the whole problem is, that we need different information out of one big table, that bloats a query if combined. Unfortunately, the SyncFramework allows only one TableAdapter per SyncTable. Now this is a problem for our application: If we were able to use more than one TableAdapter per SyncTable, we could easily spread the queries in a more efficient way. Using one query per Table which combines all the needed data takes way too much time.
Ideas
I thought of creating different TableAdapters for each one of the required queries and then merge the resulting datasets afterwards (preferably on the server). This seems to work, but is a rather awkward solution.
Does someone of you know a better solution? Or do you have some ideas that could help?
Thanks in advance,
evnu
EDIT:
So, I implemented the merge solution. If you are interested, take a look at the following code. I'll give more details if there are questions.
<WebMethod()> _
Public Function GetChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext
Dim stream As MemoryStream
Dim format As BinaryFormatter = New BinaryFormatter
Dim anchors As Dictionary(Of String, Byte())
' keep track of the tables that will be updated
Dim addTables As Dictionary(Of String, List(Of SyncTableMetadata)) = New Dictionary(Of String, List(Of SyncTableMetadata))
' list of all present anchors
Dim allAnchors As Dictionary(Of String, Byte()) = New Dictionary(Of String, Byte())
' fill allAnchors - deserialize all given anchors
For Each Table As SyncTableMetadata In groupMetadata.TablesMetadata
If Table.LastReceivedAnchor Is Nothing Or Table.LastReceivedAnchor.IsNull Then Continue For
stream = New MemoryStream(Table.LastReceivedAnchor.Anchor)
anchors = format.Deserialize(stream)
For Each item As KeyValuePair(Of String, Byte()) In anchors
allAnchors.Add(item.Key, item.Value)
Next
stream.Dispose()
Next
For Each Table As SyncTableMetadata In groupMetadata.TablesMetadata
If allAnchors.ContainsKey(Table.TableName) Then
Table.LastReceivedAnchor.Anchor = allAnchors(Table.TableName)
End If
Dim addSyncTables As List(Of SyncTableMetadata)
If syncSession.SyncParameters.Contains(Table.TableName) Then
Dim tableNames() As String = syncSession.SyncParameters(Table.TableName).Value.ToString.Split(":")
addSyncTables = New List(Of SyncTableMetadata)
For Each tableName As String In tableNames
Dim newSynctable As SyncTableMetadata = New SyncTableMetadata
newSynctable.TableName = tableName
If allAnchors.ContainsKey(tableName) Then
Dim anker As SyncAnchor = New SyncAnchor(allAnchors(tableName))
newSynctable.LastReceivedAnchor = anker
Else
newSynctable.LastReceivedAnchor = Nothing
End If
newSynctable.SyncDirection = Table.SyncDirection
addSyncTables.Add(newSynctable)
Next
addTables.Add(Table.TableName, addSyncTables)
End If
Next
' add the newly created synctables
For Each item As KeyValuePair(Of String, List(Of SyncTableMetadata)) In addTables
For Each Table As SyncTableMetadata In item.Value
groupMetadata.TablesMetadata.Add(Table)
Next
Next
' fire queries
Dim context As SyncContext = servSyncProvider.GetChanges(groupMetadata, syncSession)
' merge resulting datasets
For Each item As KeyValuePair(Of String, List(Of SyncTableMetadata)) In addTables
For Each Table As SyncTableMetadata In item.Value
If context.DataSet.Tables.Contains(Table.TableName) Then
If Not context.DataSet.Tables.Contains(item.Key) Then
Dim tmp As DataTable = context.DataSet.Tables(Table.TableName).Copy
tmp.TableName = item.Key
context.DataSet.Tables.Add(tmp)
Else
context.DataSet.Tables(item.Key).Merge(context.DataSet.Tables(Table.TableName))
context.DataSet.Tables.Remove(Table.TableName)
End If
End If
Next
Next
' create new anchors
Dim allAnchorsDict As Dictionary(Of String, Byte()) = New Dictionary(Of String, Byte())
For Each Table As SyncTableMetadata In groupMetadata.TablesMetadata
allAnchorsDict.Add(Table.TableName, context.NewAnchor.Anchor)
Next
stream = New MemoryStream
format.Serialize(stream, allAnchorsDict)
context.NewAnchor.Anchor = stream.ToArray
stream.Dispose()
Return context
End Function
