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-22
Hex to string - speed test
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.
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!
For more information search online for SQL Server 2008 Autonomous Transaction.
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.
TestKey | Value | CreateDate | |
1 | Test 1: _Test_ins | 2009-08-06 16:43:55 | |
TestKey | Value | CreateDate | |
Before ROLLBACK | 1 | Test 1: _Test_ins | 2009-08-06 16:43:55 |
Before ROLLBACK | 2 | Test 2: _Test_ins in trans | 2009-08-06 16:43:55 |
TestKey | Value | CreateDate | |
After ROLLBACK | 1 | Test 1: _Test_ins | 2009-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!
TestKey | Value | CreateDate | |
1 | Test 3 - use DbNameLoopback _Test_ins | 2009-08-06 16:50:57 | |
TestKey | Value | CreateDate | |
Loopback: Before ROLLBACK | 1 | Test 3 - use DbNameLoopback _Test_ins | 2009-08-06 16:50:57 |
Loopback: Before ROLLBACK | 2 | Test 4 - use DbNameLoopback _Test_ins in trans | 2009-08-06 16:50:57 |
TestKey | Value | CreateDate | |
Loopback: After ROLLBACK | 1 | Test 3 - use DbNameLoopback _Test_ins | 2009-08-06 16:50:57 |
Loopback: After ROLLBACK | 2 | Test 4 - use DbNameLoopback _Test_ins in trans | 2009-08-06 16:50:57 |
For more information search online for SQL Server 2008 Autonomous Transaction.
Subscribe to:
Posts (Atom)