2009-09-22

Hex to string - speed test

I recently wrote a function to parse JSON strings and part of that had to deal with converting a hex string to its string value. I decided to Google to see what other people had done. I found several posts but they used XML or a LIKE statements, which would seem to be a slow. Since binary is the fastest database collation, would binary logic be faster?

Test 1, Using LIKE
DECLARE @InputString nvarchar(max)='0x0061'
,
@hex varchar(255), @value nvarchar(max)
,
@w int, @max int= 10000
, @StartTime datetime2, @EndTime datetime2
SELECT
@hex = SUBSTRING(@InputString, 3, 4)
SELECT @w = 1, @StartTime =GETDATE()

WHILE @w < @max
BEGIN

SELECT @value =
CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE'[0-9]'
THEN CAST(SUBSTRING(@hex, 1, 1) AS INT)
ELSE
CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS int)
END * 4096
+ CASE WHEN
SUBSTRING(@hex, 2, 1) LIKE'[0-9]'
THEN CAST(SUBSTRING(@hex, 2, 1) AS INT)
ELSE
CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT)
END * 256
+ CASE WHEN SUBSTRING(@hex, 3, 1)LIKE'[0-9]'
THEN CAST(SUBSTRING(@hex, 3, 1) AS INT)
ELSE
CAST(ASCII(UPPER(SUBSTRING(@hex, 3, 1)))-55 AS INT)END * 16
+ CASE WHEN SUBSTRING(@hex, 4, 1) LIKE'[0-9]'
THEN
CAST(SUBSTRING(@hex, 4, 1) AS INT)
ELSE
CAST(ASCII(UPPER(SUBSTRING(@hex, 4, 1)))- 55 AS int)
END AS binary(1))

SELECT @w += 1
END

SELECT @EndTime =GETDATE()

SELECT @value AS 'Value', @w AS 'Iterations', DATEDIFF(millisecond, @StartTime, @EndTime)AS 'Diff'

Result 1
Time = 90 milliseconds for 10000 iterations

Test 2, Using Binary
DECLARE @InputString nvarchar(max) = '0x0061'
, @btmp varbinary(255), @value nvarchar(max)
, @w int, @max int = 10000
, @StartTime datetime2, @EndTime datetime2

SELECT @btmp = CONVERT(varbinary(8),
SUBSTRING(@InputString, 3, 4), 0) -- get hex value

SELECT @w = 1, @StartTime = GETDATE()

WHILE @w < @max
BEGIN

SELECT @value = NCHAR(
(CAST(SUBSTRING(@btmp, 1, 1) AS int)
- CASE WHEN (SUBSTRING(@btmp, 1,1))<= 0x39 THEN 48
WHEN (SUBSTRING(@btmp, 1, 1))<= 0x5A THEN 55
ELSE 87 END) * 4096
+ (CAST(SUBSTRING(@btmp, 3, 1) AS int)
- CASE WHEN (SUBSTRING(@btmp, 3, 1))<= 0x39 THEN 48
WHEN (SUBSTRING(@btmp, 3, 1))<= 0x5A THEN 55
ELSE 87 END) * 256

+ (CAST(SUBSTRING(@btmp, 5,1) AS int)
- CASE WHEN (SUBSTRING(@btmp, 5,1))<= 0x39 THEN 48
WHEN (SUBSTRING(@btmp, 5,1))<= 0x5A THEN 55
ELSE 87 END) * 16

+ (CAST(SUBSTRING(@btmp, 7,1)AS int)
- CASE WHEN (SUBSTRING(@btmp, 7,1))<= 0x39 THEN 48
WHEN (SUBSTRING(@btmp, 7,1))<= 0x5A THEN 55
ELSE 87 END)
)

SELECT @w += 1
END

SELECT @EndTime =GETDATE()

SELECT @value AS 'Value', @w AS 'Iterations'
, DATEDIFF(millisecond, @StartTime, @EndTime)AS 'Diff'

Result 2
Time = 63 milliseconds for 10000 iterations

The tests show that the binary logic method is much faster than other methods. Check out using binary or bit logic in your code for speed improvement.

2009-09-13

SQL Server 2008 and Autonomous Transactions

Save a record even after a ROLLBACK occurs.

Autonomous transactions allow you to leave the context of the calling transaction, perform an independent transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only committed data can be shared by both transactions. With previous versions of SQL Server you would write a C++ DLL and add it in as a extended stored procedure, which is not a great solution. SQL Server 2008 provides a means to do this by using a Linked Server.

