locked
BatchCount is always 1 RRS feed

  • Question

  • I've been trying to implement batching with SQL Change Tracking for some time now.  My situation is this... no matter what I do the BatchCount is always 1.  Initial sync...1.  Subsequent syncs 1.

    I'm using a stored procedure to retrieve my anchor value(s).  that stored procedure is below:

    CREATE PROCEDURE [dbo].[sync_GetNewBatchAnchor]
        @sync_initialized        bigint,
        @sync_batch_size bigint,
        @sync_last_received_anchor bigint,
        @sync_max_received_anchor bigint out,
        @sync_new_received_anchor bigint out,            
        @sync_batch_count int output           
    AS
    Begin        
           -- Set a default batch size if a valid one is not passed in.
           IF  @sync_batch_size IS NULL OR @sync_batch_size <= 0
             SET @sync_batch_size = 10    

           -- Before selecting the first batch of changes,
           -- set the maximum anchor value for this synchronization session.
           -- After the first time that this procedure is called,
           -- Sync Framework passes a value for @sync_max_received_anchor
           -- to the procedure. Batches of changes are synchronized until this
           -- value is reached.
           IF @sync_max_received_anchor IS NULL
             SELECT  @sync_max_received_anchor = change_tracking_current_version()
           
           -- If this is the first synchronization session for a database,
           -- get the lowest timestamp value from the tables. By default,
           -- Sync Framework uses a value of 0 for @sync_last_received_anchor
           -- on the first synchronization. If you do not set @sync_last_received_anchor,
           -- this can cause empty batches to be downloaded until the lowest
           -- timestamp value is reached.
           IF @sync_initialized = 0 Or @sync_last_received_anchor IS NULL OR @sync_last_received_anchor = 0
           BEGIN
                    
                    Select @sync_last_received_anchor = Max( CHANGE_TRACKING_MIN_VALID_VERSION(object_id) )
            From    sys.change_tracking_tables
           
            SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size

            -- Determine how many batches are required during the initial synchronization.
            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 whether this is the 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
    End

     

    In my server sync provider class I use the following code to call the stored procedure above:

                SqlCommand selectNewAnchorCommand = new SqlCommand();
                selectNewAnchorCommand.Connection = conn;
                selectNewAnchorCommand.CommandText = "sync_GetNewBatchAnchor";
                selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.BigInt, 8);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.BigInt, 8);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt, 8);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.BigInt, 8);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt, 8);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4);
                selectNewAnchorCommand.Parameters["@" + SyncSession.SyncMaxReceivedAnchor].Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Parameters["@" + SyncSession.SyncNewReceivedAnchor].Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Parameters["@" + SyncSession.SyncBatchCount].Direction = ParameterDirection.InputOutput;
                this.SelectNewAnchorCommand = selectNewAnchorCommand;
                this.BatchSize = 10;

     

    Can anyone give me some pointers as to why the combination of the stored procedure above and the server sync provider code above would ALWAYS result in a batch count of 1?

    If someone has a concrete example of batching with SQL change tracking I'd really love to take a look at your code (stored procedure and server sync provider).

     

    Thanks in advance for any help,

    Steve E.

     

    Tuesday, August 24, 2010 5:11 PM

Answers

  • the batching is not entirely based on row count but rather as an offset on the last received anchor. so you will have empty batches.

    for example, say you changed 5 rows, batchsize of 10, your last received anchor is 100, and the value retrieved for sync max anchor via change_tracking_current_version() is 1000.

    the range of rows to be retrieved will be between 100 to 1000. so it will start with 100 to 110, 110 to 120 and so on. assuming the 5 rows you updated has a time stamp of 901 to 905. the rows will only come down at batch 900-910.

    a typical example for this would be doing 1 row insert on one table, then doing another 1000 row insert on another table. even though the first table has one row insert, it will have empty batches because the 1000 row insert has advanced change_tracking_current_version() and that value will be retrieved as the max anchor for that sync session.
     

    Friday, August 27, 2010 2:48 AM

All replies

  • have you tried runnign SQL Profiler to check on the values being passed and the batch generated everytime you sync?

    also, can you check your SelectIncremental SPs if theyre checking for @sync_initialized = 0?

    Thursday, August 26, 2010 8:28 AM
  • Hi June,

    As far as I can tell valid values are being passed to the select new anchor command's stored procedure as I've illustrated above.  I can see a difference in the anchor values (new, last received, max received).

    The SelectIncremental sql is being generated by a SqlSyncAdapterBuilder call and, as far as I can tell, it is checking for @sync_initialized.

    I've have a very simple test case that I run that initializes the database, "touches" every server side row in a particular table, and then does a subsequent sync.  In each case the BatchCount is equal to 1... no matter if I'm receiving 1 incremental update or 100 incremental updates.

    In your opinion, should the stored procedure and select new anchor command I've illustrated in my original post work?

    Do you have a concrete example of using change tracking to batch up updates? 

     

    Steve E.

     

    Thursday, August 26, 2010 2:21 PM
  • looks good to me, havent had a chance to copy and paste your code to test thought.

    i maybe wrong and you can confirm by a sql profiler trace, if i remember it right, the SelectIncremental Stored Procs checks if @sync_initialized = 0 and if it is, it selects all the rows. So, the batching will not take effect on first sync.

    if you have done the initial sync though and you did "touch" enough rows thats more than your batch size, then it should do batching.

    chk out this similar thread: http://social.microsoft.com/Forums/en/syncdevdiscussions/thread/5711a193-baaa-4de9-b2d0-2d6ffc6421d1

    Thursday, August 26, 2010 3:06 PM
  • This is getting stranger by the minute.  I touched a large number of rows in various tables on the server database and did an initialization sync.  With a batch size of 10 I'm getting back a batch count of 57.

    What is strange is that my sync_GetNewBatchAnchor procedure is getting called 57 times for each table that I'm trying to sync even though, for example, my Region table only has 4 rows in it.

    This can't be the expected behaviour is it?

    I would have expected behaviour similar to the following:

    Region table has 4 rows > 1 batch

    Site table has 18 rows > 2 batches

    Building table has 114 rows > 12 batches

    etc.

     

    Any thoughts are appreciated.

     

    Steve E.

     

     

    Thursday, August 26, 2010 5:23 PM
  • the batching is not entirely based on row count but rather as an offset on the last received anchor. so you will have empty batches.

    for example, say you changed 5 rows, batchsize of 10, your last received anchor is 100, and the value retrieved for sync max anchor via change_tracking_current_version() is 1000.

    the range of rows to be retrieved will be between 100 to 1000. so it will start with 100 to 110, 110 to 120 and so on. assuming the 5 rows you updated has a time stamp of 901 to 905. the rows will only come down at batch 900-910.

    a typical example for this would be doing 1 row insert on one table, then doing another 1000 row insert on another table. even though the first table has one row insert, it will have empty batches because the 1000 row insert has advanced change_tracking_current_version() and that value will be retrieved as the max anchor for that sync session.
     

    Friday, August 27, 2010 2:48 AM