Microsoft SyncFramework - Sync different tables into one

Question

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

Answer 1

This is a good idea,but i would like to ask you a question,since i am trying to synchronize between sqlce desktop and oracle,could you tell me how did you use the SelectNewAnchorCommand any code will be appreciated thanks in advance