Are there standard solutions for Change tracking and filtering large tables. RRS feed

  • Question

  • Dear colleagues,

    Let me explain our application first. We build and sell an ERP system. ERP systems are known for their large databases, containing thousands of tables with tables easily exceeding a million rows. Some of our customers need parts of our functionality in environments where wireless connection with the database is not feasible (e.g. inspections of a newly build ship hull or other boxes of Faraday, near machines that generate EMPs, etc.). For those cases we provide small parts of our functionality on a mobile device that is kept ‘in sync’ using Change Tracking.

    Because mobile devices have more restrictions on the amount of available memory and because synchronizing your mobile device should not last more than a couple of minutes, a function that uses a frequently mutated table that contains over 10,000,000 rows in the database should not result in an implementation where you have an exact copy of this table on the mobile device. We only copy the columns and rows needed for the functionality on the mobile device. Below you see a (simplified) example of the incremental update statement for Change Tracking.

    SELECT dbo.T_ProductionHeader.ProdHeaderDossierCode
    ,      dbo.T_ProductionHeader.ProdStatusCode
    ,      dbo.T_ProductionHeader.ProdHeaderCompletedInd
    ,      dbo.T_ProductionHeader.Description
    ,      dbo.T_ProductionHeader.PartCode
    ,      dbo.T_ProductionHeader.Quantity
    ,      dbo.T_ProductionHeader.StartDate
    ,      dbo.T_ProductionHeader.EndDate
    FROM   dbo.T_ProductionHeader
    INNER  JOIN CHANGETABLE(CHANGES dbo.T_ProductionHeader, @sync_last_received_anchor) AS CT
           ON CT.ProdHeaderDossierCode = dbo.T_ProductionHeader.ProdHeaderDossierCode
    AND    CT.SYS_CHANGE_VERSION  <= @sync_new_received_anchor
    -- In filter
    AND    dbo.T_ProductionHeader.ProdHeaderCompletedInd = 0
    AND    dbo.T_ProductionHeader.ProdStatusCode BETWEEN N'20' AND N'65'

    The problem is that filtering the rows results in the situation that an update on the table should be translated in an insert or delete for Change Tracking. Take the filter above. Now assume a record is inserted with ProdStatusCode = '00'. Change Tracking signals an insert, but the filter statement causes this record not to be synchronized. If the ProdStatusCode is updated from '00' to '30', Change Tracking signals an update. This update is synchronized, but since a record is not present on the mobile device, nothing happens

    What I did is creating my own ‘Change Tracking’ tables having the same structure as the Change Tracking tables that Microsoft uses. I also added simple triggers on the filtered tables. Whenever an update causes a record to move from outside the filter to inside the filter, I record an insert for that record in my custom ‘Change Tracking’ table. When the record moves from inside the filter to outside the filter, I record a delete. When the record is deleted, I remove it from my custom CT table. Furthermore I wrote a scheduled job that retrieves the retention period from sys.change_tracking_databases and removes all recorded information from my custom CT tables that is older than this threshold. Next, I added a UNION clause using my custom CT tables to the standard incremental insert and incremental delete statements for the mobile device, adding the records that should be inserted or deleted because of an update.

    My question is, is there a more standard way of solving this problem or do I have to do all this stuff myself. I cannot imagine that we are the first to encounter this problem, so maybe more elegant and better solutions are already available?

    • Edited by Chris Sijtsma Wednesday, December 5, 2012 9:39 AM
    • Moved by Allen Li - MSFT Tuesday, December 11, 2012 1:45 AM (From:SQL Server Database Engine)
    Wednesday, December 5, 2012 9:31 AM


  • I browsed the internet for standard solutions to my problem en didn't find anything. The forum members also do not seem to know a standard solution. I therefore conclude there is no standard solution and you have to program your own customizing solution.

    • Marked as answer by Chris Sijtsma Friday, December 28, 2012 8:32 AM
    Friday, December 28, 2012 8:32 AM

All replies

  • Hi Chris,

    First, I want to confirm whether the following description is true:

    If ProdStatusCode BETWEEN N'20' AND N'65', this record will be treated as an updated record, but when insert a new record with ProdStatusCode = '00', you want to treat it as an updated record too.

    If there is any misunderstanding, please let me know. Based on the current situation, I suggest adding a new column to track whether the record is new, for example, we name it as IsNew, when inserting a new record into the table, this column is set to 1, after updating this column, we change its value to 0. In this way, if we want to get the updates records and the new records, we just need the following codition:

    ProdStatusCode BETWEEN N'20' AND N'65'

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedbackhere.

    Allen Li
    TechNet Community Support

    Thursday, December 6, 2012 3:26 AM
  • Hi Allen Li,

    I am afraid that I didn't explain the problem properly. We use the Microsoft Sync Framework and Change Tracking to synchronize data between the mobile device and the database. Change Tracking will always register an insert as an insert, an update as an update and a delete as a delete. There is nothing you can do about that. For the sync framework 8 queries are generated per table, amongst which the 'incremental insert', 'incremental update' and 'incremental delete' queries. Change tracking tracks mutations to tables that have the 'Change Tracking' property set. The mutations are numbered. The first mutation recieves the number 0 (or 1, I am not entirely sure what the initial number is). From there on, every mutation receives the last issued number + 1. This number isn't unique per table, but unique for all tables for which mutations are tracked. We call this number the 'anchor'. Whenever a mobile device is synchronized, the number of the last mutation synchronized to the mobile device is stored in the device. Let us say that one of the devices has 49 as it's anchor. The next time the device is synchronized, all mutations with a number higher than 49 are synchronized to the device.

    Our tables are very large, so we do not want to synchronize all records. In my example, we only want to synchronize records with ProdStatusCode in the range between '20' and '65'. If a records is created with ProdStatusCode = '00', Change Tracking will register the mutation as an insert, but our filter in the incremental insert statement wil filter it out, so the record will not be inserted on the mobile device. This is what we want. The problem is that when we update the ProdStatusCode to '30', for instance, Change Tracking will register this as an update. The sync framework will use it's incremental update statement to synchronize this update to the mobile device. However, updating the record on the mobile device does not achieve anything, because the record is not present on the mobile device. Adding a column 'IsNew' will not help, as far as I can see. I need a combination of an anchor number and an operation type. For instance, I need to know that mutation number 52 (the update of ProdStatusCode from '00' to '30') should cause the record to be inserted on the mobile device. I tried to explain that I managed to achieve this, but I wanted to know if there are standard solutions for Change Tracking / Sync Framework for this problem instead of my home brewn solution. It is always better to copy a working standard solution than to build something anew, if only because the already working standard solution is probably tested in practice.

    Thursday, December 6, 2012 8:35 AM
  • Microsoft Sync Framework Developer Discussions  


    Friday, December 7, 2012 8:55 AM
  • Thank you, FightAsABull. I first asked if this was the right forum, before I posted my question, but probably my first description of my problem was not clear enough. Probably this thread should be moved to the Sync Framework forum.

    Dear moderator who reads this post, could you please move the thread? I do not want to post the same question in mulitple forums.

    Friday, December 7, 2012 9:11 AM
  • I browsed the internet for standard solutions to my problem en didn't find anything. The forum members also do not seem to know a standard solution. I therefore conclude there is no standard solution and you have to program your own customizing solution.

    • Marked as answer by Chris Sijtsma Friday, December 28, 2012 8:32 AM
    Friday, December 28, 2012 8:32 AM