Speed up retrieval of distinct values for dropdown via caching

Question

Overview

In my ASP.Net MVC application, I have several pages that utilize a DataRecord search functionality that is dynamically configured by the site admin to have specific DataRecord fields available as criteria in one of a few different search input types. One of the input types available is a dropdown, which is populated with the distinct DataRecord values of that particular field that are relevant to whatever the search context is.

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

I'll list out things in the following manner:

1. SQL Structure
2. Sample Query
3. Business Rules
4. Miscellaneous Info (may or may not be relevant, but I didn't want to rule anything out)

SQL Structure

Listed from greatest to lowest scope, with only relevant fields. Each table has a one to many relationship with the table that follows. Keep in mind these were all created and maintained via 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]

Sample Query (as generated by 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]

Business Rules

1. The values within the Dropdown should be based on the viewing User's BusinessLine access ([BusinessLine_Id] clause in query), and the current page that the search is being used in conjunction with ([FileEnvironment] and [FileStatus]).
2. Which of the 20 DataRecords Fields should be presented as a Dropdown for searching is controlled by a site admin via an admin page, and is configured at a company level. Company A may have a Dropdown for Field1, Company B may have one for Field5, Field7, and Field18, and Company C may not have any Dropdowns what so ever.
3. While the layout and format of the DataRecords is consistent from company to company, the usage, and therefore the uniqueness of values, of Field1 - Field20 is not. Company A may have 3 unique values for Field1 across 900k records (hence why it makes sense to use a Dropdown for Field1 for them), while Company B may have something unique in Field1 for every DataRecord.
4. Everything database related is maintained via EF Migrations, and the site is set to auto apply migrations on App Startup (or on Deploy in the case of the Azure staging site). Anything that is recommended from a database perspective must be able to be implemented programmatically through migrations, so that the upgrading or instancing of the site and database may be done without manual intervention by someone with db access. Also, any database changes that need to be done should be not interfere with CodeFirst Migrations that are created when models are changed (IE cannot rename a column because some rogue index that was added outside of annotations exists)l
5. Similarly to the previous point, the Dropdown configuration is controlled via the site, so anything that needs to be done must be able to be added and removed on demand at runtime.
6. Relevant data changes that occur within usage of the site, but not necessarily by the current user:
   1. FileStatus of a DataFile changes from 0 to 1 or 2
   2. Which BusinessLines the current user can access changes
   3. Additional BusinessLines are added
7. Relevant data changes that occur outside of the site (via importer app which is also part of the solution that the site is in and therefore can be modified if necessary):
   1. New DataFiles and DataRecords are added
   2. Additional BusinessLines are added (not a copy/paste error, they can be added through the importer as well)

Miscellaneous Info

1. The site is deployed to many locations, but in each deployment, the site to database is 1:1. So an in-memory caching is not out of the question.
2. There is only one Site Admin that controls which fields are represented as Dropdowns, and he can be educated about ramifications of making frequent changes and the caching each change may result in if necessary. He is also familiar with the data in each field at a Company level, and knows which fields are good candidates for Dropdowns.
3. Just to give a little data quantity context, in just over 2.5 months, the number of DataRecords for one company went from 558k to 924k. So obviously the solution should be able to work with an ever-growing amount of data.
4. Offloading the load time of loading of the values to an ajax request as to not hold up the page load is a good solution in general, but not one I can use for this.

Answer 1

Two quick items that jump out here would be

1) to add the Field2 column that is being returned, as an INCLUDE in the CLUSTERED INDEX on the DataRecords table. That will keep it from needing to do a bookmark lookup to find the Field2 after the ON clause has done the main work of finding the ID's.

2) Not sure why there is an double select happening. I don't think it would be a big impact, but the query is just reselecting what it selected as distinct, not even changing the name...