c# - 构建大型 SQL 行集并在 .NET 中使用

Question

看看这个伪模式(请注意这是一个简化，所以请尽量不要对模式本身的“可取性”发表太多评论)。假设索引在 FK 上就位。

 TABLE Lookup (
     Lookup_ID int not null PK
     Name nvarchar(255) not null
 )

 TABLE Document (
     Document_ID int not null PK
     Previous_ID null FK REFERENCES Document(Document_ID)
 )

 TABLE Document_Lookup (
     Document_ID int not null FK REFERENCES Document(Document_ID)
     Lookup_ID int not null FK REFERENCES Lookup(Lookup_ID)
 )

卷:文档，400 万行，其中 90% 的 Previous_ID 字段值为空；查找，6000 行，附加到每个文档的平均查找 20 给 Document_Lookup 8000 万行。

现在在 .NET 服务中有结构来表示这样的查找行:-

 struct Lookup
 {
      public int ID;
      public string Name;
      public List<int> DocumentIDs;
 }

并且查找行存储在 Dictionary<int, Lookup> 中其中键是查找 ID。这里很重要的一点是，该词典应包含至少一个文档引用查找的条目，即列表 DocumentIDs应该有 Count > 0。

我的任务是有效地填充这本字典。所以简单的方法是:-

  SELECT dl.Lookup_ID, l.Name, dl.Document_ID
  FROM Document_Lookup dl
  INNER JOIN Lookup l ON l.Lookup_ID = dl.Lookup_ID
  INNER JOIN Document d ON d.Document_ID = dl.Lookup_ID
  WHERE d.Previous_ID IS NULL
  ORDER BY dl.Lookup_ID, dl.Document_ID

这可以用来相当有效地填充字典。

问题: 底层行集交付(TDS？)是否执行了一些优化？在我看来，对数据进行去规范化的查询非常普遍，因此字段值不会从一行更改到下一行的可能性很高，因此通过不发送没有的字段值来优化流是有意义的改变了。有谁知道这样的优化是否到位？ (优化似乎不存在)。

我可以使用什么更复杂的查询来消除重复(我特别想重复名称值)？我听说过“嵌套行集”这样的东西，可以生成那种东西吗？它会更高效吗？我如何在 .NET 中访问它？

我会执行两个查询；一个填充 Lookup 字典，然后第二个填充 ditionary 列表。然后我会添加代码来删除未使用的 Lookup entires。但是想象一下我的预测是错误的，Lookup 最终是 100 万行，而任何文档实际上只引用了四分之一？

Answer 1

• 只要名称在实践中比较短，优化就不一定了。

• 最简单的优化是将其拆分为两个查询，一个获取名称，另一个获取 Document_ID 列表。 (如果可以更轻松地填充数据结构，则可以采用其他顺序)。

例子:

/*First get the name of the Lookup*/
select distinct dl.Lookup_ID, l.Name
FROM Document_Lookup dl 
INNER JOIN Lookup l ON l.Lookup_ID = dl.Lookup_ID 
INNER JOIN Document d ON d.Document_ID = dl.Lookup_ID 
WHERE d.Previous_ID IS NULL 
ORDER BY dl.Lookup_ID, dl.Document_ID 

/*Now get the list of Document_IDs for each*/
SELECT dl.Lookup_ID, dl.Document_ID 
FROM Document_Lookup dl 
INNER JOIN Lookup l ON l.Lookup_ID = dl.Lookup_ID 
INNER JOIN Document d ON d.Document_ID = dl.Lookup_ID 
WHERE d.Previous_ID IS NULL 
ORDER BY dl.Lookup_ID, dl.Document_ID 

• 您还可以使用各种技巧将它们整合到一张表中，但我认为这些都是不值得的。

• 您想到的分层行集是 MSDASHAPE OLEDB 提供程序。他们可以按照您的建议进行操作，但会限制您对 SQL 使用 OLEDB 提供程序，这可能不是您想要的。

• 最后仔细考虑XML

例如:

select
  l.lookup_ID as "@l", 
  l.name as "@n",
  (
    select dl.Document_ID as "node()", ' ' as "node()" 
    from Document_Lookup dl where dl.lookup_ID = l.lookup_ID for xml path(''), type
  ) as "*"
  from Lookup l
  where l.lookup_ID in (select dl.lookup_ID from Document_Lookup dl)
  for xml path('dl')

返回:

<dl l="1" n="One">1 2 </dl>
<dl l="2" n="Two">2 </dl>