locked
Sync with batching problem RRS feed

  • Question

  • Hi

    I'm having an issue when trying to implement batching with the help of this article - http://msdn.microsoft.com/en-us/library/bb902828.aspx

    I'm finding that not all my rows are coming down on the initial sync - in fact the the stored procedure is running several times, but the @sync_last_received_anchor parameter isn't being set to the previous output of the SP - it is being run with the same parameters each time.

    Has anybody else come across this before, or have any ideas?

    Thanks,

    Andy.
    • Moved by Max Wang_1983 Friday, April 22, 2011 12:00 AM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, May 21, 2009 2:18 PM

Answers

  • I previously posted about my hastles on trying to get a filtered subset of data to be batched for incremental biderectional updates.
    Ive had a mini breakthrough in batching that i thought i should share here.
    Ive found that the reason why batching as implimented on MSDN behaves erratically in my tests on large data tables is because the 2 tracking fields 'lasteditdate timestamp' and 'creationdate bigint' were added to an existing table which was already filled with data. This means that while lasteditdate is unique over the 350000 records, the creationdate value is the same for all the records i.e. @@dbts+1(this parameter is the same as the last active timestamp in the db).
    So when the framework uses the usp_GetNewBatchAnchor procedure,if the @synch_last_received_anchor starts at zero, it works out that there should be x amount of batches in @batch_count but each batched query brings back nothing because the where clause in the incremental insert command 'select * from table where creationdate>@synch_last_received and creationdate<@synch_new_received_anchor' is always too small to reach the value of the creationdate field.It only reaches a match on the very last batched query and when it does, the amount of data it pulls out is far bigger than batchsize specified, resulting in memory errors on the mobile device.

    So the solution is : when  the creationdate field is added to your EXSITING table(either by visual studio or manually), its best to update its value on each row to the value of lasteditdate. e.g.
    update account
    set creationdate=lasteditdate
    Maybe this is an obvious solution , but when using the example straight out of msdn this is not really made clear.
    So far it seems to be working. Still not happy with the speed, but maybe i need to play around with it a bit more.
    Does this solve your problem as well?


    Im using SyncFramework CTP2,vs2008, SQL 2005, windows mobile 5, SQL CE 3.5 sp1
    Ive also implimented SyncCom which enables GZip compression over WCF on mobile devices
    Wednesday, July 29, 2009 4:02 PM

