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

请看一下这个psuedo模式（请注意，这是一个简化，所以请不要对模式本身的“可取性”做过多的评论）。假设索引位于FKS上。

 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%的前一个\u id字段值为空；查找，6000行，每个文档20附加的平均查找提供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？）进行一些优化？在我看来，使数据非标准化的查询非常常见，因此字段值不会从一行更改到下一行的可能性很高，因此通过不发送未更改的字段值来优化流是有意义的。有人知道这种视错觉是否存在吗？（OpTimoStudio似乎不存在）。
我可以使用什么更复杂的查询来消除重复（我特别考虑重复name值）？我听说过这样一个东西“嵌套行集”，这种东西可以生成吗？会不会更有表现力？如何在.net中访问它？
我将执行两个查询：一个用于填充查找字典，另一个用于填充附加列表。然后我将添加代码来删除未使用的查找实体。然而，假设我的预测是错的，查找结果是100万行，只有四分之一被任何文档引用？

最佳答案

只要名称在实际中相对较短，就可能不需要进行优化。
最简单的优化是将它分为两个查询，一个用于获取名称，另一个用于获取文档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提供程序。它们可以按照您的建议执行操作，但会限制您使用OLEDB SQL提供程序，这可能不是您想要的。
最后考虑仔细的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>