locked
Initialization times out RRS feed

  • Question

  • Hi,

    When doing the first initialization from a mobile device  using SQLCe i'm getting always a timeout.

    After tracing the server i found the query that is taking more than 3 minutes.

     

    declare
     @sync_last_received_anchor as
     bigint
    
    declare
     @sync_client_id_binary as
     binary
    
    
    SET
     @sync_client_id_binary=0xE8CCDFEAA5EAAD439F231F6AC2DB936B
    SET
     @sync_last_received_anchor=0
    
    SELECT
     [T_DIST_Service].[ServiceID], [ServiceCode], [ClientID], [ServiceTypeID], [InvoicePlanID], [Notes], [ClientBarCode], [ReceiverCode], 
    [ReceiverName], [ReceiverAddress], [ReceiverPlace], [ReceiverPostalCode], [ReceiverPostalPlace], [ReceiverContact], [Weight], [VolumesQty], [Amount],
    [ShipFromCode], [ShipFromName], [ShipFromAddress], [ShipFromPlace], [ShipFromPostalCode], [ShipFromPostalPlace], [ShipFromContact], [CollectScheduleDate],
    [DeliverScheduleDate], [RelatedServiceCode] FROM [T_DIST_Service] LEFT OUTER JOIN CHANGETABLE(CHANGES [T_DIST_Service], @sync_last_received_anchor) CT ON CT.[ServiceID] = [T_DIST_Service].[ServiceID] WHERE (T_DIST_Service.ServiceID IN ( SELECT S.ServiceID FROM T_DIST_Service S LEFT JOIN T_DIST_ServiceCurrentState SCT ON S.ServiceID = SCT.ServiceID WHERE S.ChangeDate >= DATEADD (DAY , -3,GETDATE ()) AND SCT.ServiceState!='DONE' AND SCT.DeliverDate IS NULL )) AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)
    This query return 1300 records and if i execute it without joining the CHANGETABLE only takes 2 seconds !

     

     

    Help ! :)

    Thanks

    Thursday, November 4, 2010 10:05 PM

All replies

  • since you have the standalone repro with this query, can you run it and capture the query execution plan ?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, November 5, 2010 2:31 PM
  • also, how many total rows do you have on the table (without filtering) and what is your change tracking retention settings?
    Friday, November 5, 2010 3:12 PM
  • also, how many total rows do you have on the table (without filtering) and what is your change tracking retention settings?

    93915 total records

    retention period = 5 days

     

    Execution Plan : https://www.transferbigfiles.com/5546bd2a-431a-43e1-92ba-5e19a88acc35?rid=kmPbMu77haVTPPEpBJ8o%2fw%3d%3d

     

    I don't understand why the adapter generates the query using LEFT  OUTER  JOIN  with the CHANGETABLE if is the initialization.

     

    Full query :

    exec sp_executesql N'
    IF @sync_initialized = 0 
    	SELECT [T_DIST_Service].[ServiceID], [ServiceCode], [ClientID], [ServiceTypeID], [InvoicePlanID], [Notes], [ClientBarCode], [ReceiverCode], [ReceiverName], [ReceiverAddress], [ReceiverPlace], [ReceiverPostalCode], [ReceiverPostalPlace], [ReceiverContact], [Weight], [VolumesQty], [Amount], [ShipFromCode], [ShipFromName], [ShipFromAddress], [ShipFromPlace], [ShipFromPostalCode], [ShipFromPostalPlace], [ShipFromContact], [CollectScheduleDate], [DeliverScheduleDate], [RelatedServiceCode] FROM [T_DIST_Service] LEFT OUTER JOIN CHANGETABLE(CHANGES [T_DIST_Service], @sync_last_received_anchor) CT ON CT.[ServiceID] = [T_DIST_Service].[ServiceID] WHERE (T_DIST_Service.ServiceID IN ( SELECT S.ServiceID FROM T_DIST_Service S LEFT JOIN T_DIST_ServiceCurrentState SCT ON S.ServiceID = SCT.ServiceID WHERE S.ChangeDate >= DATEADD(DAY, -3,GETDATE()) AND SCT.ServiceState!=''DONE'' AND SCT.DeliverDate IS NULL)) AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) 
    ELSE BEGIN SELECT [T_DIST_Service].[ServiceID], [ServiceCode], [ClientID], [ServiceTypeID], [InvoicePlanID], [Notes], [ClientBarCode], [ReceiverCode], [ReceiverName], [ReceiverAddress], [ReceiverPlace], [ReceiverPostalCode], [ReceiverPostalPlace], [ReceiverContact], [Weight], [VolumesQty], [Amount], [ShipFromCode], [ShipFromName], [ShipFromAddress], [ShipFromPlace], [ShipFromPostalCode], [ShipFromPostalPlace], [ShipFromContact], [CollectScheduleDate], [DeliverScheduleDate], [RelatedServiceCode] FROM [T_DIST_Service] JOIN CHANGETABLE(CHANGES [T_DIST_Service], @sync_last_received_anchor) CT ON CT.[ServiceID] = [T_DIST_Service].[ServiceID] WHERE (T_DIST_Service.ServiceID IN ( SELECT S.ServiceID FROM T_DIST_Service S LEFT JOIN T_DIST_ServiceCurrentState SCT ON S.ServiceID = SCT.ServiceID WHERE S.ChangeDate >= DATEADD(DAY, -3,GETDATE()) AND SCT.ServiceState!=''DONE'' AND SCT.DeliverDate IS NULL)) AND (CT.SYS_CHANGE_OPERATION = ''I'' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); 
    	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N''[T_DIST_Service]'')) > @sync_last_received_anchor RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'',16,3,N''[T_DIST_Service]'') END ',N'@sync_initialized bit,@sync_last_received_anchor bigint,@sync_client_id_binary varbinary(16),@sync_new_received_anchor bigint',@sync_initialized=0,@sync_last_received_anchor=0,@sync_client_id_binary=0xE8CCDFEAA5EAAD439F231F6AC2DB936B,@sync_new_received_anchor=643579
    
    

    Friday, November 5, 2010 4:19 PM
  • in such case, this is a known issue with the query built from the sync adapter. please modify the query to not use the complex joins for the initial sync. this post http://social.microsoft.com/Forums/en/uklaunch2007ado.net/thread/9766c28c-00db-4f2b-9416-99678c15f86c has the details of this limitation.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, November 5, 2010 5:09 PM
  • This resolved my problem:

    http://support.microsoft.com/kb/2276330

     

    • Marked as answer by João Passos Tuesday, November 9, 2010 12:40 AM
    • Unmarked as answer by João Passos Saturday, November 13, 2010 5:21 PM
    Tuesday, November 9, 2010 12:40 AM
  • This resolved my problem:

    http://support.microsoft.com/kb/2276330

     


    Apparently didn't solve the problem.

    After 1 week the problem came back again.

    The same query that took only a few seconds is now taking more than 1 minutes to return 950 records in a universe of 100091 records.

    If i decrease the retention period to 2 days and then set it back again to 4 the performance improves.

     

    What can this be ?

    Thanks in advance.

    Saturday, November 13, 2010 5:26 PM