Krdytkyu

I want to sync the table every few minutes to remote database, I want it to as efficient as possible. So I write this stored procedure that will only return rows that is added or removed after the last query.

Basically, this stored procedure will return something like this:

190107080001 | REMOVED | NULL   | NULL  | NULL | NULL        | NULL |

190107080005 | ADDED   | 225545 | Name2 | 5    | Specialist2 | 26   |

190107080108 | ADDED   | 230139 | Name3 | 8    | Specialist3 | 44   |

Note: the [key] is actually simple hashed value from the row itself, the remote database will use the [key] as Identity,

Actually I do not have problem with this code until now, but I think my implementation is not effective enough considering I will execute this query 24/7.

CREATE PROCEDURE [dbo].[GetMonthlySchedules] 

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    ----------------------------------------------------------------

    -- Select new record and save it to temporary table

    ----------------------------------------------------------------

    SELECT [key]

         , id

         , therapist

         , therapistId

         , specialist

         , specialistId



      INTO #Temp_MonthSchedule



      FROM doc 

INNER JOIN spe ON doc.specialistId = spe.Id 

INNER JOIN lis ON doc.Id = lis.DocId



     WHERE lis.Dt >= DATEADD(dd, 1, DATEDIFF(dd, 0,  GETDATE()))

       AND lis.Dt <= DATEADD(mm, 1, DATEDIFF(dd, 0,  GETDATE()))



    ----------------------------------------------------------------

    -- Select the different between old and new records to return it later

    ----------------------------------------------------------------

    SELECT ISNULL(old.[key], new.[key]) AS [key]

        , CASE WHEN old.Id IS NULL THEN 'ADDED' WHEN new.Id IS NULL THEN 'REMOVED' END AS operation

        , new.id, new.therapist, new.therapistId, new.specialist, new.specialistId



      INTO #Temp_MonthScheduleDiff



      FROM ComparerMonthSchedule AS old

FULL OUTER JOIN #Temp_MonthSchedule AS new

        ON old.[key] = new.[key]



     WHERE old.id IS NULL

        OR new.id IS NULL



    ----------------------------------------------------------------

    -- Replace old record with new record

    ----------------------------------------------------------------



    DELETE FROM ComparerMonthSchedule

    INSERT INTO ComparerMonthSchedule SELECT * FROM #Android_Temp_MonthSchedule



    ----------------------------------------------------------------

    -- Return records

    ----------------------------------------------------------------



    SELECT * FROM #Temp_MonthScheduleDiff



    ----------------------------------------------------------------

    -- Delete temporal table

    ----------------------------------------------------------------



    IF OBJECT_ID('tempdb..#Temp_TodaySchedule') IS NOT NULL BEGIN DROP TABLE #Temp_TodaySchedule END;

    IF OBJECT_ID('tempdb..#Temp_TodayScheduleDiff') IS NOT NULL BEGIN DROP TABLE #Temp_TodayScheduleDiff END;

END