SqlQuantumLeap

Binary Substring Tests

Jun 8th, 2015
398
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- This script pertains to the following Stack Overflow question:
  2. --       http://stackoverflow.com/questions/30330035/performance-of-varbinary-comparison-in-t-sql
  3. -------------------------------------------------------------------------------------------------------------
  4.  
  5. -- RUN THE FOLLOWING SETUP ONCE:
  6. -------------------------------------------------------------------------------------
  7. DECLARE @Pattern VARBINARY(19),
  8.         @SetsOf1000TestRows INT;
  9.  
  10. SET @SetsOf1000TestRows = 50;
  11. SET @Pattern = CONVERT(VARBINARY(19), CONVERT(VARCHAR(32), CONVERT(BINARY(16), NEWID()), 2) + '0A0B0C', 2);
  12. SET CONTEXT_INFO @Pattern;
  13.  
  14. SET NOCOUNT ON;
  15.  
  16. IF (OBJECT_ID(N'tempdb..#BinaryStuff') IS NOT NULL)
  17. BEGIN
  18.   DROP TABLE #BinaryStuff;
  19. END;
  20.  
  21. IF (OBJECT_ID(N'tempdb..#BinaryStuffWithPCC') IS NOT NULL)
  22. BEGIN
  23.   DROP TABLE #BinaryStuffWithPCC;
  24. END;
  25.  
  26. IF (OBJECT_ID(N'tempdb..#BinaryStuffMax') IS NOT NULL)
  27. BEGIN
  28.   DROP TABLE #BinaryStuffMax;
  29. END;
  30.  
  31. IF (OBJECT_ID(N'tempdb..#BinaryStuffMaxWithPCC') IS NOT NULL)
  32. BEGIN
  33.   DROP TABLE #BinaryStuffMaxWithPCC;
  34. END;
  35.  
  36. CREATE TABLE #BinaryStuff(ID INT IDENTITY(1, 1) PRIMARY KEY,
  37.                           Payload VARBINARY(100) NULL
  38.                          );
  39.  
  40. ;WITH TestSets AS
  41. (
  42.   SELECT TOP (@SetsOf1000TestRows) sac.[column_id] AS [Repeats]
  43.   FROM   [master].sys.all_columns sac
  44. )
  45. INSERT INTO #BinaryStuff (Payload)
  46.   SELECT tmp.Payload
  47.   FROM   TestSets ts
  48.   CROSS APPLY (
  49.     SELECT TOP (999)
  50.            CONVERT(VARBINARY(100),
  51.                    CONVERT(VARCHAR(100), NEWID()) + CONVERT(VARCHAR(100), COALESCE(sac.collation_name, 'bob') + sac.[name])
  52.                   ) AS [Payload]
  53.     FROM   [master].sys.all_columns sac
  54.     UNION ALL
  55.     SELECT CONVERT(VARBINARY(100),
  56.                    CONVERT(VARCHAR(50), @Pattern, 2) + REPLICATE(SUBSTRING(CONVERT(VARCHAR(36), NEWID()), 1, 8), ts.[Repeats]), 2
  57.                   )
  58.   ) tmp
  59.  
  60. ---
  61. CREATE TABLE #BinaryStuffWithPCC(ID INT PRIMARY KEY,
  62.                           Payload VARBINARY(100) NULL,
  63.                           [Prefix] AS SUBSTRING([Payload], 1, 19) PERSISTED);
  64.  
  65. INSERT INTO #BinaryStuffWithPCC (ID, Payload)
  66.   SELECT tmp.ID, tmp.Payload
  67.   FROM   #BinaryStuff tmp;
  68. ---
  69. CREATE TABLE #BinaryStuffMax(ID INT PRIMARY KEY,
  70.                           Payload VARBINARY(MAX) NULL
  71.                          );
  72.  
  73. INSERT INTO #BinaryStuffMax (ID, Payload)
  74.   SELECT tmp.ID, tmp.Payload
  75.   FROM   #BinaryStuff tmp;
  76. ---
  77. CREATE TABLE #BinaryStuffMaxWithPCC(ID INT PRIMARY KEY,
  78.                           Payload VARBINARY(MAX) NULL,
  79.                           [Prefix] AS CONVERT(VARBINARY(19), SUBSTRING([Payload], 1, 19)) PERSISTED);
  80.  
  81. INSERT INTO #BinaryStuffMaxWithPCC (ID, Payload)
  82.   SELECT tmp.ID, tmp.Payload
  83.   FROM   #BinaryStuffMax tmp;
  84. ---
  85.  
  86. ALTER TABLE #BinaryStuff REBUILD WITH (FILLFACTOR = 100);
  87.  
  88. ALTER TABLE #BinaryStuffWithPCC REBUILD WITH (FILLFACTOR = 100);
  89.  
  90. CREATE NONCLUSTERED INDEX [IX_BinaryStuffWithPCC_Prefix]
  91.   ON #BinaryStuffWithPCC([Prefix])
  92.   WITH (FILLFACTOR = 100);
  93.  
  94. ALTER TABLE #BinaryStuffMax REBUILD WITH (FILLFACTOR = 100);
  95.  
  96. ALTER TABLE #BinaryStuffMaxWithPCC REBUILD WITH (FILLFACTOR = 100);
  97.  
  98. CREATE NONCLUSTERED INDEX [IX_BinaryStuffMaxWithPCC_Prefix]
  99.   ON #BinaryStuffMaxWithPCC([Prefix])
  100.   WITH (FILLFACTOR = 100);
  101.  
  102.  
  103. SELECT @Pattern AS [SamplePattern];
  104.  
  105. SELECT *
  106. FROM   #BinaryStuff
  107. WHERE  [ID] % 1000 = 0;
  108. GO
  109. -------------------------------------------------------------------------------------
  110. -- END OF SETUP
  111.  
  112.  
  113. -- Selecting the VARBINARY field tests (highlight and run individually):
  114.  
  115. DECLARE @Pattern VARBINARY(19),
  116.         @DevNull VARBINARY(19);
  117. SET @Pattern = CONTEXT_INFO();
  118. --SELECT @Pattern;
  119.  
  120. SET STATISTICS TIME ON;
  121.  
  122. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
  123. FROM   #BinaryStuff tmp
  124. WHERE  CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;
  125.  
  126. SET STATISTICS TIME OFF;
  127. GO
  128.  
  129. PRINT '-------------------';
  130.  
  131. DECLARE @Pattern VARBINARY(19),
  132.         @DevNull VARBINARY(19);
  133. SET @Pattern = CONTEXT_INFO();
  134. --SELECT @Pattern;
  135.  
  136. SET STATISTICS TIME ON;
  137.  
  138. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
  139. FROM   #BinaryStuff tmp
  140. WHERE  SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;
  141.  
  142. SET STATISTICS TIME OFF;
  143. GO
  144.  
  145. PRINT '-------------------';
  146.  
  147. DECLARE @Pattern VARBINARY(19),
  148.         @DevNull VARBINARY(19);
  149. SET @Pattern = CONTEXT_INFO();
  150. --SELECT @Pattern;
  151.  
  152. SET STATISTICS TIME ON;
  153.  
  154. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
  155. FROM   #BinaryStuffWithPCC tmp --WITH (INDEX = [IX_BinaryStuffWithPCC_Prefix])
  156. WHERE  tmp.[Prefix] <> @Pattern;
  157.  
  158. SET STATISTICS TIME OFF;
  159. GO
  160.  
  161. -------------------------------
  162.  
  163. -- Selecting the ID field tests (highlight and run individually):
  164.  
  165. DECLARE @Pattern VARBINARY(19),
  166.         @ID      INT;
  167. SET @Pattern = CONTEXT_INFO();
  168. --SELECT @Pattern;
  169.  
  170. SET STATISTICS TIME ON;
  171.  
  172. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
  173. FROM   #BinaryStuff tmp
  174. WHERE  CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;
  175.  
  176. SET STATISTICS TIME OFF;
  177. GO
  178.  
  179. PRINT '-------------------';
  180.  
  181. DECLARE @Pattern VARBINARY(19),
  182.         @ID      INT;
  183. SET @Pattern = CONTEXT_INFO();
  184. --SELECT @Pattern;
  185.  
  186. SET STATISTICS TIME ON;
  187.  
  188. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
  189. FROM   #BinaryStuff tmp
  190. WHERE  SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;
  191.  
  192. SET STATISTICS TIME OFF;
  193. GO
  194.  
  195. PRINT '-------------------';
  196.  
  197. DECLARE @Pattern VARBINARY(19),
  198.         @ID      INT;
  199. SET @Pattern = CONTEXT_INFO();
  200. --SELECT @Pattern;
  201.  
  202. SET STATISTICS TIME ON;
  203.  
  204. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
  205. FROM   #BinaryStuffWithPCC tmp
  206. WHERE  tmp.[Prefix] <> @Pattern;
  207.  
  208. SET STATISTICS TIME OFF;
  209. GO
  210.  
  211. -------------------------------
  212.  
  213. -- Select the ID field against MAX type tests (highlight and run individually):
  214. --   First run-through is with data size <= 100 bytes
  215. --   Second run-through (after running UPDATE statements that follow) is with data size just over 14k bytes
  216.  
  217. DECLARE @Pattern VARBINARY(19),
  218.         @ID      INT;
  219. SET @Pattern = CONTEXT_INFO();
  220. --SELECT @Pattern;
  221.  
  222. SET STATISTICS TIME ON;
  223.  
  224. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
  225. FROM   #BinaryStuffMax tmp
  226. WHERE  CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;
  227.  
  228. SET STATISTICS TIME OFF;
  229. GO
  230.  
  231. PRINT '-------------------';
  232.  
  233. DECLARE @Pattern VARBINARY(19),
  234.         @ID      INT;
  235. SET @Pattern = CONTEXT_INFO();
  236. --SELECT @Pattern;
  237.  
  238. SET STATISTICS TIME ON;
  239.  
  240. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
  241. FROM   #BinaryStuffMax tmp
  242. WHERE  SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;
  243.  
  244. SET STATISTICS TIME OFF;
  245. GO
  246.  
  247. PRINT '-------------------';
  248.  
  249. DECLARE @Pattern VARBINARY(19),
  250.         @ID      INT;
  251. SET @Pattern = CONTEXT_INFO();
  252. --SELECT @Pattern;
  253.  
  254. SET STATISTICS TIME ON;
  255.  
  256. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
  257. FROM   #BinaryStuffMaxWithPCC tmp
  258. WHERE  tmp.[Prefix] <> @Pattern;
  259.  
  260. SET STATISTICS TIME OFF;
  261. GO
  262.  
  263. PRINT '-------------------';
  264.  
  265. DECLARE @Pattern VARBINARY(19),
  266.         @ID      INT;
  267. SET @Pattern = CONTEXT_INFO();
  268. --SELECT @Pattern;
  269.  
  270. SET STATISTICS TIME ON;
  271.  
  272. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
  273. FROM   #BinaryStuffMax tmp
  274. WHERE  tmp.Payload < @Pattern
  275. OR    tmp.Payload > @Pattern + 0xFF;
  276.  
  277. SET STATISTICS TIME OFF;
  278. GO
  279.  
  280. -----
  281. -- Run the following two UPDATEs to test off-page lookups
  282. -- Run the two SELECTs before and after the UPDATEs to see the physical effects
  283. -- After the UPDATEs, re-run the "Select the ID field against MAX type tests"
  284. SELECT AVG(LEN(tmp.Payload)) AS [AvgBytes], MAX(LEN(tmp.Payload)) AS [MaxBytes]
  285. FROM   #BinaryStuffMax tmp;
  286.  
  287. SELECT so.[name], *
  288. FROM tempdb.sys.dm_db_partition_stats stat
  289. INNER JOIN tempdb.sys.objects so
  290.         ON so.[object_id] = stat.[object_id]
  291. WHERE stat.[object_id] < 0
  292. AND   so.[name] LIKE N'#BinaryStuff%'
  293. ORDER BY so.[name];
  294.  
  295.  
  296. UPDATE tmp
  297. SET    tmp.Payload = (tmp.Payload + CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARCHAR(MAX), 'ThisIsSomeTest'), 1000)))
  298. FROM   #BinaryStuffMax tmp
  299.  
  300. UPDATE tmp
  301. SET    tmp.Payload = (tmp.Payload + CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARCHAR(MAX), 'ThisIsSomeTest'), 1000)))
  302. FROM   #BinaryStuffMaxWithPCC tmp
  303.  
  304. ALTER TABLE #BinaryStuffMax REBUILD WITH (FILLFACTOR = 100);
  305.  
  306. ALTER TABLE #BinaryStuffMaxWithPCC REBUILD WITH (FILLFACTOR = 100);
  307.  
  308.  
  309. SELECT AVG(LEN(tmp.Payload)) AS [AvgBytes], MAX(LEN(tmp.Payload)) AS [MaxBytes]
  310. FROM   #BinaryStuffMax tmp;
  311.  
  312. SELECT so.[name], *
  313. FROM tempdb.sys.dm_db_partition_stats stat
  314. INNER JOIN tempdb.sys.objects so
  315.         ON so.[object_id] = stat.[object_id]
  316. WHERE stat.[object_id] < 0
  317. AND   so.[name] LIKE N'#BinaryStuff%'
  318. ORDER BY so.[name];
RAW Paste Data