Jackrabbit OAK-8977 (Improve performance)

/*******************************************************************
 * Script:  JackRabbitOAK8977.sql ( https://pastebin.com/csh4fy42 )
 * By:      Solomon Rutzky ( https://SqlQuantumLeap.com/ )
 * On:      2020-03-29
 * Issue:   RDBBlobStore performance improvement for SQL Server (MSSQL)
 *          https://issues.apache.org/jira/browse/OAK-8977
 * Purpose: Prove best combination is binary collation and stored proc.
 *******************************************************************/

GO
-- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
SET PARSEONLY ON;
GO

-- add RDBBlobStore performance test ( https://issues.apache.org/jira/browse/OAK-8926 )
-- http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-store-document/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBBlobStoreDB.java?revision=1874271&view=markup



--------------------------------------------------------------------------------
--- BEGIN SETUP
--- Highlight down to "END SETUP" and execute
--------------------------------------------------------------------------------



USE [tempdb];

---------------------------------------------------------
-- DROP TABLE dbo.[DATASTORE_DATA];
-- DROP TABLE dbo.[DATASTORE_META];

CREATE TABLE dbo.[DATASTORE_DATA]
(
    [ID] VARCHAR(64) COLLATE Latin1_General_CI_AS NOT NULL, -- Scenario = 1
    [DATA] VARBINARY(MAX),
    CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE dbo.[DATASTORE_META]
(
    [ID] VARCHAR(64) COLLATE Latin1_General_CI_AS NOT NULL, -- Scenario = 1
    [LVL] INT,
    [LASTMOD] BIGINT,
    CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
);
---------------------------------------------------------
GO
IF (OBJECT_ID(N'dbo.StoreBlockInDatabase') IS NULL)
BEGIN
    EXEC(N'CREATE PROCEDURE dbo.[StoreBlockInDatabase] AS SET NOCOUNT ON;');
END;

GO
ALTER PROCEDURE dbo.[StoreBlockInDatabase]
(
    @Digest VARBINARY(32),
    @Level INT,
    @Data VARBINARY(MAX)
)
AS
SET NOCOUNT ON;

DECLARE @Id VARCHAR(64) = CONVERT(VARCHAR(64), @Digest, 2),
        @Now BIGINT;

SELECT @Now = [cpu_ticks]
FROM   sys.dm_os_sys_info;

BEGIN TRY
    UPDATE dbo.[DATASTORE_META]
    SET    [LASTMOD] = @Now
    WHERE  [ID] = @Id;

    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@Id, @Data);
        INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@Id, @Level, @Now);
    END;
END TRY
BEGIN CATCH
    ;THROW;
END CATCH;

GO
---------------------------------------------------------
GO
IF (OBJECT_ID(N'dbo.StoreBlockInDatabase2') IS NULL)
BEGIN
    EXEC(N'CREATE PROCEDURE dbo.[StoreBlockInDatabase2] AS SET NOCOUNT ON;');
END;

GO
ALTER PROCEDURE dbo.[StoreBlockInDatabase2]
(
    @Digest VARBINARY(32),
    @Level INT,
    @Data VARBINARY(MAX)
)
AS
SET NOCOUNT ON;

DECLARE @Id VARCHAR(64) = CONVERT(VARCHAR(64), @Digest, 2),
        @Now BIGINT;

SELECT @Now = [cpu_ticks]
FROM   sys.dm_os_sys_info;

BEGIN TRY
    IF (EXISTS(
            SELECT *
            FROM   dbo.[DATASTORE_META]
            WHERE  [ID] = @Id
            ))
    BEGIN
        UPDATE dbo.[DATASTORE_META]
        SET    [LASTMOD] = @Now
        WHERE  [ID] = @Id;
    END;
    ELSE
    BEGIN
        INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@Id, @Data);
        INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@Id, @Level, @Now);
    END;
END TRY
BEGIN CATCH
    ;THROW;
END CATCH;

GO
---------------------------------------------------------
GO
IF (OBJECT_ID(N'tempdb..#RunTests') IS NULL)
BEGIN
    EXEC(N'CREATE PROCEDURE #RunTests AS SET NOCOUNT ON;');
END;

