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

概述

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

I'm looking to decrease the amount of time it takes to create these dropdowns, and am open to suggestions.

我将以下列方式列出内容：

• SQL结构

• 示例查询

• 业务规则

• 杂项信息（可能相关，也可能不相关，但我不想排除任何内容）

SQL结构

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

CREATE TABLE [dbo].[CompanyInfoes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PK_dbo.CompanyInfoes] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[BusinessLines](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Company_Id] [int] NOT NULL,
    CONSTRAINT [PK_dbo.BusinessLines] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[BusinessLines]  WITH CHECK ADD  CONSTRAINT [FK_dbo.BusinessLines_dbo.CompanyInfoes_Company_Id] FOREIGN KEY([Company_Id])
REFERENCES [dbo].[CompanyInfoes] ([Id])

ALTER TABLE [dbo].[BusinessLines] CHECK CONSTRAINT [FK_dbo.BusinessLines_dbo.CompanyInfoes_Company_Id]

CREATE TABLE [dbo].[DataFiles](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileStatus] [int] NOT NULL,
    [FileEnvironment] [int] NOT NULL,
    [BusinessLine_Id] [int] NOT NULL,
    CONSTRAINT [PK_dbo.DataFiles] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[DataFiles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.DataFiles_dbo.BusinessLines_BusinessLine_Id] FOREIGN KEY([BusinessLine_Id])
REFERENCES [dbo].[BusinessLines] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[DataFiles] CHECK CONSTRAINT [FK_dbo.DataFiles_dbo.BusinessLines_BusinessLine_Id]

CREATE TABLE [dbo].[DataRecords](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [File_Id] [int] NOT NULL,
    [Field1] [nvarchar](max) NULL,
    [Field2] [nvarchar](max) NULL,
    ... 
    [Field20] [nvarchar](max) NULL,
    CONSTRAINT [PK_dbo.DataRecords] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[DataRecords]  WITH CHECK ADD  CONSTRAINT [FK_dbo.DataRecords_dbo.DataFiles_File_Id1] FOREIGN KEY([File_Id])
REFERENCES [dbo].[DataFiles] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[DataRecords] CHECK CONSTRAINT [FK_dbo.DataRecords_dbo.DataFiles_File_Id1]

示例查询（由EF生成）

SELECT [Distinct1].[Field2] AS [Field2]
    FROM ( SELECT DISTINCT 
        [Extent1].[Field2] AS [Field2]
        FROM  [dbo].[DataRecords] AS [Extent1]
        INNER JOIN [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 = [Extent2].[FileStatus])
    )  AS [Distinct1]

业务规则

• 下拉列表中的值应基于查看用户的BusinessLine访问权限（查询中的 [BusinessLine_Id] 子句），以及搜索与（ [FileEnvironment] 和 [FileStatus] ）一起使用的当前页面 .

• 应将20个DataRecords字段中的哪一个显示为用于搜索的下拉列表，由站点管理员通过管理页面控制，并在公司级别进行配置 . A公司可能有Field1的下拉，B公司可能有Field5，Field7和Field18的下拉，而公司C可能没有任何下拉 .

• 虽然DataRecords的布局和格式在公司之间是一致的，但Field1-Field20的使用情况以及因此值的唯一性却不一致 . 公司A在900k记录中可能有3个Field1的唯一值（因此为什么使用Field1的Dropdown为它们是有意义的），而公司B可能在Field1中为每个DataRecord都有一些独特的东西 .

• 通过EF迁移维护与数据库相关的所有内容，并且该站点设置为在App Startup上自动应用迁移（或者在Azure分段站点的情况下在部署时） . 从数据库角度推荐的任何内容都必须能够通过迁移以编程方式实现，以便可以在没有db访问权限的人员干预的情况下完成站点和数据库的升级或实例化 . 此外，任何需要完成的数据库更改都不应干扰更改模型时创建的CodeFirst迁移（IE无法重命名列，因为存在一些在注释之外添加的恶意索引）l

• 与上一点类似，Dropdown配置是通过站点控制的，因此必须能够在运行时按需添加和删除任何需要完成的操作 .

• 在网站使用过程中发生的相关数据更改，但不一定是当前用户：

• DataFile的FileStatus从0更改为1或2

• 当前用户可以访问哪些BusinessLines更改

• 添加了其他BusinessLines

• 在站点外发生的相关数据更改（通过导入器应用程序，该应用程序也是站点所在解决方案的一部分，因此可以根据需要进行修改）：

• 添加了新的DataFiles和DataRecords

• 添加了其他BusinessLines（不是复制/粘贴错误，也可以通过导入程序添加它们）

其他信息

• 该站点部署到许多位置，但在每个部署中，站点到数据库的比例为1：1 . 因此，内存缓存并非不可能 .

• 只有一个站点管理员可以控制哪些字段表示为下拉列表，并且可以了解如何进行频繁更改，并在必要时对每个更改进行缓存 . 他也熟悉公司级别每个领域的数据，并知道哪些领域是下拉列表的合适候选人 .

• 为了给出一点数据量背景，在短短2.5个月内，一家公司的DataRecords数量从558k增加到924k . 显然，解决方案应该能够处理不断增长的数据量 .

• 将加载值的加载时间卸载到ajax请求以便不占用页面加载是一个很好的解决方案，但不是我可以使用的 .