如何避免多次数据库往返和大量不相关的数据？

Question

我曾使用过各种应用程序并多次遇到这种情况。直到现在我还没有弄清楚什么是最好的方法。

场景如下：

• 我有一个桌面或网络应用程序，
• 我需要从数据库中检索简单的文档。该文档具有一般详细信息和项目详细信息，因此数据库：

GeneralDetails 表：

| DocumentID | DateCreated | Owner     |
| 1          | 07/07/07    | Naruto    |
| 2          | 08/08/08    | Goku      |
| 3          | 09/09/09    | Taguro    |

ItemDetails 表

| DocumentID | Item        | Quantity  |
| 1          | Marbles     | 20        |
| 1          | Cards       | 56        |
| 2          | Yo-yo       | 1         |
| 2          | Chess board | 3         |
| 2          | GI Joe      | 12        |
| 3          | Rubber Duck | 1         |

如您所见，这些表具有一对多关系。现在，为了检索所有文档及其各自的项目，我总是执行以下两种操作之一：

方法 1 - 多次往返（伪代码）：

 Documents = GetFromDB("select DocumentID, Owner " +
                       "from GeneralDetails") 
 For Each Document in Documents
{
    Display(Document["CreatedBy"])
    DocumentItems = GetFromDB("select Item, Quantity " + 
                              "from ItemDetails " + 
                              "where DocumentID = " + Document["DocumentID"] + "")
    For Each DocumentItem in DocumentItems
    {
        Display(DocumentItem["Item"] + " " + DocumentItem["Quantity"])
    }
}

方法 2 - 大量不相关的数据（伪代码）：

DocumentsAndItems = GetFromDB("select g.DocumentID, g.Owner, i.Item, i.Quantity " + 
                              "from GeneralDetails as g " +
                              "inner join ItemDetails as i " +
                              "on g.DocumentID = i.DocumentID")
//Display...

我使用第一种我在大学时使用桌面应用程序时的方法，性能还不错，所以我意识到还可以。

直到有一天，我看到一篇文章“让网络更快”，它说多次往返数据库是不好的；所以从那时起我就使用了第二种方法。

在第二种方法中，我通过使用内部联接同时检索第一个和第二个表来避免往返，但它会产生不必要或冗余的数据。查看结果集。

| DocumentID | Owner     | Item        | Quantity  |
| 1          | Naruto    | Marbles     | 20        |
| 1          | Naruto    | Cards       | 56        |
| 2          | Goku      | Yo-yo       | 1         |
| 2          | Goku      | Chess board | 3         |
| 2          | Goku      | GI Joe      | 12        |
| 3          | Taguro    | Rubber Duck | 1         |

结果集具有冗余的DocumentID和Owner。它看起来像一个非规范化的数据库。

现在的问题是，如何避免往返，同时避免冗余数据？

Answer 1

ActiveRecord 和其他 ORM 使用的方法是选择第一个表，将 ID 批处理在一起，然后在 IN 子句中使用这些 ID 进行第二次选择。

SELECT * FROM ItemDetails WHERE DocumentId IN ([此处以逗号分隔的 ID 列表])

优点：

• 没有冗余数据

缺点：

• 两次查询

一般来说，第一种方法称为“N+1 查询问题”，解决方案参考作为“渴望加载”。我倾向于认为您的“方法 2”更可取，因为数据库的延迟通常胜过数据传输速率上的冗余数据的大小，但 YRMV。与软件中的几乎所有事物一样，这是一种权衡。

Answer 2

内连接更好，因为数据库有更多优化的可能性。

一般来说，您不能创建这样的查询，它不会产生冗余结果。为此，关系模型的限制太多。我会接受它：数据库负责优化这些情况。

如果您确实遇到性能问题（主要是由于网络瓶颈），您可以编写一个存储过程，该存储过程进行查询并将其非规范化。在您的示例中，您创建了一个如下结果：