GO
ALTER PROCEDURE #RunTests
(
    @Iterations TINYINT = 10,
    @BlockSize INT = 1500,
    @DurationMS INT = 2000,
    @SingleTestPattern TINYINT = NULL
)
AS
SET NOCOUNT ON;

IF (OBJECT_ID(N'dbo.[TestResults]') IS NULL)
BEGIN
    -- DROP TABLE dbo.[TestResults];
    CREATE TABLE dbo.[TestResults]
    (
        [TestResultsID] INT NOT NULL
            IDENTITY(1, 1)
            CONSTRAINT [PK_TestResults] PRIMARY KEY,
        [TestTime] DATETIME2 NOT NULL
            CONSTRAINT [DF_TestResults_TestTime] DEFAULT (SYSDATETIME()),
        [Scenario] TINYINT NOT NULL,
        [TestPattern] TINYINT NOT NULL,
        [RowsInserted] INT NOT NULL,
        [TotalRows] INT NOT NULL
    );
END;


DECLARE @TestPattern TINYINT = 1;

DECLARE @Iteration TINYINT = 0,
        @RowCount INT = 0,
        @EndTime DATETIME2,
        @DATA VARBINARY(MAX),
        @Digest BINARY(32),
        @ID VARCHAR(64),
        @SQL NVARCHAR(MAX),
        @Scenario TINYINT;

-- NOTE: Does not yet handle scenario 3!!
SELECT @Scenario = CASE col.[collation_name]
                    WHEN N'Latin1_General_CI_AS' THEN 1
                    WHEN N'Latin1_General_BIN2' THEN 2
                    ELSE 0
                END
FROM  sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'dbo.DATASTORE_META')
AND   col.[name] = N'ID';


WHILE (@TestPattern < 7)
BEGIN
    SET @Iteration = 0;

    -- Execute the following 2 statements when changing either TestPattern or Scenario (for consistency):
    TRUNCATE TABLE dbo.[DATASTORE_DATA];
    TRUNCATE TABLE dbo.[DATASTORE_META];

    WHILE (@Iteration < @Iterations)
    BEGIN
        SET @RowCount = 0;
        SET @EndTime = DATEADD(MILLISECOND, @DurationMS, SYSDATETIME());

        WHILE (SYSDATETIME() < @EndTime)
        BEGIN
            SET @DATA = CRYPT_GEN_RANDOM(@BlockSize);

            SET @Digest = HASHBYTES('SHA2_256', @DATA);

            SET @ID = CONVERT(VARCHAR(64), @Digest, 2);


            BEGIN TRAN; -- I think the JDBC code is doing this

            IF (@TestPattern = 1)
            BEGIN
                UPDATE dbo.[DATASTORE_META]
                SET    [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
                WHERE  [ID] = @ID;

                IF (@@ROWCOUNT = 0)
                BEGIN
                    INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@ID, @DATA);
                    INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@ID, 0, CONVERT(INT, GETDATE()));
                END;
            END;


            IF (@TestPattern = 2)
            BEGIN
                IF (EXISTS(
                        SELECT *
                        FROM   dbo.[DATASTORE_META]
                        WHERE  [ID] = @ID
                        ))
                BEGIN
                    UPDATE dbo.[DATASTORE_META]
                    SET    [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
                    WHERE  [ID] = @ID;
                END;
                ELSE
                BEGIN
                    INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@ID, @DATA);
                    INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@ID, 0, CONVERT(INT, GETDATE()));
                END;
            END;


            IF (@TestPattern = 3)
            BEGIN
                SET @SQL = N'
                UPDATE dbo.[DATASTORE_META]
                SET    [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
                WHERE  [ID] = ''' + @ID + N''';

                IF (@@ROWCOUNT = 0)
                BEGIN
                    INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (''' + @ID + N''', ' + CONVERT(NVARCHAR(MAX), @DATA, 1) + N');
                    INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (''' + @ID + N''', 0, CONVERT(INT, GETDATE()));
                END;
                ';

                EXEC(@SQL);
            END;


            IF (@TestPattern = 4)
            BEGIN

                SET @SQL = N'EXEC dbo.[StoreBlockInDatabase] 0x' + @ID + N', 0, ' + CONVERT(NVARCHAR(MAX), @DATA, 1) + N';';

                EXEC(@SQL);
            END;


            IF (@TestPattern = 5)
            BEGIN
                EXEC sp_executesql
                    N'EXEC dbo.[StoreBlockInDatabase] @tmpDigest, 0, @tmpData;',
                    N'@tmpDigest VARBINARY(32), @tmpData VARBINARY(MAX)',
                    @tmpDigest = @Digest,
                    @tmpData = @DATA;
            END;


            IF (@TestPattern = 6)
            BEGIN
                EXEC sp_executesql
                    N'EXEC dbo.[StoreBlockInDatabase2] @tmpDigest, 0, @tmpData;',
                    N'@tmpDigest VARBINARY(32), @tmpData VARBINARY(MAX)',
                    @tmpDigest = @Digest,
                    @tmpData = @DATA;
            END;


            COMMIT TRAN; -- I think the JDBC code is doing this

            SET @RowCount += 1;
        END;

        INSERT INTO dbo.[TestResults] ([Scenario], [TestPattern], [RowsInserted], [TotalRows])
        OUTPUT INSERTED.*
            SELECT @Scenario, @TestPattern, @RowCount, COUNT(*)
            FROM   dbo.[DATASTORE_DATA];

        SET @Iteration += 1;

        WAITFOR DELAY '00:00:00.500'; -- half-second delay
    END;

    SET @TestPattern += 1;
END;
GO

--------------------------------------------------------------------------------
--- END SETUP
--------------------------------------------------------------------------------


-- Exec test proc, apply Scenario 2 changes, then exec test proc again:

EXEC #RunTests;


----------------------------------------
-- Scenario = 2 (change collation to binary)

ALTER TABLE [DATASTORE_DATA] DROP CONSTRAINT [DATASTORE_DATA_PK];
ALTER TABLE [DATASTORE_DATA] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_DATA] ADD CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY CLUSTERED ([ID] ASC);

