通过缓存加快检索下拉列表的不同值

Question

概述

在我的ASP.Net MVC应用程序中，我有几个页面使用DataRecord搜索功能，该功能由站点管理员动态配置，以使特定的DataRecord字段可用作几种不同搜索输入类型之一的标准。可用的输入类型之一是下拉列表，其中填充了与搜索上下文相关的特定字段的不同DataRecord值

我希望减少创建这些下拉列表所需的时间，并愿意接受建议

我将以以下方式列出：

1. SQL结构
2. 示例查询
3. 业务规则
4. 杂项信息(可能相关，也可能不相关，但我不想排除任何可能性)

SQL结构

从最大范围到最低范围列出，仅包含相关字段。每个表与下面的表都有一个一对多的关系。请记住，这些都是通过迁移的EF Code First创建和维护的

CREATE TABLE[dbo]。[公司信息]([Id][int]IDENTITY(1,1)NOT NULL，CONSTRAINT[PK_dbo.CompanyInfoes]主键集群([Id]ASC)其中(PAD_INDEX=OFF，STATISTICS_NORECOMPUTE=OFF，IGNORE_DUP_KEY=OFF，ALLOW_ROW_LOCKS=ON，ALLOW_PAGE_LOCKS=ON)在[PRIMARY]上)在[PRIMARY]

CREATE TABLE[dbo]上。[业务线]([Id][int]IDENTITY(1,1)NOT NULL，[Company_Id][int]不为空，CONSTRAINT[PK_dbo.BusinessLines]主键集群([Id]ASC)其中(PAD_INDEX=OFF，STATISTICS_NORECOMPUTE=OFF，IGNORE_DUP_KEY=OFF，ALLOW_ROW_LOCKS=ON，ALLOW_PAGE_LOCKS=ON)在[PRIMARY]上)在[主]上TEXTIMAGE_ON[主]ALTER TABLE[dbo]。带有CHECK ADD CONSTRAINT[FK_dbo.BusinessLines_dbo.CompanyInfoes_Company_Id]FOREING KEY([Company_Id])的[BusinessLines]参考文献[dbo]。[公司信息]([Id])ALTER TABLE[dbo]。[BusinessLines]CHECK CONSTRAINT[FK_dbo.BusinessLines_dbo.CompanyInfoes_Company_Id]

