如何从SQL Server缓存一个大表

本文关键字：一个 SQL Server 缓存 | 更新日期: 2023-09-27 18:10:15

我有一个表，有很多行(300万)，我需要在我的应用程序中的几个点查询一些行。我发现这样做的方式是第一次查询所有数据，任何需要并将其存储在static DataTable与SqlAdapter.Fill()中，用于应用程序的其余生命周期。

这是快速的，因为当我需要的东西，我使用DataTable.Select("some query")和应用程序处理的信息很好。

问题是这个表占用了大约800MB的内存，我必须在pc上运行这个应用程序，这可能太大了。

我认为另一种方法是每次查询我需要的数据。这只占用很少的内存，但性能很差(对数据库的查询很多，数据库位于一个网络地址，并且有1000次查询，您开始注意到ping和所有这些…)。

是否存在性能和内存使用之间的中间点?

编辑:我正在检索的是sales，它有日期、产品和数量。我按乘积查询，它不是那样索引的。但是，无论如何，进行1000次查询，即使查询花费0.05s，一个0.2s的ping总共花费200秒……

如何从SQL Server缓存一个大表

首先和dba谈谈性能

如果您下载整个表，实际上可能会给网络和SQL带来比执行单个查询更多的负载。

作为一名dba，如果我知道你要下载一个大表，我会立即为product添加索引。

为什么要执行1000次查询?

如果在创建产品时查找销售额，则缓存有问题。您还没有销售数据。缓存的问题是数据陈旧。如果你知道数据不会改变——你要么有要么没有——那么你就可以消除对陈旧数据的担忧。

在顺序和同时之间存在某种东西。您可以在单个请求中打包多个选择。这样做的是进行一次往返，效率更高。

select * from tableA where ....;  
select * from tableB where ....;

使用DataReader只需调用SqlDataReader。NextResult Method ()

using (SqlDataReader rdr = cmd.ExecuteReader())
{
   while (rdr.Read())
   {
   }
   rdr.NextResultSet();
   while (rdr.Read())
   {
   }
}

我很确定你可以在一个数据集中用多个数据表做同样的事情。

另一个选项是LocalDB。它是针对开发人员的，但对于你正在做的事情来说，它会工作得很好。没有内存问题的数据表速度。您甚至可以在ProductID上添加索引。与内存相比，写入磁盘将花费更长的时间，但您没有耗尽内存。

然后是永远邪恶的(nolock)。知道你在做什么，我不会详细介绍所有可能的弊端，但我可以告诉你，我经常使用它。

这个问题可以归结为内存与性能。答案是缓存

如果你知道你的使用模式是什么样的，那么你可以做的一件事就是在应用程序中创建一个本地缓存。

极端的情况是-你的缓存大小为800MB，所有的数据都在其中(从而牺牲内存)-或者-你的缓存大小为0MB，所有的查询都去网络(从而牺牲性能)。

关于缓存设计的三个重要问题的答案如下:

如何填充缓存?

  1. 如果你可能会多次进行一些查询，将其存储在缓存中，在进入网络之前，测试你的缓存是否已经有结果。如果没有，查询数据库，然后将结果存储在缓存中。
  2. 如果在查询一些数据之后，您可能会查询下一个和/或前一个数据块，那么查询所有数据一次并缓存它，以便当您查询下一个数据块时，您已经在缓存中了。

有效的想法是，如果你知道一些信息将来可能需要，提前缓存它。

如何释放缓存?

你可以决定缓存的释放机制是主动还是被动。

被动:当缓存已满时，可以从缓存中取出数据。

active:定期运行一个后台线程，它会为你清除。

一种混合方法是，当你达到内存限制的80%时，运行一个释放线程，然后释放任何你能释放的内存。

要从缓存中删除哪些数据?

这个问题已经在操作系统的页面替换策略问题的上下文中得到了回答。

为了完成，我将在这里总结重要的:

  1. 清除最近最少使用的数据(如果它不太可能被使用);
  2. 退出最早引入的数据(如果最早的数据不太可能被使用);
  3. 退出最近引入的数据(如果您认为新引入的数据最不可能被使用)。
  4. 自动删除小于t时间单位的数据

RE: "我不能索引任何东西，因为我不是数据库管理员，也不能要求。"

你能预先填充一个临时表和索引吗?,例如

Select * into #MyTempTable from BigHugeTable 
Create Index Prodidx on #MyTempTable (product)

为了使用临时表，您必须确保始终重用相同的连接(并且它没有关闭)。

相关文章：