1

Context

I have created a stored procedure that simulates a merge command for an OLTP in-memory table. It used the simulated merge for upsert moving massive data form a normal table to an OLTP one.

Procedure

This is the code:

CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS   
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1; 
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR  
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;  

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor; 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;

        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;  

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0  
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status);  
    END
      
    CLOSE Employee_Cursor;  
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END

The cursor upserts a range of rows like this because OLTP tables (target) do not support MERGE commands.

Problem

If I execute my SP I will get a result the first cursor selected FETCH and a list of single row counts.

table result

message

Question

I understand that this is normal behavior, but I try to suppress this to get a simpler result.

What I want:

  1. No table result from CURSOR select
  2. A sum of affected row count as single number of all update/insert operations

The empty FETCH was indeed an error and solves the ROW result problem (point 1).

Looks like this now:

CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS   
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1; 
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR  
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;  

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;  

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0  
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status); 

        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    END
      
    CLOSE Employee_Cursor;  
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END
4

Instead of using a cursor*, write the update and insert operations explicitly as set-based operations:

CREATE OR ALTER PROCEDURE cache.MoveInverterData
(
    @sourceInverterID bigint, 
    @from datetime2(7)
)
AS   
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    DECLARE @RowsAffected integer = 0;

    -- TODO: Add error handling

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

        -- Update existing rows
        UPDATE CID
        SET [Status] = DID.[Status]
        FROM [data].InverterData AS DID
        JOIN cache.InverterData AS CID
                WITH (SNAPSHOT)
            ON CID.InverterID = DID.InverterID
            AND CID.[Timestamp] = DID.[Timestamp]
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from;

        SET @RowsAffected += @@ROWCOUNT;

        -- Insert new rows
        INSERT cache.InverterData
            (InverterID, [Timestamp], [Status])
        SELECT
            DID.InverterID,
            DID.[Timestamp],
            DID.[Status]
        FROM [data].InverterData AS DID
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from
            AND NOT EXISTS
            (
                SELECT 1
                FROM cache.InverterData AS CID
                    WITH (SNAPSHOT)
                WHERE
                    CID.InverterID = DID.InverterID
                    AND CID.[Timestamp] = DID.[Timestamp]
            );

        SET @RowsAffected += @@ROWCOUNT;

    COMMIT TRANSACTION;

    -- TODO: Use the @RowsAffected result
END;

Ensure both tables have a (non-hash) index on InverterID, [Timestamp].

* Unlike some other database engines, cursors are usually quite inefficient in SQL Server. The overhead and per-row operations almost always make a set-based solution faster and more efficient.

| improve this answer | |
3

Alternatively, if you wish to use a natively-compiled stored procedure as much as possible, create a memory-optimized table type to hold the new data:

CREATE TYPE dbo.InverterData AS TABLE
(
    RowID integer IDENTITY (1, 1) NOT NULL,
    InverterID  bigint NOT NULL,
    [Timestamp] datetime2(7) NOT NULL,
    [Status] nvarchar(50) NOT NULL,

    PRIMARY KEY NONCLUSTERED HASH (RowID)
        WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);

Then write a natively-compiled procedure to merge the new data:

CREATE PROCEDURE cache.MoveInverterData_Native
    @InverterData dbo.InverterData READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

    DECLARE @RowID integer = 1; 
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    WHILE @RowID > 0
    BEGIN
        SELECT
            @InverterID = ID.InverterID,
            @Timestamp = ID.[Timestamp],
            @Status = ID.[Status]
        FROM @InverterData AS ID
        WHERE ID.RowID = @RowID;

        IF @@ROWCOUNT = 0
        BEGIN
            SET @RowID = 0
        END
        ELSE
        BEGIN
            UPDATE cache.InverterData
            SET [Status] = @Status
            WHERE InverterID = @InverterID
            AND [Timestamp] = @Timestamp;

            IF @@ROWCOUNT = 0
                INSERT cache.InverterData
                    (InverterID, [Timestamp], [Status])
                VALUES
                    (@InverterID, @Timestamp, @Status);

            SET @RowID += 1;
        END;
    END;
END;

And a wrapper procedure to populate the memory-optimized table type from the source table:

CREATE OR ALTER PROCEDURE cache.MoveInverterData
(
    @sourceInverterID bigint, 
    @from datetime2(7)
)
AS   
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    DECLARE @InverterData dbo.InverterData;

    INSERT @InverterData
        (InverterID, [Timestamp], [Status])
    SELECT
        ID.InverterID,
        ID.[Timestamp],
        ID.[Status]
    FROM [data].InverterData AS ID
    WHERE
        ID.InverterID = @sourceInverterID
        AND ID.[Timestamp] >= @from;

    EXECUTE cache.MoveInverterData_Native @InverterData;
END;

The whole process is then invoked by a single call like:

EXECUTE cache.MoveInverterData
    @sourceInverterID = 1,
    @from = '20180601';
| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.