locked
Problem with selectchanges on a large table RRS feed

  • Question

  • It has been a while since I needed to ask a question on these forums, but that time has come again.

    The short version:

    I have a table with 3.7 million records in it. When I try to call the _selectchanges stored procedure for this table, I am getting a DbSyncException stating:

    Cannot enumerate changes at the RelationalSyncProvider for table "XXX"

    The inner exception is a SqlException with the message:

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    This is occurring on MSF 2.0 running on .NET 4.0. The database is SQL Server 2008 R2. The "_selectchanges" method uses row level filtering. The failure is not solid; sometimes it succeeds, but it fails most of the time.

    Our configuration uses a sync service that the client application talks to via a proxy over WCF (nothing unusual about that). The error is being thrown in the service, not the client.

    The longer version

    This particular table has the most rows of any table in our database and is pretty crucial to our service. The table is row level filtered and, of the 3.7 million records, about 77,000 would be selected for a specific site. I have added a couple of indexes to XXX_tracking to improve performance in a couple of other queries (and that worked). I have pulled the query out of the stored procedure and it typically takes 25 seconds to complete.

    I have looked at the execution plan for the query (that gave me the tip about adding an couple of indexes to the tracking table), but it doesn’t suggest anything. I have also tried setting the connection timeout for the database connection to 60 seconds (the default is 15 seconds) and that didn’t help (I didn’t really expect it to).

    Is there a way to adjust the timeout value on the DbSyncProvider/SqlConnection? Is there something else I can do or should look at to get this to work? This table is only going to get larger. I don’t know if pruning older records from the table is a viable option.

    As I mentioned earlier, we are using the 2.0 version of the sync framework. We put a lot of effort into making this work on the 2.0 framework, so I would be reluctant to upgrade right now unless I absolutely had to do so.

    Thursday, December 2, 2010 6:17 PM

Answers

