使用 SQL Server EF6 保存静态列表的最佳方法

本文关键字：列表 最佳 方法 静态 保存 SQL Server EF6 使用 | 更新日期: 2023-09-27 18:32:04

我正在开始重写现有应用程序。 当前性能瓶颈之一是将用户生成的员工列表保存到静态列表中，以便他们以后可以返回并查看相同的员工列表。

下面是我正在寻找的功能性的简单示例。 生成的列表将由比示例中更复杂的查询生成。

方案： 用户搜索夜班的所有员工，并希望保存此列表以便稍后加载。 他们希望列表始终返回结果，因为这是他们第一次运行搜索。 也就是说，如果新员工被添加到夜班，当他们拉起它时，他们不应该出现在名单上。

我尝试过的：

目前，有一个非常糟糕的解决方案，即将结果列表中的所有 ID 存储为字符串数组，然后使用这些 ID 重建查询。 这是非常低效的，并且会导致具有太多参数的大型列表出现问题。

我还尝试过从保存的 ID 数组构建一个表，然后加入，但是，这在大型列表（20,000+ 员工）上非常慢，并且经常导致超时。

我目前的想法是为每个列表创建一个新表，然后在该表和 Employee 表上调用 JOIN。 但是，如果 100 个用户每人保存 10 个大型列表（20,000+ 名员工），它很快就会成为存储和表管理的噩梦。

我认为这是一个相当普遍的解决方案问题。 但是，我无法找到有关如何存储静态列表的任何示例或最佳实践（我可能正在寻找错误的东西）。 有没有人对如何最好地处理这种类型的用例场景有任何一般概念？

更新：我想我之前尝试过以下设置，但由于某种原因它不起作用，这是几年前的事了;但回过头来看，这似乎最有意义。 我认为我遇到的问题是 NHibernate 在 Linq 中的子选择方面存在问题。 但是，这不再是一个限制。

我想我有一个StaticSavedLists表和索引表，链接人员（在前面的示例中为员工），在多对多映射中将列表链接到员工。c# 中的类如下所示：

public class StaticSavedList : BaseModel
{
    public string Name { get; set; }
    public IList<StaticSavedListPersonIdx> PersonsIdx { get; set; } //Has many persons
}
public class StaticSavedListPersonIdx : BaseModel
{
    public StaticSavedList StaticSavedList { get; set; }
    public Person Person { get; set; }
}

使用 SQL Server EF6 保存静态列表的最佳方法

您可能需要有一个包含搜索"标题"详细信息的表，其中将包含搜索的 ID，然后是包含每个搜索条目的第二个表。然后，您只需要以某种方式获取 ID（可能是通过用户 ID 和轮班日期）并使用它来连接结果和员工表。

下面是架构的外观

班次搜索 

SearchID    int (PK)
ShiftDate   datetime

搜索结果

SearchID    int (PK)
EmployeeID  int (PK)

员工

EmployeeID  int (PK)
FirstName   varchar
etc ...

可能的 LINQ 查询

DateTime shiftDate = new DateTime(2014,11,26);
int searchId = db.ShiftSearches.Single(s => s.ShiftDate == shiftDate).SearchID;
var results = from r in db.SearchResults where r.SearchID == searchId
    join e in Employees on r.EmployeeID equals e.EmployeeID
    select e;

这样，您只需要一个表，而且它非常薄，因此不应占用太多空间 - 因为您只是存储所需的搜索和员工 ID，因此无论如何您都无法真正缩小数据。

您发布的类结构几乎符合此概念。

实体框架可能并非在所有情况下都适合选择技术，一次处理 20k 行的批处理可能是其中一种情况。

但是，我相信您对数据模型的设计是好的。下面，在 Sql Only 上，可以显示包含(ListId, EmployeeId)对的 60k 行可以在一秒钟内插入到合理聚类的ListSearchEmployee表中，随后，20k 行列表之一可以在冷启动后的 1.8 秒内重新连接到完整的Employee行。

性能瓶颈更有可能是原始用户搜索 - 据推测，这可能是针对 Employees + 相关表执行的近乎任意的查询，很难为所有排列编制索引。

一些性能建议（针对列表保存 + 重新提取）：

  • 使用SqlBulkCopy将 EmployeeId 大容量转储到列表表中
  • 使用一些低级别的东西，比如基本SqlReader来获取 UI 的列表数据（尽管我想它会被分页吗？ - 如果是这样，关闭AsNoTracking() DbSet.SqlQuery可能就足够了。
  • 不要打扰列表表上的外键 - 这会减慢插入速度。
  • 不要尝试同步清理不需要的旧列表搜索 - 将它们排队等待删除，并有一个后台进程来执行删除。
  • 因此，ListSearchEmployee表由于有大量改动而经常需要重新编制索引。
-- Sample data setup - not considered in the timing
CREATE TABLE Employee
(
  EmployeeID INT identity(1,1),
  Name NVARCHAR(100),
  SomeOtherFieldToLessenTheDensityOfEmployee CHAR(500),
  PRIMARY KEY(EmployeeID)
);
CREATE TABLE ListSearch
(
  ListSearchID INT IDENTITY(1,1) PRIMARY KEY
  -- Other fields you may want to identify the search, e.g. date, which user, which filters etc
)
CREATE TABLE ListSearchEmployee
(
  ListSearchID INT,
  EmployeeID INT, -- Don't bother Foreign Keying for performance
  PRIMARY KEY CLUSTERED (ListSearchID, EmployeeID)
);
-- Insert 1M Employees
WITH cteData AS
(
   SELECT top 1000000 sc1.name, ROW_NUMBER() OVER (ORDER BY sc1.object_id) AS rn
   FROM sys.columns sc1 CROSS JOIN sys.columns sc2 CROSS JOIN sys.columns sc3
)
INSERT INTO Employee(Name)
SELECT name + CAST(rn AS VARCHAR)
FROM cteData;
-- Timing : 0.972 seconds on SQLExpress 2012 on an i3
-- Inserting 3 x 20 k lists of pseudo random employees (but not contigious on the EmployeeId Cluster)
WITH cteData AS
(
   SELECT top 20000 1 as listid, ROW_NUMBER()  OVER (ORDER BY sc1.object_id) * 50 AS empid
   FROM sys.columns sc1 CROSS JOIN sys.columns sc2
  UNION ALL
   SELECT top 20000 2 as listid, ROW_NUMBER()  OVER (ORDER BY sc1.object_id) * 30 AS empid
   FROM sys.columns sc1 CROSS JOIN sys.columns sc2
  UNION ALL
   SELECT top 20000 3 as listid, ROW_NUMBER()  OVER (ORDER BY sc1.object_id) * 41 AS empid
   FROM sys.columns sc1 CROSS JOIN sys.columns sc2
)
INSERT INTO ListSearchEmployee(ListSearchID, EmployeeID)
SELECT listid, empid
FROM cteData;
DBCC DROPCLEANBUFFERS;
-- Timing : 1.751 seconds on SQLExpress 2012 on an i3
-- Joining 20k rows
SELECT * 
FROM ListSearchEmployee el INNER JOIN Employee e on el.EmployeeID = e.EmployeeID
WHERE el.ListSearchID = 2
相关文章：