1
\$\begingroup\$

I want to sync the table every few minutes to a remote database. I want it to be as efficient as possible. I wrote this stored procedure that will only return rows that are 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
\$\endgroup\$
  • \$\begingroup\$ Have you read the sql tag info? "1) Provide context, 2) Include the schema, 3) If asking about performance, include indexes and the output of EXPLAIN SELECT." \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Jan 11 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.