-- This script pertains to the following Stack Overflow question:
-- http://stackoverflow.com/questions/30330035/performance-of-varbinary-comparison-in-t-sql
-------------------------------------------------------------------------------------------------------------
-- RUN THE FOLLOWING SETUP ONCE:
-------------------------------------------------------------------------------------
DECLARE @Pattern VARBINARY(19),
@SetsOf1000TestRows INT;
SET @SetsOf1000TestRows = 50;
SET @Pattern = CONVERT(VARBINARY(19), CONVERT(VARCHAR(32), CONVERT(BINARY(16), NEWID()), 2) + '0A0B0C', 2);
SET CONTEXT_INFO @Pattern;
SET NOCOUNT ON;
IF (OBJECT_ID(N'tempdb..#BinaryStuff') IS NOT NULL)
BEGIN
DROP TABLE #BinaryStuff;
END;
IF (OBJECT_ID(N'tempdb..#BinaryStuffWithPCC') IS NOT NULL)
BEGIN
DROP TABLE #BinaryStuffWithPCC;
END;
IF (OBJECT_ID(N'tempdb..#BinaryStuffMax') IS NOT NULL)
BEGIN
DROP TABLE #BinaryStuffMax;
END;
IF (OBJECT_ID(N'tempdb..#BinaryStuffMaxWithPCC') IS NOT NULL)
BEGIN
DROP TABLE #BinaryStuffMaxWithPCC;
END;
CREATE TABLE #BinaryStuff(ID INT IDENTITY(1, 1) PRIMARY KEY,
Payload VARBINARY(100) NULL
);
;WITH TestSets AS
(
SELECT TOP (@SetsOf1000TestRows) sac.[column_id] AS [Repeats]
FROM [master].sys.all_columns sac
)
INSERT INTO #BinaryStuff (Payload)
SELECT tmp.Payload
FROM TestSets ts
CROSS APPLY (
SELECT TOP (999)
CONVERT(VARBINARY(100),
CONVERT(VARCHAR(100), NEWID()) + CONVERT(VARCHAR(100), COALESCE(sac.collation_name, 'bob') + sac.[name])
) AS [Payload]
FROM [master].sys.all_columns sac
UNION ALL
SELECT CONVERT(VARBINARY(100),
CONVERT(VARCHAR(50), @Pattern, 2) + REPLICATE(SUBSTRING(CONVERT(VARCHAR(36), NEWID()), 1, 8), ts.[Repeats]), 2
)
) tmp
---
CREATE TABLE #BinaryStuffWithPCC(ID INT PRIMARY KEY,
Payload VARBINARY(100) NULL,
[Prefix] AS SUBSTRING([Payload], 1, 19) PERSISTED);
INSERT INTO #BinaryStuffWithPCC (ID, Payload)
SELECT tmp.ID, tmp.Payload
FROM #BinaryStuff tmp;
---
CREATE TABLE #BinaryStuffMax(ID INT PRIMARY KEY,
Payload VARBINARY(MAX) NULL
);
INSERT INTO #BinaryStuffMax (ID, Payload)
SELECT tmp.ID, tmp.Payload
FROM #BinaryStuff tmp;
---
CREATE TABLE #BinaryStuffMaxWithPCC(ID INT PRIMARY KEY,
Payload VARBINARY(MAX) NULL,
[Prefix] AS CONVERT(VARBINARY(19), SUBSTRING([Payload], 1, 19)) PERSISTED);
INSERT INTO #BinaryStuffMaxWithPCC (ID, Payload)
SELECT tmp.ID, tmp.Payload
FROM #BinaryStuffMax tmp;
---
ALTER TABLE #BinaryStuff REBUILD WITH (FILLFACTOR = 100);
ALTER TABLE #BinaryStuffWithPCC REBUILD WITH (FILLFACTOR = 100);
CREATE NONCLUSTERED INDEX [IX_BinaryStuffWithPCC_Prefix]
ON #BinaryStuffWithPCC([Prefix])
WITH (FILLFACTOR = 100);
ALTER TABLE #BinaryStuffMax REBUILD WITH (FILLFACTOR = 100);
ALTER TABLE #BinaryStuffMaxWithPCC REBUILD WITH (FILLFACTOR = 100);
CREATE NONCLUSTERED INDEX [IX_BinaryStuffMaxWithPCC_Prefix]
ON #BinaryStuffMaxWithPCC([Prefix])
WITH (FILLFACTOR = 100);
SELECT @Pattern AS [SamplePattern];
SELECT *
FROM #BinaryStuff
WHERE [ID] % 1000 = 0;
GO
-------------------------------------------------------------------------------------
-- END OF SETUP
-- Selecting the VARBINARY field tests (highlight and run individually):
DECLARE @Pattern VARBINARY(19),
@DevNull VARBINARY(19);
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
FROM #BinaryStuff tmp
WHERE CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;
SET STATISTICS TIME OFF;
GO
PRINT '-------------------';
DECLARE @Pattern VARBINARY(19),
@DevNull VARBINARY(19);
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
FROM #BinaryStuff tmp
WHERE SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;
SET STATISTICS TIME OFF;
GO
PRINT '-------------------';
DECLARE @Pattern VARBINARY(19),
@DevNull VARBINARY(19);
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
FROM #BinaryStuffWithPCC tmp --WITH (INDEX = [IX_BinaryStuffWithPCC_Prefix])
WHERE tmp.[Prefix] <> @Pattern;
SET STATISTICS TIME OFF;
GO
-------------------------------
-- Selecting the ID field tests (highlight and run individually):
DECLARE @Pattern VARBINARY(19),
@ID INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM #BinaryStuff tmp
WHERE CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;
SET STATISTICS TIME OFF;
GO
PRINT '-------------------';
DECLARE @Pattern VARBINARY(19),
@ID INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM #BinaryStuff tmp
WHERE SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;
SET STATISTICS TIME OFF;
GO
PRINT '-------------------';
DECLARE @Pattern VARBINARY(19),
@ID INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM #BinaryStuffWithPCC tmp
WHERE tmp.[Prefix] <> @Pattern;
SET STATISTICS TIME OFF;
GO
-------------------------------
-- Select the ID field against MAX type tests (highlight and run individually):
-- First run-through is with data size <= 100 bytes
-- Second run-through (after running UPDATE statements that follow) is with data size just over 14k bytes
DECLARE @Pattern VARBINARY(19),
@ID INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM #BinaryStuffMax tmp
WHERE CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;
SET STATISTICS TIME OFF;
GO
PRINT '-------------------';
DECLARE @Pattern VARBINARY(19),
@ID INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM #BinaryStuffMax tmp
WHERE SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;
SET STATISTICS TIME OFF;
GO
PRINT '-------------------';
DECLARE @Pattern VARBINARY(19),
@ID INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM #BinaryStuffMaxWithPCC tmp
WHERE tmp.[Prefix] <> @Pattern;
SET STATISTICS TIME OFF;
GO
PRINT '-------------------';
DECLARE @Pattern VARBINARY(19),
@ID INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;
SET STATISTICS TIME ON;
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM #BinaryStuffMax tmp
WHERE tmp.Payload < @Pattern
OR tmp.Payload > @Pattern + 0xFF;
SET STATISTICS TIME OFF;
GO
-----
-- Run the following two UPDATEs to test off-page lookups
-- Run the two SELECTs before and after the UPDATEs to see the physical effects
-- After the UPDATEs, re-run the "Select the ID field against MAX type tests"
SELECT AVG(LEN(tmp.Payload)) AS [AvgBytes], MAX(LEN(tmp.Payload)) AS [MaxBytes]
FROM #BinaryStuffMax tmp;
SELECT so.[name], *
FROM tempdb.sys.dm_db_partition_stats stat
INNER JOIN tempdb.sys.objects so
ON so.[object_id] = stat.[object_id]
WHERE stat.[object_id] < 0
AND so.[name] LIKE N'#BinaryStuff%'
ORDER BY so.[name];
UPDATE tmp
SET tmp.Payload = (tmp.Payload + CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARCHAR(MAX), 'ThisIsSomeTest'), 1000)))
FROM #BinaryStuffMax tmp
UPDATE tmp
SET tmp.Payload = (tmp.Payload + CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARCHAR(MAX), 'ThisIsSomeTest'), 1000)))
FROM #BinaryStuffMaxWithPCC tmp
ALTER TABLE #BinaryStuffMax REBUILD WITH (FILLFACTOR = 100);
ALTER TABLE #BinaryStuffMaxWithPCC REBUILD WITH (FILLFACTOR = 100);
SELECT AVG(LEN(tmp.Payload)) AS [AvgBytes], MAX(LEN(tmp.Payload)) AS [MaxBytes]
FROM #BinaryStuffMax tmp;
SELECT so.[name], *
FROM tempdb.sys.dm_db_partition_stats stat
INNER JOIN tempdb.sys.objects so
ON so.[object_id] = stat.[object_id]
WHERE stat.[object_id] < 0
AND so.[name] LIKE N'#BinaryStuff%'
ORDER BY so.[name];
Comments