locked
Very Slow query 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:11 PM

Answers

All replies