locked
Batching with datetime not timestamps RRS feed

  • Question

  • Hi there

    I have created an application with VS 2008 that has a local database cache which synchronises with a MS SQL 2000 server database.

    The trouble that I am running into is that the amount of data that has changed that is being transferred is too large to be stored in memory all at once and causes an out of memory exception. I have to implement batching for my application.

    I have looked at many of the batching examples out there and they all use timestamp or similar to split the changes records into manageable sections. The code that was generated for my application by VS uses datetime fields to track changes not timestamps.

    This leaves me asking myself the question how am I going to implement batching for my application at this late stage?

    if anyone has any ideas or examples for this I would be very grateful for the help!

    Mark

    • Moved by Hengzhe Li Friday, April 22, 2011 3:09 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, June 4, 2009 3:31 AM

All replies

  • Hi Mark,

    I´ve runned exactly into the same problem as I started with using the Local Database Cache Tool from VS2008 and wanted to implement batching after this.

    As far as I know, it is NOT possible to implement batching with DateTime. You cannot convert DateTime to Timestamp, because it is something totally different (Timestamps are logical clocks in MS SQL) and if you change the SP for Batching to use only DateTime, I think you have too many fragments and your batching gets too slow. But I did not try it.

    What I did...

    The Local Database Tool from VS2008 creates code to Sync all columns of the database tables.
    Because I did not need all these columns, I removed the columns out of the generated code. That helped  for the first time to decrease memory usage.

    You can also forget the whole generated code and use the SqlSyncAdapterBuilder class. For me it was not possible, because my tables have int-IDs as primary keys and UIDs as second (not primary key) columns, which I need for creating data on my mobile devices. This does not work with the SqlSyncAdapterBuilder because this class can only sync via PrimaryKey.

    The SqlSyncAdapterBuilder has the advantage, that you can easily add the table columns you want to sync and the whole sql-code is generated in behind.

    E.g:
     public static SyncAdapter CreateRoomsAdapter(SqlConnection connection)
            {
               SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder(connection);
    
               builder.SyncDirection = SyncDirection.DownloadOnly;
                builder.CreationTrackingColumn = "CREATE_DATE";
                builder.UpdateTrackingColumn = "CHANGE_DATE";
                builder.DeletionTrackingColumn = "DELETE_DATE";
                
                builder.TableName = "FL_ROOM_T";
                
                string[] roomCols = new string[8];
                roomCols[0] = "ROOM_ID";
                roomCols[1] = "ROOM_CODE";
                roomCols[2] = "ROOM_BARCODE";
                roomCols[3] = "CHANGE_DATE";
                roomCols[4] = "USER_NAME";
                roomCols[5] = "COMPUTER_NAME";
                roomCols[6] = "CHANGE_FROM";
                roomCols[7] = "CREATE_DATE";
    
                builder.DataColumns.AddRange(roomCols);
    
                builder.TombstoneTableName = "FL_ROOM_T_SYNC";
    
                return builder.ToSyncAdapter(true, true, false, false);
            }
    As last hint...

    If your Sync-Szenario uses a WebService/WCF, then you can decrease memory-usage Surrogates, because there is a Serialization-Bug in the Sync-Framework (data table objects are serialized twice as xml => more memory usage). Have a look here:

    http://blogs.msdn.com/mahjayar/archive/2008/10/01/dbsyncprovider-improving-memory-performance-in-wcf-based-synchronization.aspx



    I am planing to write a tutorial about everything I´ve learned until now to give something back to the comunity. So I would be happy if you leave me a note if my comments help you or if you run into trouble. Maybe I also wrote something wrong. So please correct me ;-)


    Regards from Cologne,

    Martin
    • Edited by macap Thursday, June 4, 2009 11:59 AM
    Thursday, June 4, 2009 8:55 AM