sql-server - Sql Server 中的按位与

Question

我有一个非常典型的情况。我们有一个名为 Users 的表，其中有一列名为 Branches (varchar 1000)。

该组织可以有 1000 个分支机构。因此，如果用户有权访问分支 1、5 和 10，则分支字符串将如下所示:

1000100001000000000......

(即 1 表示用户根据分支编号有权访问分支的位置)。请不要建议更好的数据存储选项，这是我从跨洲部署的遗留应用程序中得到的。

现在考虑到这一背景(并考虑到可能有 > 10000 个用户)，我想搜索有权访问任意一组给定分支的所有用户，例如查找有权访问分支 10、65、90 或 125 的所有用户。

一个简单的解决方案是将所需的分支集(即 10、65、90、125)转换为分支字符串(00000010100 等)，然后使用标量 UDF 迭代两个分支字符串并首先返回 true 2 个分支字符串有 1 的匹配发生，如果公共(public)位置没有 1，则为 false。

除此之外，我还可以选择在 C# 中的应用程序中进行搜索。其中一些用户具有特权(大约 1000 或更多)，并且他们的数据缓存在应用程序中，因为访问非常频繁。但对于其他没有特权的用户来说，数据仅存在于数据库中。

我有两个问题:1)对于数据库搜索，除了我提到的UDF方法之外，还有更好的方法吗？2) 对于特权用户，在性能方面会更好，在应用程序中搜索(这进一步可以基于分支字符串上的 for 循环，如 UDF 中，或者作为 2 个分支数组上的 Linq Intersect 运算符，即 Linq Intersect在 [1,5,9,50,80,200] 和 [6,90,256,300] 等)数据库搜索或基于应用程序的搜索会产生更快的结果吗？

考虑在这两种情况下可能还有其他搜索参数，例如姓氏开头为。

我当前的方法是首先根据其他参数(例如姓氏开头)过滤数据库中的这两种情况的行。然后使用标量 UDF 根据分支过滤此结果集，然后返回结果。

Answer 1

用 SQL 来做，只会比用 C# 或其他前端做快 100 倍。

使用内置数字表将长字符串分解为多个位置(数字系列最大为 2047)。

示例表格

create table users (userid int)
insert users select 1 union all select 2

create table permission (userid int, bigstr varchar(1000))
insert permission
select 1, REPLICATE('0', 56) + '1' -- 57th
        + REPLICATE('0', 32) + '1' -- 90th
        + REPLICATE('0', 64) + '1' -- 155th
        + REPLICATE('0', 845)
insert permission
select 2, REPLICATE('0', 66) + '1' -- 67th
        + REPLICATE('0', 98) + '1' -- 166th
        + REPLICATE('0', 657) + '1' -- 824th
        + REPLICATE('0', 176)

显示列表中所有匹配权限的示例

select *
from users u
inner join permission p on p.userid=u.userid
inner join master..spt_values v on v.type='p'
  and SUBSTRING(p.bigstr,v.number,1) = '1'
  and v.number between 1 and LEN(p.bigstr)  -- or 1000 if it is always 1000
where v.number in (57,90,824)

要查找有权访问列表中至少一个分支的用户:

select distinct u.userid
from users u
inner join permission p on p.userid=u.userid
inner join master..spt_values v on v.type='p'
  and SUBSTRING(p.bigstr,v.number,1) = '1'
  and v.number between 1 and LEN(p.bigstr)  -- or 1000 if it is always 1000
where v.number in (57,90,824)

等等..