SQL Server二進位彙總運算效能的研究

程式開發中涉及許可權管理，系統採用了整形數記錄使用者權限，權限等級設定為：

Level1	1
Level2	2
Level3	4
Level4	8
Level5	16
Level6	31
Level7	32
Level8	64
Level9	127
Level10	128
Level11	255
Level12	65535

 

 

在資料庫中如果一個使用者a權限等級為1, 他的三個角色分別被授予許可權2，2，4

則使用者的許可權應該返回7，即1|2|2|4.

由於T-SQL中沒有二進位的彙總函式，我們的預存程序中起先使用遊標迴圈記錄逐條進行OR運算，然後輸出結果。在進行壓力測試時發現，當系統壓力達到一定程度時SQL Server伺服器CPU會達到100%，而且一直居高不下。

 

大家知道，臭名昭著的遊標一直是SQL編程人員聲討的對象，於是我改用迴圈讀取表記錄逐條進行OR運算，然後輸出結果。

在同樣的壓力下進行測試，結果仍然不理想。

 

根據網上文章http://www.eggheadcafe.com/software/aspnet/33139293/bitwise-aggregate-functio.aspx，有如下兩種方法可以對int進行二進位彙總運算。

 

If you have a numbers table:

SELECT      SUM(mytable.mycolumn),
SUM(DISTINCT Bits.bitval)
FROM        mytable
INNER JOIN (SELECT POWER(2, n-1) AS bitval
FROM   dbo.Numbers
WHERE  n <= 31) AS Bits
ON    mytable.value & Bits.bitval = Bits.bitval;

 

Or if you don't have one (why not?) and don't want to create one (why
not??????), use this instead:

SELECT SUM(mycolumn),
MAX(mycolum & 1)
+ MAX(mycolum & 2)
+ MAX(mycolum & 4)
+ MAX(mycolum & 8)
+ MAX(mycolum & 16)
(....)
+ MAX(mycolum & 1073741824)
FROM   mytable;

 

 

於是又分別按照這兩種方法寫了一遍許可權查詢。

測試指令碼和結果如下：

DECLARE @Access table
(Id INT identity primary key,
 Access INT)

DECLARE @insertCount INT
SET @insertCount=10000

WHILE @insertCount>0
BEGIN

 INSERT INTO @Access(Access)
 SELECT 0 Access
 UNION ALL
 SELECT 1 Access
 UNION ALL
 SELECT 2 Access
 UNION ALL
 SELECT 4 Access
 UNION ALL
 SELECT 8 Access
 UNION ALL
 SELECT 16 Access
 UNION ALL
 SELECT 31 Access
 UNION ALL
 SELECT 32 Access
 UNION ALL
 SELECT 64 Access
 UNION ALL
 SELECT 127 Access
 UNION ALL
 SELECT 128 Access
 UNION ALL
 SELECT 65535 Access
 SET @insertCount = @insertCount - 1
END

DECLARE @SumAccess int
SET @SumAccess=0

--方法1,使用遊標
SELECT getdate()
DECLARE @CurrentAccess INT

DECLARE access_cursor CURSOR FOR
SELECT Access
FROM @Access

OPEN access_cursor
FETCH NEXT FROM access_cursor INTO @CurrentAccess

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SumAccess=@SumAccess|@CurrentAccess
FETCH NEXT FROM access_cursor INTO @CurrentAccess

END

CLOSE access_cursor
DEALLOCATE access_cursor

SELECT @SumAccess AS UsingCursor
SELECT getdate()

--方法2,使用迴圈

DECLARE @MinId INT
DECLARE @MaxId INT

SET @SumAccess=0
SELECT @MinId=Min(Id)
FROM @Access

SELECT @MaxId=Max(Id)
FROM @Access

WHILE @MinId<=@MaxId
BEGIN
 SELECT @SumAccess=@SumAccess|Access
 FROM @Access
 WHERE Id=@MinId
 
 SET @MinId=@MinId+1
END

SELECT @SumAccess AS UsingWhileLoop
SELECT GETDATE()
--方法3,使用二進位彙總方法1
SELECT
  (SUM(DISTINCT(Access & 0x000001))
 + SUM(DISTINCT(Access & 0x000002))
 + SUM(DISTINCT(Access & 0x000004))
 + SUM(DISTINCT(Access & 0x000008))
 + SUM(DISTINCT(Access & 0x000010))
 + SUM(DISTINCT(Access & 0x000020))
 + SUM(DISTINCT(Access & 0x000040))
 + SUM(DISTINCT(Access & 0x000080))
 + SUM(DISTINCT(Access & 0x000100))
 + SUM(DISTINCT(Access & 0x000200))
 + SUM(DISTINCT(Access & 0x000400))
 + SUM(DISTINCT(Access & 0x000800))
 + SUM(DISTINCT(Access & 0x001000))
 + SUM(DISTINCT(Access & 0x002000))
 + SUM(DISTINCT(Access & 0x004000))
 + SUM(DISTINCT(Access & 0x008000))

 --可根據許可權的最大值繼續增加，我們的系統中0x008000為最大值
  ) as BitWiseAggre1
FROM @Access;

SELECT GETDATE();

--方法3,使用二進位彙總方法2
WITH AccessValue (Access)
AS
(
 SELECT 1 --必須寫成1否則如果寫成0x000001 SQL Server會將資料類型預設為varbinary,這樣，下面的處理中將無法進行二進位OR運算
 UNION ALL
 SELECT 0x000002
 UNION
 SELECT 0x000004
 UNION ALL
 SELECT 0x000008
 UNION ALL
 SELECT 0x000010
 UNION ALL
 SELECT 0x000020
 UNION ALL
 SELECT 0x000040
 UNION ALL
 SELECT 0x000080
 UNION ALL
 SELECT 0x000100
 UNION ALL
 SELECT 0x000200
 UNION ALL
 SELECT 0x000400
 UNION ALL
 SELECT 0x000800
 UNION ALL
 SELECT 0x001000
 UNION ALL
 SELECT 0x002000
 UNION ALL
 SELECT 0x004000
 UNION ALL
 SELECT 0x008000
 --可根據許可權的最大值繼續增加，我們的系統中0x008000為最大值
)

SELECT   
SUM(DISTINCT a.Access) AS BitWiseAggre2
FROM        Access_test t
INNER JOIN AccessValue a
ON    t.Access & a.Access = a.Access

SELECT GETDATE()

 

100,000 records
Using Cursor	3.28s
Using While Loop	1.34s
Bit wise Aggregate Sum1	2s
Bit wise Aggregate Sum2	0.4s
10,000 records
Using Cursor	0.33s
Using While Loop	0.25s
Bit wise Aggregate Sum1	0.2s
Bit wise Aggregate Sum2	0.033s
1,000 records
Using Cursor	0.15s
Using While Loop	0.12s
Bit wise Aggregate Sum1	0.02s
Bit wise Aggregate Sum2	0s

 

 

結論：使用二進位彙總方法中第二種方法，即定義包含許可權資料的常量表，效能最佳。