All replies

  • Did you implement the example 1:1 or did you use your own data table structure?

    If yes, can you please post the structure of your ID-column and the tracking-columns (Change,Create,Delete).


    Regards,
    Martin
    Friday, May 22, 2009 8:23 AM
  • Hi,

    We had to change from using bigint for the create_timestamp to using a numeric(19,0) due to the database having to be compatible with other products which wont support bigints.  We do cast to bigint in the new batch anchor stored procedure, the SelectIncrementalInsertsCommand and SelectIncrementalUpdatesCommmand.

    So the tracking-columns are as follows (as the tables are so far download only):
    update_originator_id (int, not null)
    update_timestamp (timestamp, not null)
    create_timestamp (numeric(19,0), null)

    the ID-columns vary from a couple of varchars to integers - it happens on all sync tables.

    Cheers,

    Andy.
    Friday, May 22, 2009 8:35 AM
  • Hi,

    why is the create-timestamp allowed to be null?
    If it comes from an old table structure... try to update the create_timestamp with the update_timestamp if it´s null.

    E.g.

    UPDATE [table] SET create_timestamp = update_timestamp WHERE create_timestamp is null


    This often helps with various problems. Update, Create and Delete columns should never be null.


    I also do not understand why you have to cast in the new batch anchor SP _AND_ in the IncrementalCommands.
    Isn´t it enough to cast in one of them, e.g. in the Incremental Commands (.... .WHERE update_timestamp > CONVERT(.....)).

    Maybe it helps examples for incremental comamnds and your new anchor SP.



    Regards,

    Martin
    Friday, May 22, 2009 8:40 AM
  • Hi,

    I've changed the create_timestamp to not null as you suggested - although there were no nulls due to the default value of the DBTS.

    My theory was that if i present the columns as they would have been as if they were bigints it should work the same as the example.

    These are the inserts and updates commands - theres just a find and replace on the create_timestamp column to cast it

    --SelectIncrementalInsertsCommand

    SELECT

     

    [FIELDNAMES] FROM [TABLENAME]

    WHERE

     

    (CAST(create_timestamp AS bigint) > @sync_last_received_anchor AND CAST(create_timestamp AS bigint) <= @sync_new_received_anchor AND update_originator_id <> @sync_originator_id)

    --SelectIncrementalUpdatesCommand

    SELECT

     

    [FIELDNAMES] FROM [TABLENAME]

    WHERE

     

    (update_timestamp > @sync_last_received_anchor AND update_timestamp <= @sync_new_received_anchor AND update_originator_id <> @sync_originator_id AND CAST(create_timestamp AS bigint) <= @sync_last_received_anchor)

    And this is the Stored Procedure - I've found this just gets the minimum anchor for the first table in the group, but i can deal with this problem later - the first table is also not getting all the rows

    ALTER procedure [sp_new_batch_anchor] (
             @sync_last_received_anchor timestamp , 
             @sync_batch_size int,
    	@sync_table_name nvarchar(500),
    	@sync_max_received_anchor timestamp output,
    	@sync_new_received_anchor timestamp output, 		
    	@sync_batch_count int output)		
    as            
    	DECLARE @SQL NVARCHAR(4000);
    	DECLARE @ParamDef NVARCHAR(4000);
    
    
    	if @sync_batch_size <= 0
    		set @sync_batch_size = 1000    
    
    	if @sync_max_received_anchor is null
    		set @sync_max_received_anchor = @@DBTS
    
    	if @sync_last_received_anchor IS NULL OR @sync_last_received_anchor = 0
    	BEGIN
    		--Determins lowest timestamp in table as starting point
    		SET @ParamDef = N'@sync_last timestamp output';
    		SET @SQL = N' 
    				SELECT @sync_last = MIN(TimestampCol) FROM (
    				  SELECT MIN(update_timestamp) AS TimestampCol FROM ' + @sync_table_name + '
    				  UNION
    				  SELECT CAST(MIN(create_timestamp) AS bigint) AS TimestampCol FROM ' + @sync_table_name + '
    				) MinTimestamp	'
    		EXECUTE sp_executesql @SQL, @ParamDef, @sync_last = @sync_last_received_anchor output;
    		
    		SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
    
    		if @sync_batch_count <= 0
    			SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_last_received_anchor / @sync_batch_size))
    
    	END
    	ELSE
    	BEGIN
    
    		SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
    
           -- Determine how many batches are required during subsequent synchronizations.
    		IF @sync_batch_count <= 0
    			SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_new_received_anchor /  @sync_batch_size)) + 1  
    	END
    
    	--Check if last batch
    	IF @sync_new_received_anchor >= @sync_max_received_anchor
    	BEGIN
    
    	 SET @sync_new_received_anchor = @sync_max_received_anchor        
    	 IF @sync_batch_count <= 0
    	   SET @sync_batch_count = 1
    
    	END
    Many thanks,

    Andy.

    Friday, May 22, 2009 9:38 AM
  • Hi,

    at the moment I do not have a real idea.

    But I would try it first with one table and then an easier way to detect the lowest timestamp on that table. Only to get sure that the problem is not there. E.g.

    SELECT @sync_last = MIN(TimestampCol) FROM (
    SELECT MIN(update_timestamp) AS TimestampCol FROM [yourTestTable]
    UNION
    SELECT CAST(MIN(create_timestamp) AS bigint) AS TimestampCol FROM [yourTestTable]
    ) MinTimestamp
    Without sp_executesql and the whole stuff.

    It´s always the best way to create a minimal example first.
    How does your selectNewAnchor-Call looks like?
    And did you run the SQL-Profiler?


    I have to say, that I also did not finished a working example of batching until now, because I used DateTime in my Tracking-Columns (as shown in nearly all beginners tutorials)
    which seems to be not possible for batching and at the moment I cannot change my datastructures to a Timestamp.


    Regards,

    Martin
    Friday, May 22, 2009 10:20 AM
  • Hi,

    The lowest timestamp bit is working ok.  I put some inserts into the stored procedure to make sure of this.  I'm finding that the output of the sp - sync_new_recieved_anchor is correct the first time (ie a batch size larger than the smallest timestamp), but you would expect this to be input as sync_last_received_anchor next time the sp is called.

    I run the SQL-Profiler and found that the sp is being called each time with exactly the same inputs - therefore not progressing along to the rest of the data.

    This is the code which sets up the SelectNewAchorCommand:
    SqlCommand anchorCmd = new SqlCommand();
    anchorCmd.CommandType = CommandType.StoredProcedure;
    anchorCmd.CommandText = "sp_new_batch_anchor";
    anchorCmd.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.InputOutput;
    anchorCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Input;
    anchorCmd.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int).Direction = ParameterDirection.Input;
    anchorCmd.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar).Direction = ParameterDirection.Input;
    anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;
    anchorCmd.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int).Direction = ParameterDirection.InputOutput;
    _serverProvider.SelectNewAnchorCommand = anchorCmd;
    _serverProvider.BatchSize = 3000;
    



    Its like the 'data window' isn't moving along to the next batch.


    Many thanks,


    Andy.
    Friday, May 22, 2009 10:41 AM
  • Hi Andy,

    mmm everything seems fine. Sorry that I cannot help you.
    Maybe it´s really a bug in the Batching and that´s the reason why I also was not able to implement some batching until now :-(


    Regards,
    Martin
    Friday, May 22, 2009 11:11 AM
  • As an update I've reverted to using bigint for create_timestamp which has not solved the issue.

    Also i'm using Sync Services for ADO.net v1.0.  Would there be any benifit in using v2?

    Thanks.

    • Proposed as answer by bowlerc Wednesday, July 29, 2009 4:01 PM
    Tuesday, May 26, 2009 1:14 PM
  • I previously posted about my hastles on trying to get a filtered subset of data to be batched for incremental biderectional updates.
    Ive had a mini breakthrough in batching that i thought i should share here.
    Ive found that the reason why batching as implimented on MSDN behaves erratically in my tests on large data tables is because the 2 tracking fields 'lasteditdate timestamp' and 'creationdate bigint' were added to an existing table which was already filled with data. This means that while lasteditdate is unique over the 350000 records, the creationdate value is the same for all the records i.e. @@dbts+1(this parameter is the same as the last active timestamp in the db).
    So when the framework uses the usp_GetNewBatchAnchor procedure,if the @synch_last_received_anchor starts at zero, it works out that there should be x amount of batches in @batch_count but each batched query brings back nothing because the where clause in the incremental insert command 'select * from table where creationdate>@synch_last_received and creationdate<@synch_new_received_anchor' is always too small to reach the value of the creationdate field.It only reaches a match on the very last batched query and when it does, the amount of data it pulls out is far bigger than batchsize specified, resulting in memory errors on the mobile device.

    So the solution is : when  the creationdate field is added to your EXSITING table(either by visual studio or manually), its best to update its value on each row to the value of lasteditdate. e.g.
    update account
    set creationdate=lasteditdate
    Maybe this is an obvious solution , but when using the example straight out of msdn this is not really made clear.
    So far it seems to be working. Still not happy with the speed, but maybe i need to play around with it a bit more.
    Does this solve your problem as well?


    Im using SyncFramework CTP2,vs2008, SQL 2005, windows mobile 5, SQL CE 3.5 sp1
    Ive also implimented SyncCom which enables GZip compression over WCF on mobile devices
    Wednesday, July 29, 2009 4:02 PM
  • Hi Mandy,
    Can I bother you to restate your problem just so I can understand the original issue here.  What is your scenario and what sync directions are you using?


    Maheshwar Jayaraman - WCF -- http://blogs.msdn.com/dotnetremoting
    Wednesday, August 12, 2009 11:20 PM
    Moderator
  • Alright Maheshwar, thanks for looking into this.

    I have implemented bidirectional synchronization with decoupled tracking for exactly 4 tables. Server is SQL Ent edition and client is Sql Express 2005 with WCF service in between to synchronize data across two databases.

    TableC - 100 records
    TableAG - 200000 records
    TableT - 400000 records
    TableK - 600000 records

    Now server database already exists and in use, so we temporarily modified sync triggers to insert records in empty server Tracking tables in order to download them at client end during first synchronization. [IS THIS THE RIGHT APPROACH, OR DO WE HAVE ANY ALTERNATIVE TO DOWNLOAD ALL THE SEVER RECORDS TO CLIENT?]

    Please let me know, if you need any other info.



    Thursday, August 13, 2009 10:27 AM