I use this linked server approach with the error logging I designed for a recent customer's database, which I will call DbName for the following examples. This insures that the record does not get deleted in the case of a ROLLBACK action. To implement this I created Linked Server connection, called DbNameLoopback, to the DbName database, and enabled RPC. The script follows. Note the sp_serveroption statement is the option to not include the call in the current transaction.

Script to Implement You may have to ask your DBA or system administrator to run this.

USE MASTER
GO

IF EXISTS (SELECT srv.name FROM sys.servers srv
WHERE srv.server_id != 0 AND srv.name = N'DbNameLoopback')
EXEC master.dbo.sp_dropserver @server=N'DbNameLoopback', @droplogins='droplogins'
GO

EXEC sp_addlinkedserver @server = N'DbNameLoopback',@srvproduct = N' '
,@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO

EXEC sp_serveroption DbNameLoopback, N'remote proc transaction promotion','FALSE'
-- FALSE = the local transaction will not be promoted to distributed transaction
Go

EXEC sp_serveroption DbNameLoopback,N'RPC OUT','TRUE' -- Enable RPC to the given server.
GO

Test Example:

1) Create a table and stored procedure:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo._TestTable') AND type in (N'U'))
DROP TABLE dbo._TestTable
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE dbo._TestTable
( TestKey int IDENTITY(1, 1) NOT NULL
, Value varchar(max) NOT NULL
, CreateDate datetime2 NOT NULL CONSTRAINT DF_TestTable_CreateDate DEFAULT (getdate())
, CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
(
TestKey ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo._Test_ins') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo._Test_ins
GO

CREATE PROCEDURE dbo._Test_ins
( @Value varchar(50))
AS
SET NOCOUNT ON

INSERT DBNAME.dbo._TestTable(Value)
VALUES(@Value)
GO


2) Run the following script to see the effects of a Rollback.

TRUNCATE TABLE dbo._TestTable

exec _Test_ins 'Test 1: _Test_ins'
SELECT * FROM dbo._TestTable

BEGIN TRAN
exec _Test_ins 'Test 2: _Test_ins in trans'
SELECT 'Before ROLLBACK', * FROM dbo._TestTable
ROLLBACK
SELECT 'After ROLLBACK', * FROM dbo._TestTable -- one record deleted :-(

2) Results: The second record dissapears after the rollback.

TestKeyValueCreateDate
1Test 1: _Test_ins2009-08-06 16:43:55
TestKeyValueCreateDate
Before ROLLBACK1Test 1: _Test_ins2009-08-06 16:43:55
Before ROLLBACK2Test 2: _Test_ins in trans2009-08-06 16:43:55
TestKeyValueCreateDate
After ROLLBACK1Test 1: _Test_ins2009-08-06 16:43:55


3) Run the following script to see the effects using the Loopback linked server:

TRUNCATE TABLE dbo._TestTable
exec DbNameLoopback.DbName.dbo._Test_ins 'Test 3 - use DbNameLoopback _Test_ins'
SELECT * FROM dbo._TestTable

BEGIN TRAN
-- exec dbo._TestLoopback_ins 'Test 4 - use _TestLoopback_ins in transaction'
exec DbNameLoopback.DbName.dbo._Test_ins 'Test 4 - use DbNameLoopback _Test_ins in trans'
SELECT 'Loopback: Before ROLLBACK', * FROM dbo._TestTable
ROLLBACK

SELECT 'Loopback: After ROLLBACK', * FROM dbo._TestTable -- No record deleted :-)


3) Results: The second record remains after the rollback - yea!

TestKeyValueCreateDate
1Test 3 - use DbNameLoopback _Test_ins2009-08-06 16:50:57
TestKeyValueCreateDate
Loopback: Before ROLLBACK1Test 3 - use DbNameLoopback _Test_ins2009-08-06 16:50:57
Loopback: Before ROLLBACK2Test 4 - use DbNameLoopback _Test_ins in trans2009-08-06 16:50:57
TestKeyValueCreateDate
Loopback: After ROLLBACK1Test 3 - use DbNameLoopback _Test_ins2009-08-06 16:50:57
Loopback: After ROLLBACK2Test 4 - use DbNameLoopback _Test_ins in trans2009-08-06 16:50:57




For more information search online for SQL Server 2008 Autonomous Transaction.