| DocumentID | Owner     | Items                                   | Quantity    |
| 1          | Naruto    | Marbles, Cards                          | 20, 56      |
| 2          | Goku      | Yo-yo, Chess board, GI Joe, Rubber Duck | 1, 3, 12, 1 |

但这当然不符合第一个范式 - 因此您需要在客户端上解析它。
如果您使用支持 XML 的数据库（例如 Oracle 或 MS SQL Server），您甚至可以在服务器上创建 XML 文件并将其发送到客户端。

但无论你做什么，请记住：过早的优化是万恶之源。在你不能100%确定你确实面临着一个可以这样解决的问题之前，不要做这种事情。

Answer 3

您可以读取第一个表，从第二个表中提取所需行的键，然后通过第二个选择检索它们。

像这样的东西

DocumentItems = GetFromDB("select Item, Quantity " + 
                          "from ItemDetails " + 
                          "where DocumentID in (" + LISTING_OF_KEYS + ")")

Answer 4

你的第二种方法绝对是一个可行的方法。
但您不必选择不打算使用的列。
因此，如果您只需要 Item 和 Quantity，请执行以下操作：（

DocumentsAndItems = GetFromDB("select i.Item, i.Quantity " + 
                          "from GeneralDetails as g " +
                          "inner join ItemDetails as i " +
                          "on g.DocumentID = i.DocumentID")

我想您还有其他条件可以放入查询的 where 部分，否则不需要加入。）

Answer 5

如果您使用 .NET 和 MS SQL Server，这里的简单解决方案是考虑使用 MARS（多个活动结果集）。下面是直接从 MARS 演示的 Visual Studio 2015 帮助中提取的示例代码块：

using System;
using System.Data;
using System.Data.SqlClient;

class Class1
{
  static void Main()
  {
     // By default, MARS is disabled when connecting
     // to a MARS-enabled host.
     // It must be enabled in the connection string.
     string connectionString = GetConnectionString();

     int vendorID;
     SqlDataReader productReader = null;
     string vendorSQL = 
       "SELECT VendorId, Name FROM Purchasing.Vendor";
     string productSQL = 
       "SELECT Production.Product.Name FROM Production.Product " +
       "INNER JOIN Purchasing.ProductVendor " +
       "ON Production.Product.ProductID = " + 
       "Purchasing.ProductVendor.ProductID " +
       "WHERE Purchasing.ProductVendor.VendorID = @VendorId";

   using (SqlConnection awConnection = 
      new SqlConnection(connectionString))
   {
      SqlCommand vendorCmd = new SqlCommand(vendorSQL, awConnection);
      SqlCommand productCmd = 
        new SqlCommand(productSQL, awConnection);

      productCmd.Parameters.Add("@VendorId", SqlDbType.Int);

      awConnection.Open();
      using (SqlDataReader vendorReader = vendorCmd.ExecuteReader())
      {
        while (vendorReader.Read())
        {
          Console.WriteLine(vendorReader["Name"]);

          vendorID = (int)vendorReader["VendorId"];

          productCmd.Parameters["@VendorId"].Value = vendorID;
          // The following line of code requires
          // a MARS-enabled connection.
          productReader = productCmd.ExecuteReader();
          using (productReader)
          {
            while (productReader.Read())
            {
              Console.WriteLine("  " +
                productReader["Name"].ToString());
            }
          }
        }
      }
      Console.WriteLine("Press any key to continue");
      Console.ReadLine();
    }
  }
  private static string GetConnectionString()
  {
    // To avoid storing the connection string in your code,
    // you can retrive it from a configuration file.
    return "Data Source=(local);Integrated Security=SSPI;" + 
      "Initial Catalog=AdventureWorks;MultipleActiveResultSets=True";
  }
 }

