locked
SqlSyncAdapterBuilder Filter when @sync_initialized = 0 RRS feed

  • Question

  • I have a server table which has 50,000+ records in it.  The client only needs a subset of them.  I'm using SQL Express 2008 for the client and SQL Enterprise 2008 for the server and have employed change tracking on both.  I am using the SQLSyncAdapterBuilder to generate SQL commands for me.  When I specify a SQLSyncAdapterBuilder filter, it appears that the SQL command which is ran on the server checks for @sync_initialized = 0.  If the client table has not yet initialized, it acquires ALL 50,000+ records and does NOT apply the filter.  However, if the client table has been initialized... the filter IS applied.

    Question: Is there a way to override the default SQLSyncAdapterBuilder generation of commands to force the filter to be applied to both the initialized and uninitialized client table states?

    VB.NET:

            Dim organizationsAdapterBuilder As SqlSyncAdapterBuilder = New SqlSyncAdapterBuilder(DirectCast(localSyncProvider.Connection, SqlConnection))
    
            With organizationsAdapterBuilder
    
                .TableName = "Organizations"
    
                .FilterClause = "[Organizations].[OrgEntSys] <= 1000"
    
                .ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
    
            End With
    

    TSQL:

    IF @sync_initialized = 0 
    
    	SELECT [Organizations].[OrgEntSys], 
    
    		[Organizations].[OrgSet] 
    
    	FROM [Organizations] 
    
    ELSE
    
    BEGIN 
    
    	SELECT [Organizations].[OrgEntSys], 
    
    		[Organizations].[OrgSet], 
    
    	FROM [Organizations] 
    
    		JOIN CHANGETABLE(CHANGES [Organizations], @sync_last_received_anchor) CT ON CT.[OrgEntSys] = [Organizations].[OrgEntSys] AND CT.[OrgSet] = [Organizations].[OrgSet] 
    
    	WHERE ([Organizations].[OrgEntSys] <= 1000) 
    
    		AND (CT.SYS_CHANGE_OPERATION = ''I'' AND CT.SYS_CHANGE_CREATION_VERSION  <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); 
    
    END
    
    
    • Edited by wjchristenson2 Friday, September 11, 2009 3:30 PM Formatting
    • Moved by Max Wang_1983 Thursday, April 21, 2011 11:28 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, September 11, 2009 3:28 PM

Answers

  • Unfortunately, while the command builder is useful it is not very performant (requires querying the system tables every sync) and has holes such as the one you mentioned above.  Workaround would be to modify the queries to apply the filtering during initial sync.  You may want to also think about streaming the CE file on initial sync instead of performing full int.  It is a much more scalable approach although it is slightly more complex.  Gist is to generate the CE file close to (or on) the database server and then expose the ability to download the file.  This will improve the time it takes to initialize by an order of magnitude.  You would still have to modify the server procs to add the filters during init.

    Apologies for the inconvenience.  This is a known issue that will be addressed in a subsequent release.

    Regards, 


    Sean Kelley
    Senior Program Manager
    Microsoft
    Sunday, September 13, 2009 9:57 PM
    Moderator