ALTER TABLE [DATASTORE_META] DROP CONSTRAINT [DATASTORE_META_PK];
ALTER TABLE [DATASTORE_META] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_META] ADD CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY CLUSTERED ([ID] ASC);
----------------------------------------

EXEC #RunTests;



/* -- Highlight and execute as necessary:

SELECT * FROM dbo.[DATASTORE_DATA];
SELECT * FROM dbo.[DATASTORE_META];

DELETE FROM dbo.[TestResults] WHERE [TestResultsID] = xyz;
SELECT * FROM dbo.[TestResults];
*/



-- TestPattern 3 = close approximation of current code
-- TestPattern 5 = proposed stored procedure approach
SELECT [Scenario], [TestPattern],
    COUNT([RowsInserted]) AS [TestCount],
    MIN([RowsInserted]) AS [MinRows],
    AVG([RowsInserted]) AS [AvgRows],
    MAX([RowsInserted]) AS [MaxRows],
    SUM([RowsInserted]) AS [TotalRows]
FROM dbo.[TestResults]
GROUP BY [Scenario], [TestPattern]
ORDER BY [Scenario], [TestPattern];
/*
Scenario   TestPattern   TestCount   MinRows   AvgRows   MaxRows   TotalRows
1          1             10          11605     13329     14583     133297
1          2             10          11409     14522     16547     145221
1          3             10           1681      2108      2322      21080

2          1             10          12755     16064     17108     160640
2          2             10          13959     15312     16927     153125
2          3             10           2144      2422      2696      24228
2          4             10           2238      3012      3802      30121
2          5             10            963      4876      6626      48768
2          6             10            907      4696      6532      46969
*/



----------------------------------------
-- Scenario = 3 (keep binary collation, change PK to NONclustered)

ALTER TABLE [DATASTORE_DATA] DROP CONSTRAINT [DATASTORE_DATA_PK];
ALTER TABLE [DATASTORE_DATA] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_DATA] ADD CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC);

ALTER TABLE [DATASTORE_META] DROP CONSTRAINT [DATASTORE_META_PK];
ALTER TABLE [DATASTORE_META] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_META] ADD CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC);
----------------------------------------

GO
-- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
SET PARSEONLY OFF;
GO