SQL Server 2008 : new binary – hex string conversion functionality can dramatically improve related query performan...

[SQL-Server-Team]

First published on MSDN on Oct 31, 2008

In previous SQL Server releases it wasn’t possible to convert binary data to string characters in hex format directly, because SQL Server did not have a built-in Transact-SQL command for converting binary data to a hexadecimal string. The Transact-SQL CONVERT command converted binary data to character data in a one byte to one character fashion. SQL Server would take each byte of the source binary data, convert it to an integer value, and then uses that integer value as the ASCII value for the destination character data. This behavior applied to the binary, varbinary, and timestamp datatypes.

The only workarounds were to use either a stored procedure as described in a Knowledge Base Article:  "INFO: Converting Binary Data to Hexadecimal String" ( http://support.microsoft.com/kb/104829 ) or by writing a CLR function.

An ISV I work with doesn’t support CLR and therefore they implemented their own version of a custom convert function in form of a stored procedure. This one was even faster than everything else they found on the Internet.

NEW – IN SQL SERVER 2008 the convert function was extended to support binary data – hex string conversion. It looks like a tiny improvement almost not worth mentioning.

However, for the ISV it was a big step forward as some critical queries need this functionality. Besides the fact that they no longer have to ship and maintain their own stored procedure, a simple repro showed a tremendous performance improvement.

Repro:

=====

I transformed the procedure described in the KB article mentioned above into a simple function. The stored procedure below will create a simple test table with one varbinary column and insert some test rows in 10K packages ( e.g. nr_rows = 100 -> 1 million rows in the table ).

The repro shows two different test cases:

1. insert 0x0 two million times

2. insert 0x0123456789A12345 two million times

Depending on the length of the value the disadvantage of the stored procedure solution will be even bigger. On my test machine the results of the test queries below were:

(both tests were done with the same SQL Server 2008 instance - no change of any settings)

1. two million times value 0x0

a, using stored procedure : about 3460 logical reads, no disk IO, ~52 secs elapsed time

b, using new convert feature : about 5200 logical reads,  no disk IO, < 1 sec elapsed time

2. two million times value 0x0123456789A12345

a, using stored procedure : about 3460 logical reads, no disk IO, ~157 secs elapsed time

b, using new convert feature : about 5200 logical reads,  no disk IO, < 1 sec elapsed time

Repro Script:

========

create function sp_hexadecimal ( @binvalue varbinary(255) )

returns varchar(255)

as

begin

declare @charvalue varchar(255)

declare @i int

declare @length int

declare @hexstring char(16)

select @charvalue = '0x'

select @i = 1

select @length = datalength(@binvalue)

select @hexstring = '0123456789abcdef'

while (@i <= @length)

begin

declare @tempint int

declare @firstint int

declare @secondint int

select @tempint = convert(int, substring(@binvalue,@i,1))

select @firstint = floor(@tempint/16)

select @secondint = @tempint - (@firstint*16)

select @charvalue = @charvalue +

substring(@hexstring, @firstint+1, 1) +

substring(@hexstring, @secondint+1, 1)

select @i = @i + 1

end

return ( @charvalue )

end

create procedure cr_conv_test_table ( @value varbinary(16), @nr_rows int )

as

begin

declare @exist int

declare @counter int

set NOCOUNT ON

set statistics time off

set statistics io off

set statistics profile off

set @exist = ( select count(*) from sys.objects

where name = 'conv_test_table' and

type = 'U' )

if( @exist = 1 )

drop table conv_test_table

set @exist = ( select count(*) from sys.objects

where name = 'conv_test_table_temp' and

type = 'U' )

if( @exist = 1 )

drop table conv_test_table_temp

create table conv_test_table ( varbincol varbinary(16) )

create table conv_test_table_temp ( varbincol varbinary(16) )

set @counter = 10000

while @counter > 0

begin

insert into conv_test_table_temp values ( @value )

set @counter = @counter - 1

end

set @counter = @nr_rows

while @counter > 0

begin

insert into conv_test_table select * from conv_test_table_temp

set @counter = @counter - 1

end

end

-- create 2 million test rows

execute cr_conv_test_table 0x0, 200

set statistics time on

set statistics io on

-- compare runtime of stored procedure with new convert feature

select count(*) from conv_test_table

where dbo.sp_hexadecimal(varbincol) = '0x00'

select count(*) from conv_test_table

where CONVERT(varchar(255),varbincol,1) = '0x00'

-- create 2 million test rows

execute cr_conv_test_table 0x0123456789A12345, 200

set statistics time on

set statistics io on

-- compare runtime of stored procedure with new convert feature

select count(*) from conv_test_table

where dbo.sp_hexadecimal(varbincol) = '0x0123456789A12345'

select count(*) from conv_test_table

where CONVERT(varchar(255),varbincol,1) = '0x0123456789A12345'

Cross Posted from http://blogs.microsoft.com/mssqlisv