以下SQL命令是如何工作的?
exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [OrderLine] USING (
VALUES (@p1, @p2, 0),
(@p3, @p4, 1),
(@p5, @p6, 2),
(@p7, @p8, 3)) AS i ([Item], [OrderId], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Item], [OrderId])
VALUES (i.[Item], i.[OrderId])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;
SELECT [t].[Id] FROM [OrderLine] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];
',N'@p1 nvarchar(64),@p2 int,@p3 nvarchar(64),@p4 int,@p5 nvarchar(64),@p6 int,@p7 nvarchar(64),@p8 int',@p1=N'Item-1',@p2=1,@p3=N'Item-2',@p4=1,@p5=N'Item-3',@p6=1,@p7=N'Item-4',@p8=1
(它由EF Core生成,并插入了一些OrderLine
实体(。
编辑:
我了解TABLE类型变量的声明,并对MERGE操作有基本的想法。但是很难理解数据是如何以及何时实际插入OrderLine表的。
这是一个有趣的问题,值得用整篇文章来回答。幸运的是,Brent Ozar写了《实体框架核心的奇SQL案例》。
MERGE语句的作用实质上与INSERT ... OUTPUT inserted.ID VALUES (),(),()
相同。子句ON 1=0
确保只执行INSERT分支。那么,为什么会有如此复杂的语法呢?
出现这种奇怪SQL的原因是批处理插入的性能。具体而言,10公里行的性能提高了248%。
在表中插入多行的方法只有几种:
- 您可以在一个长批中编写5000个INSERT查询,但速度很慢。由于每条语句都必须修改索引等,因此速度几乎是单个大型INSERT的5000倍
- 可以传递表类型参数。但这也很慢,因为服务器无法知道该参数中有多少项,并且假设只有一行。这可能会导致非常糟糕的执行计划
- 是否存储在表变量中?与表参数相同的问题
- 您可以先将所有这些行写入临时表,然后将它们插入到目标中,但这也有其自身的问题——表应该命名为什么?它是独一无二的吗
ID呢
表变量用于收集生成的id,_Position
用于维护顺序,即使在并行执行的情况下也是如此。我怀疑,这就是INSERT VALUES
没有被使用的原因。
由于EF Core只在一批中发送了5000个项目,因此它需要一种方法来检索5000个新ID,以识别这些ID属于哪些对象。通常,人们会使用ID来识别行,但一开始没有ID!
唯一剩下的就是按照插入对象的顺序返回ID。INSERT OUTPUT VALUES
不能保证在没有ORDER子句的情况下,服务器可以以最便宜的方式自由返回数据。
在这种情况下,保存ID和顺序的唯一安全方法是在@inserted0
中使用显式_Position
值存储它们,并按该顺序返回它们。
结论
这关乎性能,正如Brent Ozar所说:
是的,SQL并不完美,但它的速度快了248%。
布伦特说:好哇,孩子。这……并不理想。
如何以及何时将数据实际插入OrderLine表中。
操作位为ON 1=0
这意味着行永远不会匹配,所以每一行都会进入
WHEN NOT MATCHED THEN
INSERT ([Item], [OrderId])
VALUES (i.[Item], i.[OrderId])