SqlDataAdapter如何在内部工作？ [英] How SqlDataAdapter works internally?

问题描述

我不知道如何 SqlDataAdapter 在内部工作，特别是当使用 UpdateCommand 更新一个巨大的 DataTable （因为通常只是从循环中发送sql语句更快）。

这里有一些想法： / p>

• 创建一个准备好的sql语句（使用 SqlCommand.Prepare（） code> CommandText 填充和sql参数用正确的sql类型初始化。然后，它循环需要更新的数据行，并为每个记录更新参数值，并调用 SqlCommand.ExecuteNonQuery（）。


• 它创建了一堆 SqlCommand 对象，其中所有内容（ CommandText 和sql参数）。几个SqlCommands一次然后批量到服务器（取决于 UpdateBatchSize ）。


• 它使用一些特殊的，低级或无证的sql允许以有效的方式对多行执行更新的驱动程序指令（要更新的行需要使用特殊的数据格式和相同的sql查询（ UpdateCommand



解决方案

它使用内部设施的SQL Server客户端类（称为命令集）。您可以使用单个命令将多个批次发送到SQL Server。这减少了每个调用的开销。





每个语句更新一行，每批发送一个语句，但每个往返发送多个批次。此列表中的最后一点是魔术酱。

不幸的是，此工具未公开曝光。 Ayende对此进行了一次攻击，并为其构建了一个基于私人反射的API。




如果你想要更多的信息，我鼓励你看看内部的 SqlCommandSet 类。





也就是说，你自己可以比这更快。：使用TVP传输更新数据，并发出一个 UPDATE 更新许多行。





这样的查询将如下所示：

 更新T设置Tx = @ src.x从T连接@src on T.ID = @ src.ID 
  




I wonder how SqlDataAdapter works internally, especially when using UpdateCommand for updating a huge DataTable (since it's usually a lot faster that just sending sql statements from a loop).

Here is some idea I have in mind :

• It creates a prepared sql statement (using SqlCommand.Prepare()) with CommandText filled and sql parameters initialized with correct sql types. Then, it loops on datarows that need to be updated, and for each record, it updates parameters values, and call SqlCommand.ExecuteNonQuery().
• It creates a bunch of SqlCommand objects with everything filled inside (CommandText and sql parameters). Several SqlCommands at once are then batched to the server (depending of UpdateBatchSize).
• It uses some special, low level or undocumented sql driver instructions that allow to perform an update on several rows in a effecient way (rows to update would need to be provided using a special data format and a the same sql query (UpdateCommand here) would be executed against each of these rows).

解决方案

It uses an internal facility of the SQL Server client classes which is called command sets. You can send multiple batches with a single command to SQL Server. This cuts down on per-call overhead. You have less server roundtrips and such.

A single row is updated per statement, and one statement per batch is sent, but multiple batches per roundtrip are send. The last point in this list is the magic sauce.

Unfortunately, this facility is not publicly exposed. Ayende took a hack on this and built a private-reflection bases API for it.

If you want more information I encourage you to look at the internal SqlCommandSet class.

That said, you can go faster than this by yourself: Transfer the update data using a TVP and issue a single UPDATE that updates many rows. That way you save all per-batch, per-roundtrip and per-statement overheads.

Such a query would look like this:

update T set T.x = @src.x from T join @src on T.ID = @src.ID

这篇关于SqlDataAdapter如何在内部工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！