在.net中构建大型SQL行集和消费

本文关键字：SQL net 构建 大型 | 更新日期: 2023-09-27 18:15:28

看一下这个psuedo模式(请注意，这个是的简化，所以请尽量不要过多地评论模式本身的"可取性")。假设索引在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)
 )

Volumes: Document, 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?)执行一些优化吗?在我看来，对数据进行反规范化的查询是非常常见的，因此字段值从一行到下一行不改变的可能性很高，因此通过不发送未改变的字段值来优化流是有意义的。有人知道这样的优化是否到位吗?(优化似乎不存在)。

我可以使用什么更复杂的查询来消除重复(我认为特别是重复name值)?我听说过"嵌套行集"这样的东西，这种东西可以生成吗?它会更高效吗?我如何在。net中访问它?

我将执行两个查询;一个用来填充查找字典，第二个用来填充字典列表。然后，我将添加代码以删除未使用的Lookup条目。然而，想象一下，我的预测是错误的，查找结果是100万行，其中只有四分之一被任何文档引用?

在.net中构建大型SQL行集和消费

  • 只要名称在实践中相对较短，则可能不需要优化。

  • 最简单的优化是将其拆分为两个查询，一个获取名称，另一个获取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>

当您询问"嵌套行集"时，您是指使用DbDataReader.NextResult()方法吗?

如果您的查询有两个"输出"(两个返回单独结果集的选择语句)，您可以使用DbDataReader.Next()循环通过第一个，当它返回"false"时，您可以调用DbDataReader.NextResult()，然后使用DbDataReader.Next()再次继续。

var reader = cmd.ExecuteReader();
  while(reader.Read()){
    // load data
  }
  if(reader.NextResult()){
    while(reader.Read()){
      // lookup record from first result
      // load data from second result
    }
  }

在类似的情况下，我经常这样做来减少重复的数据，它真的很有效:

SELECT * FROM tableA WHERE [condition]
SELECT * FROM tableB WHERE EXISTS (SELECT * FROM tableA WHERE [condition] AND tableB.FK = tableA.PK)

免责声明:我没有在你描述的那么大的结果集上尝试过。

这样做的缺点是您需要一种方法将第二个结果集映射到第一个结果集，使用散列表或顺序列表。

相关文章：