sll sll - 1 year ago 78
SQL Question

Performance of varbinary comparison in T-SQL

One of a table columns is of VARBINARY type, need to raise a query to evaluate rows for particular bytes pattern, following approach dramatically decreases performance:

declare @pattern varbinary

// 19 bytes constant
set @pattern = 0x00.....

-- r.payload is of VARBINARY type as well
SELECT .... FROM ...
WHERE substring(r.payload, 0, 19) <> @pattern

Is there any alternative to the given approach?

Answer Source

Here you go, use a calculated column to store the first 19 bytes, PERSISTED so you can index it.

A demonstration can be found here on SQLFiddle.

When I perform the linked test, the persisted and indexed approach is about 5 times faster. This might increase significantly if the average [Payload] is very large.

CREATE TABLE [dbo].[YourTable]
    [Payload] VARBINARY(MAX),
    [Prefix] AS CAST([Payload] AS BINARY(19)) PERSISTED

CREATE NONCLUSTERED INDEX [IX_YourTable_Prefix] ON [YourTable]([Prefix]);


            [Prefix] <> @pattern 

or some such.

It is not entirely clear to me what your are trying to achieve but potentially the use of HASHBYTES might be useful.