CREATE TABLE[dbo]。[数据文件]([Id][int]IDENTITY(1,1)NOT NULL，[FileStatus][int]NOT NULL，[FileEnvironment][int]NOT NULL，[BusinessLine_Id][int]NOT NULL，CONSTRAINT[PK_dbo.DataFiles]主键集群([Id]ASC)其中(PAD_INDEX=OFF，STATISTICS_NORECOMPUTE=OFF，IGNORE_DUP_KEY=OFF，ALLOW_ROW_LOCKS=ON，ALLOW_PAGE_LOCKS=ON)在[PRIMARY]上)在[主]上TEXTIMAGE_ON[主]ALTER TABLE[dbo]。[DataFiles]WITH CHECK ADD CONSTRAINT[FK_dbo.DataFiles_dbo.BusinessLines_BusinessLine_Id]FOREING KEY([BusinessLine_Id)参考文献[dbo]。[业务线]([Id])删除级联ALTER TABLE[dbo]。[DataFiles]CHECK CONSTRAINT[FK_dbo.DataFiles_dbo.BusinessLines_BusinessLine_Id]

CREATE TABLE[dbo]。[数据记录]([Id][int]IDENTITY(1,1)NOT NULL，[File_Id][int]NOT NULL，[Field1][nvarchar](最大值)为NULL，[Field2][nvarchar](最大值)为NULL，。。。[Field20][nvarchar](最大值)为NULL，CONSTRAINT[PK_dbo.DataRecords]主键集群([Id]ASC)其中(PAD_INDEX=OFF，STATISTICS_NORECOMPUTE=OFF，IGNORE_DUP_KEY=OFF，ALLOW_ROW_LOCKS=ON，ALLOW_PAGE_LOCKS=ON)在[PRIMARY]上)在[主]上TEXTIMAGE_ON[主]ALTER TABLE[dbo]。[DataRecords]WITH CHECK ADD CONSTRAINT[FK_dbo.DataRecords_dbo.DataFiles_File_Id1]FOREING KEY([File_Id])参考文献[dbo]。[DataFiles]([Id])删除级联ALTER TABLE[dbo]。[DataRecords]CHECK CONSTRAINT[FK_dbo.DataRecords_dbo.DataFiles_File_Id1]

示例查询(由EF生成)

SELECT[Distinct1]。[字段2]AS[字段2]FROM(选择不同[延伸1]。[字段2]AS[字段2]自[dbo]。[DataRecords]AS[Extent1]内部联接[dbo]。[DataFiles]AS[Extent2]ON[Extent1]。[File_Id]=[Extent2]。[Id]WHERE([Extent2].[BusinessLine_Id]IN(4，5，6，7，8，11，12，13，14))AND(0=[Extent2].[FileEnvironment])AND(1=[Extend2].[FileStatus]))AS[Distinct1]

业务规则

1. 下拉列表中的值应基于查看用户的BusinessLine访问权限(查询中的[BusinessLine_Id]子句)以及与搜索一起使用的当前页面([FileEnvironment]和[FileStatus])
2. 20个DataRecords字段中的哪一个应该作为下拉列表显示以进行搜索，由站点管理员通过管理页面进行控制，并在公司级别进行配置。公司A可能有字段1的下拉列表，公司B可能有字段5、字段7和字段18的下拉列表。公司C可能没有任何下拉列表
3. 虽然DataRecords的布局和格式在不同公司之间是一致的，但Field1-Field20的用法以及值的唯一性却不一致。在900k条记录中，公司A的Field1可能有3个唯一值(因此，为什么对它们使用Field1的下拉列表是有意义的)，而公司B的每个DataRecord的Field1中可能有一些唯一值
4. 所有与数据库相关的内容都通过EF迁移进行维护，并且该站点设置为在应用程序启动时自动应用迁移(或者在Azure过渡站点的情况下在部署时自动应用)。从数据库角度推荐的任何内容都必须能够通过迁移以编程方式实现，这样就可以在没有数据库访问权限的人手动干预的情况下完成站点和数据库的升级或实例化。此外，任何需要进行的数据库更改都不应干扰在更改模型时创建的CodeFirst迁移(IE无法重命名列，因为存在一些在注释之外添加的恶意索引)
5. 与前一点类似，Dropdown配置是通过站点控制的，因此任何需要做的事情都必须能够在运行时根据需要添加和删除
6. 网站使用过程中发生的相关数据更改，但不一定由当前用户更改：
   1. FileDataFile的状态从0更改为1或2
   2. 当前用户可以访问哪些业务线更改
   3. 添加了其他业务线
7. 网站外发生的相关数据更改(通过进口商应用程序，该应用程序也是网站所在解决方案的一部分，因此可以在必要时进行修改)：
   1. 添加新的数据文件和数据记录
   2. 添加了其他业务线(不是复制/粘贴错误，它们也可以通过导入程序添加)

杂项信息

1. 站点被部署到许多位置，但在每次部署中，站点与数据库的比例是1:1。因此，内存中的缓存并不是不可能的
2. 只有一个站点管理员控制哪些字段被表示为下拉列表，他可以了解频繁更改的后果，以及在必要时缓存每个更改可能导致的后果。他还熟悉公司层面每个领域的数据，知道哪些领域是Dropdown的好候选者
3. 仅举一个数据量的例子，在短短2.5个多月的时间里，一家公司的DataRecords数量从55.8万增加到92.4万。因此，很明显，该解决方案应该能够处理不断增长的数据量
4. 将值加载到ajax请求的加载时间减载以避免页面加载通常是一个很好的解决方案，但我不能使用它

Answer 1

跳出这里的两个快速项目是

1) 以添加正在返回的Field2列，作为DataRecords表上CLUSTRED INDEX中的INCLUDE。这将使它不需要在ON子句完成查找ID的主要工作后进行书签查找来查找Field2。

2) 不确定为什么会出现双重选择。我不认为这会有太大的影响，但查询只是重新选择它选择的不同内容，甚至没有更改名称。。。