希望这能让您走上理解之路。关于往返的主题有许多不同的理念，其中很大程度上取决于您正在编写的应用程序的类型以及您要连接的数据存储。如果这是一个 Intranet 项目并且没有大量的并发用户，那么大量的数据库往返并不是您认为的问题或担忧，除非您的声誉看起来没有这样做更精简的代码！ （咧嘴笑）
如果这是一个 Web 应用程序，那么情况就不同了，您应该尽量确保不会过于频繁地返回井（如果可以避免的话）。 MARS 是解决此问题的一个很好的答案，因为所有内容都会一次性从服务器返回，然后由您来迭代返回的数据。
希望这对您有用！

Answer 6

答案取决于您的任务。

1. 如果您想生成列表/报告，那么您需要具有冗余数据的方法2。您可以通过网络传输更多数据，但可以节省生成内容的时间。

2. 如果您想先显示常规列表，然后通过用户点击显示详细信息，那么最好使用方法1。生成和发送有限的数据集将会非常快。

3. 如果您想将所有数据预加载到应用程序中，则可以使用 XML。它将提供所有非冗余数据。但是，还需要进行额外的编程，即在 SQL 中进行 XML 编码并在客户端进行解码。

我会做这样的事情来在 SQL 端生成 XML：

;WITH t AS (
    SELECT g.DocumentID, g.Owner, i.Item, i.Quantity
    FROM GeneralDetails AS g
    INNER JOIN ItemDetails AS i 
    ON g.DocumentID = i.DocumentID
)
SELECT 1 as Tag, Null as Parent, 
    DocumentID as [Document!1!DocumentID],
    Owner as [Document!1!Owner],
    NULL as [ItemDetais!2!Item],
    NULL as [ItemDetais!2!Quantity]
FROM t GROUP BY DocumentID, Owner
UNION ALL
SELECT 2 as Tag, 1 as Parent, DocumentID, Owner, Item, Quantity
FROM t 
ORDER BY [Document!1!DocumentID], [Document!1!Owner], [ItemDetais!2!Item], [ItemDetais!2!Quantity]
FOR XML EXPLICIT;

Answer 7

据我所知，您有很多选项

1. 可以连接字符串，以便所有项目都不会出现多余的数据。即“Marbles, Cards”
2. 将您的查询作为压缩的 XML 文件返回，然后您的程序可以像解析数据库一样对其进行解析。
   • 这样一来，您不仅可以享受一次旅行的优势，而且还可以在一个文件中获取可能数量庞大的所有数据。
3. 这个项目是我个人的偏好，实现一种延迟加载的形式。
   • 这意味着仅在需要时加载“附加”数据。因此，虽然这确实有多个行程，但这些行程只是为了获取所需的数据。

Answer 8

不知何故，在我的应用程序中，大约有 200 个表单/屏幕和一个大约有 300 个表的数据库，我从来不需要第一种方法或第二种方法。

在我的应用程序中，用户经常在屏幕上看到两个网格（表格），彼此相邻：

• main GeneralDetails 表，其中包含文档列表（通常有搜索功能，使用一堆各种过滤器）。

• 来自选定文档的 ItemDetails 表的数据。不适用于所有文档。仅针对一份当前文档。当用户在第一个网格中选择不同的文档时，我（重新）运行查询以检索所选文档的详细信息。仅适用于一个选定的文档。

因此，主表和详细表之间没有连接。
并且，没有循环来检索所有主文档的详细信息。

为什么您需要了解客户端所有文档的详细信息？

我想说，最佳实践可以归结为常识：

通过网络仅传输您需要的数据而不存在冗余总是好的。保持查询/请求的数量尽可能低总是好的。不要循环发送多个请求，而是发送一个将返回所有必需行的请求。如果确实需要的话，然后在客户身上切片和切块。

---

如果需要以某种方式处理一批文档及其详细信息，那就是另一回事了，到目前为止，我总是设法在服务器端完成此操作，而无需将所有这些数据传输到客户端。

