如何将SQL Server表加载到内存中以便运行查询

Question

我正在 SQL Server 上运行超过 50k 次简单的表查询，我真的很想加快速度。我已经阅读到可以将数据拉入内存并使用类似于 dataview 的东西进行查询。

是否有一种方法可以使用 Linq 得到以下结果 -

var dv = CreateTheDataViewFromDatabase();

var result = dv.Where("CategoryId=2 And Uncle='bob'");

这样做比多次查询数据库快很多吗？我担心dataview上没有索引，因此将其存储在内存中的收益可能会丧失。

- user1266921

---

如果这是一张足够小的表，似乎SQL Server已经将它加载到内存中了......那么你只需要处理通信问题。 - Mark Schultheiss

当客户端加载完数据后，如果有人在此之后添加/更改/删除记录，你将看不到这些变化。那么如何判断客户端的记录已经过期，需要从服务器进行刷新呢？如果现在有20个用户运行您的应用程序，查看100K条记录，并且您正在通过网络将所有数据加载到客户端，那么x年后会怎样呢？我建议您对性能问题进行一些分析。也许您选择了不必要的列，或者问题是在客户端渲染数据，与数据库或网络无关。 - Nick.McDermaid

如果你需要从另一台服务器的SQL表中提取数据，并且希望或需要避免使用链接服务器，那么这是一个很好的使用案例。 - undefined

Answer 1

我认为最快的方法是生成一个单一的查询，按顺序返回整个“50K简单选择”集合的结果，以便您可以将整个查询作为单个查询运行并顺序迭代结果。

原因是当您将表格下载到内存中时，您失去了在SQL Server中可能可用的任何索引信息，以帮助查询结果。当然，惩罚是您的查询返回了50,000个查询的数据，但如果您使用Datareader而不是DataTable，则应该能够避免在客户端上保留不必要的信息。

- Joel Coehoorn

---

如果你正在使用LINQ，我肯定会一次性将整个结果集带入内存中。我有一个命令行应用程序，它执行类似的操作，并运行各种报告，构建Excel文件、XML文件等，所有这些都是基于一个相当广泛的LINQ查询结果集，通常包含30-40k行数据，而且性能相当不错。 - Kevin Dahl

谢谢@Kevin，你处理的时间是几分钟还是几小时？我目前需要花费几个小时，因此需要改进。 - user1266921

我们的处理时间范围为4-5分钟，其中大部分时间都用于基于数据集呈现PDF/XLS报告（有时高达600个）。将数据读取到内存中可能需要约1分钟的总执行时间。 - Kevin Dahl

Answer 2

是的，您可以使用AsEnumerable来对您的DataTable进行Linq操作。例如：

var rows = from row in dt.AsEnumerable()
            where row.Field<int>("CategoryId") == 2 && row.Field<string>("Uncle") == "bob" 
            select row;

请确保您添加对 System.Data.DataSetExtensions 的引用。

更新：动态 SQL

可以动态构建条件。请查看 Dynamic LINQ 和 Predicate Builder。

- Ulises

---

谢谢@Ulises，但我需要动态构建查询，是否可以在运行时将linq where子句链接在一起？ - user1266921

Answer 3

听起来你的程序在与数据库通信时花费了大量时间。如果你只查询一次数据库并将结果保存在计算机的内存中，就可以大大加快进程。如果你正在使用ADO.NET Entity Data Model，那么你可以轻松地查询数据库并将检索到的记录保存在IEnumerable中。然后，你可以使用LINQ对保存的值执行查询。

//Get the data from your database 
public IEnumerable<DataValue> GetDataValues()
{
    try
    {
        using (var db = new MyEntities())
        {
            return (from data in db.DataValues
                   select data).ToList(); 
        }
    }
    catch(Exception e)
    {
        Console.WriteLine(e); 
    }
}


//Perform operations on the data
public void DoSomething()
{
    var data = GetDataValues(); 
    var result = data.Where(p => p.CategoryId == 2 && p.Uncle == "Bob"); 
    //etc...
}

你可以在这里和这里找到一些使用LINQ的不错示例。

- jake

---

那么你如何处理同步问题呢？比如，我正在使用内存进行工作，但数据库已经被更改，现在这两个不再同步了？ - Zapnologica

这就是EntityFramework的用途。http://msdn.microsoft.com/zh-cn/data/ef.aspx - jake

Entity Framework 并不会将您的表存储在内存中，如果每次查询 EF，您都会损失性能。 - Zapnologica

EntityFramework 的 ObjectStateManager 跟踪实体的更改。 您还可以手动标记实体为已修改并将其提交回数据库。 http://msdn.microsoft.com/en-us/library/system.data.objects.objectstatemanager(v=vs.110).aspx - jake

Answer 4

这是可能的，就像其他答案中所示，但有一些需要考虑的事情：

首先，需要拉取的数据总量是多少。如果该数量需要使用虚拟内存，那么操作系统在分页上需要的时间会使速度优势全部丧失。

其次，50k+的查询是很多的。考虑进行设计更改/重构以减少查询量。不同算法的好处可以超越您提出的解决方案。

- lboshuizen

---

@Iboshuizen 是的，我仔细考虑过更改正在发生的事情，但是这50k个人每个人都需要运行一个选择操作，它们都是唯一的，所以在这方面我束手无策。 - user1266921