VB.Net使用DataTable与校验和缓存SQL Server数据的方案咨询

你的缓存设计整体方向是成立的：基于校验和做增量拉取，对变更频率不高的基础业务表来说，能明显降低数据库查询压力、减少网络传输量，也能提升客户端响应速度。但现有实现存在可靠性、性能、逻辑层面的多个问题，具体如下：

现存问题 #

• 校验和逻辑不可靠
  第一，你当前计算校验和用了WITH(NOLOCK) hint，会读取到未提交事务的脏数据，如果后续事务回滚，本地缓存的校验和、数据都会和库端正式数据不一致。第二，你对Connections表的校验是针对基表计算，但实际拉取的是ViewConnectionsOnly视图——如果视图带过滤条件、关联了其他表，会出现两种不一致：基表数据变了但视图输出没变（白跑一次全量拉取），视图依赖的其他关联表变了但基表Connections没动（缓存不更新，数据过时）。第三，CHECKSUM_AGG+BINARY_CHECKSUM存在理论上的哈希碰撞概率，虽然日常业务场景下概率极低，但如果是对数据一致性要求极高的场景，不能100%依赖它做变更判断。
• 性能瓶颈完全来自关联实现，不是LINQ本身的问题
  你现在JoinTables里逐行调用DataTable.Select()做关联匹配，本质是每匹配一个字段就对维度表做一次全表扫描，时间复杂度是O(N*M)，17000条主表记录乘三个维度表的行数，性能差是必然的，比LINQ转CopyToDataTable慢也很正常。另外你现在只要任意一个表校验和变化，就全量遍历所有主表记录重算关联字段，哪怕只是VLAN表改了一条描述，也要扫完17000行，做了很多无用功。还有当前校验逻辑是每个表单独开一次数据库连接跑校验命令，4个表就开4次连接，存在不必要的开销。
• 存在显性逻辑bug
  构造函数里只初始化了Connections表的校验配置项，Buildings、Switches、VLANs三个表的校验行没有写入TblCheckRetrieve，第一次执行RetrieveData时会因为找不到对应CheckRow直接抛索引越界异常。关联取值时直接取Select()结果的第0行，没有判断是否存在匹配记录，一旦库里面有外键缺失的脏数据，会直接崩溃。主表的关联字段在重填数据后没有清空旧值，如果新拉的数据存在匹配不到的关联项，会残留上一次的旧值，导致数据错误。

优化方案 #

• 修正校验和逻辑
  所有表的校验和计算逻辑，要和实际拉取数据的查询逻辑完全一致：比如Connections的校验和就针对ViewConnectionsOnly的返回结果计算，不要直接算基表。去掉NOLOCK hint，如果怕读操作阻塞写，可以开启数据库的读提交快照隔离级别，既避免脏读，也不会产生读锁。如果对一致性要求极高，可以在校验和匹配后加一个基于行版本ROWVERSION的二次校验，把哈希碰撞的影响降到最低。
• 把关联逻辑的时间复杂度降到O(N)
  不要用DataTable.Select逐行查找，关联前先把三个维度表遍历一次转成字典，后续匹配直接走字典O(1)查找，17000条数据的关联耗时会降到毫秒级，参考实现：

  Private Sub JoinTables()
      ' 预先把维度表转成字典，每个维度表只遍历1次
      Dim buildingMap As New Dictionary(Of Integer, String)
      For Each bRow As DataRow In TblBuildings.Rows
          Dim bid = Convert.ToInt32(bRow("BuildingID"))
          If Not buildingMap.ContainsKey(bid) Then buildingMap.Add(bid, bRow("Building").ToString())
      Next
  
      Dim switchMap As New Dictionary(Of Integer, String)
      For Each sRow As DataRow In TblSwitches.Rows
          Dim sid = Convert.ToInt32(sRow("SwitchID"))
          If Not switchMap.ContainsKey(sid) Then switchMap.Add(sid, sRow("Switch").ToString())
      Next
  
      Dim vlanMap As New Dictionary(Of Integer, String)
      For Each vRow As DataRow In TblVLANs.Rows
          Dim vid = Convert.ToInt32(vRow("VLANID"))
          If Not vlanMap.ContainsKey(vid) Then vlanMap.Add(vid, vRow("VLAN").ToString())
      Next
  
      ' 遍历主表做字段填充，全程O(1)匹配
      For Each aRow As DataRow In TblConnections.Rows
          Dim bid = Convert.ToInt32(aRow("BuildingID"))
          aRow("Building") = If(buildingMap.ContainsKey(bid), $"{bid}-{buildingMap(bid)}", bid.ToString())
          
          Dim sid = Convert.ToInt32(aRow("SwitchID"))
          aRow("Switch") = If(switchMap.ContainsKey(sid), switchMap(sid), "")
          
          Dim vid = Convert.ToInt32(aRow("VLANID"))
          aRow("VLAN") = If(vlanMap.ContainsKey(vid), vlanMap(vid), "")
      Next
      ConnectionsRowCount = TblConnections.Rows.Count
  End Sub
  

  额外加一层判断：如果只是三个维度表的校验和变化，不需要重拉主表TblConnections，直接执行Join逻辑即可；只有主表本身校验和变化时，才需要重拉主表数据，能进一步减少开销。
• 补全逻辑细节
  构造函数里把四个表的校验配置全部初始化到TblCheckRetrieve中，不要遗漏。把四个表的校验查询合并成一个SQL命令，一次数据库连接就能拿到所有校验和，减少连接开销。填充DataTable前调用BeginLoadData()，填充完调用EndLoadData()，临时关闭约束、索引校验，提升填充速度。
• 补充说明：LINQ本身性能并不差，你之前遇到的LINQ转CopyToDataTable性能问题，本质也是没有对关联键做哈希索引导致的，和用不用LINQ没有直接关系。

内容的提问来源于stack exchange，提问作者PaulvdElst