All replies

  • lengthen the timeout time on the remote sync provider ie. .CommandTimeout = x; where x is a value large enough to ensure your database can select through 3.7 mil rows
    Thursday, December 2, 2010 8:03 PM
  • if am not mistaken, the CommandTimeout property was only made available in v2.1 so you may have to upgrade.
    • Proposed as answer by Ganeshan Thursday, December 9, 2010 5:56 PM
    Friday, December 3, 2010 8:44 AM
  • Upgrading to 2.1 is not the answer I wanted to hear, but I somewhat expected to hear. Before I commit to that path, here are a few questions. Note that as I have been looking over MSF 2.1 to see what it offers, I have updated my list of questions.

    • How compatible are the interfaces? What am I likely to have to rewrite if I upgrade? Can I simply "plug 2.1" into my existing application and it will work?
    • Does 2.1 solve the problem of progress notification when data is batched? In 2.0, the sync progress event was messed up if the data was batched (it lost track of the total number of records being synced).
    • Are there any known issues that I need to be aware of?
    • What changed on the metadata? I assume this is changes to the "tracking" table (possibly to remove columns that I've never seen used).
    • The ability to apply changes in batch transactions (as opposed to a stored procedure call per update) looks particularly appealing. Will we be able to use this if our central database is SQL Server 2005 and our client database is SQL Server 2008 R2? The central database is owned and maintained by the company and we don't have a lot control over what version of SQL Server they run. The client database is administered by us, so we can control that upgrade.

    If there are URLs to the answers to these questions, that would sufficient. I've tried looking into the MSF 2.1 documentation, but I find I get lost pretty quickly. I am actually starting to get a little excited about this; we are at a perfect point in our development effort to upgrade, but I would like to be confident that the upgrade can be done quickly and painlessly, and that we are going to derive real benefit from doing it.

    I have one other question that has nothing to do with framework versions, but I need ask it anyway. When records are being synced for table XXX that will result in a record insertion, what is the call sequence? Does the sync framework call "XXX_insert" and then "XXX_insertmetadata" or "XXX_insertmetadata" and then "XXX_insert"? Are both calls done in the same transaction?

    There may be a way to work around this issue by adding an extra column to my "tracking" table. But since MSF owns the "insertmetadata" interface, I would have to look up the parameter inside my "insertmetadata" stored procedure.

    I really appreciate your help and the fact that you respond quickly with helpful information.

    Friday, December 3, 2010 1:00 PM
  • Compatibility  - check http://msdn.microsoft.com/en-us/library/dd936971(v=SQL.110).aspx or check out the What's New section as well as the Release Notes file.

    Metadata - there's a change in the metadata format, see http://msdn.microsoft.com/en-us/library/ff928564(v=SQL.110).aspx for upgrade options

    Bulk Update - use UseBulkProcedures property during provisioning to set whether bulk update procedures should be created.

    Insert Proc - xxx_insert is called first before xxx_insertmetadata

    Friday, December 3, 2010 5:05 PM
  • What class has the CommandTimeout property? I am attempting to upgrade to MSF 2.1, but I cannot find the class that allegedly has this property.

    I found this property on SqlSyncProvider, but I am using DbSyncProvider.  Both DbSyncProvider and SqlSyncProvider inherit from RelationalSyncProvider, but they are not the same nor are they compatible.  So, is there is another class I should be using?

    Update:

    At this particular point, I am not going to upgrade to MSF 2.1. I can get the code to compile, but I cannot get it to run (missing SelectMetadataForCleanupCommand() which I have not seen mentioned anywhere; who knows what else is missing.). So far, I have managed to work around every limitation I have encountered (and they are numerous) on MSF 2.0. I am not going to repeat this exercise on MSF 2.1.

    It turns out that the CommandTimeout property is a property of the SqlCommand class and is exposed by the IDbCommand interface. I should be able to set it there in my framework. If that doesn't work, I own the tracking table for the table that is giving me trouble, so I can add whatever columns and indexes I need to get it to perform to my satisfaction without having to wade through an upgrade to another version that offers me no benefit.

    Monday, December 6, 2010 4:43 PM
  • if you're using DbSyncProvider, i'm assuming you have a custom provider? if so, then yes, you can just set it directly on the SqlCommand.

    Tuesday, December 7, 2010 3:46 PM
  • hi,

    I got the similar error which is resolved by simply increased the timoutout to 120 sec in the connstr() [i.e. append ";Connection Timeout=120"]. not sure why it does work in your case.


    more details

    my application is using MFx v2.0 with batching. the error happened on

    sqlSyncProvider.GetChangeBatch(batchSize, destinationKnowledge, out changesWrapper.DataRetriever);

     

    Microsoft.Synchronization.Data.RelationalSyncProvider.EnumerateChangesInBatchesInternal(Object batchProducer)

     

    ERROR  , WebDev.WebServer, 4, 12/22/2010 21:44:44:177,       RelationalSyncProvider.BatchedEnum: Background thread had an unexpected exception. Queue it.

    ERROR  , WebDev.WebServer, 4, 12/22/2010 21:44:44:193,       SyncBatchProducer: Background enumeration encountered an error.

    ERROR  , WebDev.WebServer, 10, 12/22/2010 21:44:44:210, Caught exception while getting changes: Microsoft.Synchronization.Data.DbSyncException: Cannot enumerate changes at the RelationalSyncProvider for table 'xxxxxxxxx'. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    Users MedalsUsers MedalsUsers Medals

     

    --just my  2 cents



    --alex
    Wednesday, December 22, 2010 10:54 PM
  • On 2.0, you may get the SqlException for command timeout. This is fixed in Sync Framework 2.1.  In 2.1, you can set the timeout value on SqlSyncProvider like this : http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.sqlserver.sqlsyncprovider.commandtimeout(v=SQL.110).aspx 

    Thursday, December 23, 2010 12:56 AM
    Answerer