如果由于某种原因需要向客户端获取所有主文档的列表以及所有文档的详细信息，我将进行两个查询而无需任何循环：

SELECT ... FROM GeneralDetails

SELECT ... FROM ItemDetails

这两个查询将返回两个数据数组，如果需要，我将在客户端内存中的内部结构中组合主从数据。

Answer 9

您可以通过分别从两个表中检索所需的数据来进一步优化此过程。然后，您可以循环记录或连接表以生成与来自 SQL Server 的结果集相同的结果集。

使用 ORM，您可以在两次往返中分别检索实体 - 一次检索 GeneralDetails，另一次在检查 GeneralDetails.DocumentId 后检索 ItemDetails >。尽管有两次到数据库的往返，但它比其他两种方法都要优化。

这是一个 NHibernate 示例：

void XXX()
{
    var queryGeneral = uow.Session.QueryOver<GeneralDetails>();
    var theDate = DateTime.Now.Subtract(5);
    queryGeneral.AndRestrictionOn(c => c.SubmittedOn).IsBetween(theDate).And(theDate.AddDays(3));

    // Whatever other criteria applies.

    var generalDetails = queryGeneral.List();

    var neededDocIds = generalDetails.Select(gd => gd.DocumentId).Distinct().ToArray();

    var queryItems = uow.Session.QueryOver<ItemDetails>();
    queryItem.AndRestrictionOn(id => id.DocumentId).IsIn(neededDocs);

    var itemDetails = queryItems.List();

    // The records from both tables are now in the generalDetails and itemDetails lists so you can manipulate them in memory...
}

我相信（没有实际示例）使用 ADO.NET 数据集实际上可以保存到数据库的第二次往返。您甚至不需要加入结果；这是编码风格和工作流程的问题，但通常您可以通过同时处理两个结果集来更新您的 UI，

void YYY()
{
    var sql = "SELECT *  FROM GeneralDetails WHERE DateCreated BETWEEN '2015-06-01' AND '2015-06-20';";
    sql += @"
            WITH cte AS (
                SELECT DocumentId FROM GeneralDetails WHERE DateCreated BETWEEN '2015-06-01' AND '2015-06-20'
            )
            SELECT * FROM ItemDetails INNER JOIN cte ON ItemDetails.DocumentId = cte.DocumentId";

    var ds = new DataSet();

    using (var conn = new SqlConnection("a conn string"))
    using (var da = new SqlDataAdapter())
    {
        conn.Open();
        da.SelectCommand = conn.CreateCommand();
        da.SelectCommand.CommandText = sql;
        da.Fill(ds);
    }

    // Now the two table are in the dataset so you can loop through them and do your stuff...
}

• 注意：我只是为了示例而编写了上面的代码，并且没有经过测试！

Answer 10

自从我提出这个问题以来，我意识到在检索数据方面我还可以在其他方面优化我的应用程序。在这种情况下，我将执行以下操作：

• 问问自己，我真的需要检索许多文档及其子项吗？通常在 UI 中，我会在列表中显示记录，只有当用户需要子项时（如果用户单击记录）我才会检索它们。

• 如果确实需要显示很多带有子项的记录，例如帖子/评论，我只会提供一些帖子，考虑分页，或者提供“加载更多”功能。

总而言之，我可能最终会进行延迟加载，仅在用户需要时才检索数据。

一种避免往返数据库服务器的解决方案，虽然不能保证性能提升，因为它需要在数据库服务器和应用程序中进行更多处理，但它是检索多个记录集，一个结果到父文档，一个结果到子项，请参阅伪代码：

 recordSets = GetData
     ("select * from parentDocs where [condition] ;
        select * from subItems where [condition]")

 //join the parent documents and subitems here

我可能需要一个临时表来存储父文档，这样我就可以将它用于第二个查询中的条件，因为我只需要检索所选父文档的子项。

我还应该指出，进行基准测试比立即应用原则更好，因为它确实是个案的基础。