-- Create a table of modified/created timestamps for this sync session since the last @sync_last_received_anchor
INSERT INTO zTimeStamps
SELECT a.[TimeStampCreated] AS TimeStampCreatedUpdated, @sync_session_id
FROM Areas a
JOIN Blocks b ON b.BlockID = a.BlockID
JOIN Estates e ON e.EstateID = b.EstateID
WHERE
e.ContractID = @ContractID AND
e.EstateID = CASE WHEN @EstateID > 0 THEN @EstateID ELSE e.EstateID END AND
(a.[TimeStampCreated] > @sync_last_received_anchor AND
a.TimeStampCreated > @sync_last_received_anchor)
UNION
SELECT a.TimeStampUpdated, @sync_session_id
FROM Areas a
JOIN Blocks b ON b.BlockID = alias.BlockID
JOIN Estates e ON e.EstateID = b.EstateID
WHERE
e.ContractID = @ContractID AND
e.EstateID = CASE WHEN @EstateID > 0 THEN @EstateID ELSE e.EstateID END AND
(a.[TimeStampCreated] > @sync_last_received_anchor AND
a.TimeStampUpdated > @sync_last_received_anchor)
-- TODO: UNION created/modified timestamps for all other sync tables (if absolutely needed)
ORDER BY TimeStampCreatedUpdated
SET @sync_max_received_anchor = (SELECT MAX(TimeStampCreatedUpdated) FROM zTimeStamps WHERE syncSessionId = @sync_session_id)
-- Determine if any gaps in the timestamp sequence to avoid empty batches
SET @sync_new_received_anchor = spGetNextBatchAnchor(@sync_last_received_anchor, @sync_batch_size, @sync_session_id)
-- Pseudo-code for spGetNextBatchAnchor
CREATE PROC spGetNextBatchAnchor
(
@sync_last_received_anchor TIMESTAMP,
@sync_batch_size INT,
@sync_session_id
)
AS
DECLARE @sync_new_received_anchor TIMESTAMP
-- Look up the calculated timestamp corresponding to @sync_last_received_anchor + @sync_batch_size (for this @sync_session_id)
-- If a timestamp exists, everything ok, return the calculated timestamp
-- If a timestamp does not exist for the calculated timestamp, find/return the next highest timestamp in the table of available TimeStamps for this client
RETURN @sync_new_received_anchor
Above is the general approach that I´m planning on taking to get around the issue with empty batches.
1. Build a table (zTimestamps) containing all modified and created timestamps since @sync_last_received_anchor. This is quite an expensive operation and I have a couple of questions here: is it necessary to build a list of timestamps for *all* tables in all sync groups, or is it possible to pass in the name of the table or sync group that is currently being sync´d so that the list of timestamps is relevant for just the table about to be sync´d? I´d like the bit of code which builds the timestamp table to run just once for each sync, so I would check the @sync_session_id to see if the table exists or not.
2. Instead of simply adding the batch size to the @sync_last_received_anchor to obtain the value for @sync_new_received_anchor and perhaps returning an anchor which would contain no data (and an empty batch), instead I would look up the anchor in the zTimestamps table to see if data with this timestamp exists. If there is no record containing this timestamp, return the next highest timestamp for this client so that the next batch will contain some data.
Thoughts with this approach? A bit of a hack, but the only way I can think of getting around the issue with empty batches.
