Research on SQL Server binary aggregation Performance

Permission management is involved in program development. The system uses an integer to record user permissions. The permission level is set:

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

 

 

In the database, if a user A has a permission level of 1, his three roles are granted permissions 2, 2, and 4 respectively.

The user's permission should return 7, that is, 1 | 2 | 2 | 4.

Since there is no binary aggregate function in the T-SQL, we use cursor loop records in the stored procedure to perform or operations one by one, and then output the results. During the stress test, we found that when the system pressure reaches a certain level, the CPU of the SQL Server server will reach 100% and remain high.

 

As we all know, the notorious cursor has always been an object that SQL programmers have complained about, so I used to read Table records cyclically and perform or operations one by one, and then output the results.

The test results are still unsatisfactory under the same stress.

 

Based on online posts.

 

If you have a numbers table:

Select sum (mytable. mycolumn ),
Sum (distinct bits. bitval)
From mytable
Inner join (select power (1, 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;

 

 

Therefore, the permission query is written again according to the two methods.

The test script and result are as follows:

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 accesskeysecret
Union all
Select 128 accesskeysecret
Union all
Select 65535 accesskeysecret
Set @ insertcount = @ insertcount-1
End

Declare @ sumaccess int
Set @ sumaccess = 0

-- Method 1: Use a cursor
Select getdate ()
Declare @ currentaccess int

Declare access_cursor cursor
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 ()

-- Method 2: Use loop

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 ()
-- Method 3: Use binary aggregation method 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 ))

-- Continue to increase according to the maximum permission value. In our system, 0x008000 is the maximum value.
) As bitwiseaggre1
From @ access;

Select getdate ();

-- Method 3: Use binary aggregation method 2
With accessvalue (ACCESS)
As
(
Select 1 -- it must be written as 1; otherwise, if it is written as 0x000001, SQL Server will set the data type to varbinary by default, so that the following processing will not be able to perform binary or operations
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
-- Continue to increase according to the maximum permission value. In our system, 0x008000 is the maximum value.
)

Select
Sum (distinct A. Access) as bitwiseaggre2
From access_test t
Inner join accessvalue
On T. Access & A. Access = A. Access

Select getdate ()

 

100,000 records
Using cursor	3.28 s
Using WHILE LOOP	1.34 s
Bit wise aggregate sum1	2 S
Bit wise aggregate sum2	0.4 s
10,000 records
Using cursor	0.33 s
Using WHILE LOOP	0.25 s
Bit wise aggregate sum1	0.2 s
Bit wise aggregate sum2	0.033 s
1,000 records
Using cursor	0.15 s
Using WHILE LOOP	0.12 s
Bit wise aggregate sum1	0.02 s
Bit wise aggregate sum2	0 s

 

 

Conclusion: The second method of binary aggregation is used to define a constant table containing permission data, which has the best performance.