带有循环的SQL SERVER INSERT语句需要优化

3 回答

• 1

  这是另一种方法...它完成得非常快......在我的SQL服务器上不到5秒

  if object_id('tempdb..#Numbers') is not null drop table #Numbers
  create table #Numbers (Num int)
  
  insert into #Numbers (Num)
  SELECT TOP (3000000) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
  OPTION (MAXDOP 1);
  
  if object_id('tempdb..#test') is not null drop table #test
  create table #test (text1 nvarchar(50), text2 nvarchar(50))
  
  insert into #test (text1, text2)
  select N'你好' [text1], 'VJ' + cast(Num as nvarchar) [text2] from #Numbers
  

  回复于 2024-04-29T13:23:37+08:00
• 1

  你可以这样试试

  WITH CTE_TEST
  AS
  (
      SELECT N'你好' AS CODE,'VJ'+ cast(1 as varchar(20)) NAME, 1 AS VCOUNT
      UNION ALL
      SELECT N'你好' ,'VJ'+ cast(VCOUNT+1 as varchar(20)) NAME, VCOUNT+1 AS VCOUNT
      FROM    CTE_TEST
      WHERE   VCOUNT+1 < 3000000
  )
  INSERT INTO test (text1 , text2)
  SELECT CODE,NAME FROM CTE_TEST
  OPTION (MAXRECURSION 0)
  

  您可以在CTE中使用其他逻辑，只需将结果集插入实际表中即可 . 这里的insert语句在循环之外（1个插入语句有3000000条记录），因此它比在循环中插入3000000次（3000000个插入语句，每个记录1条记录）更快

  默认情况下，MAXRECURSION为100以避免无限循环，在这里你需要覆盖它（但这不是一个好习惯） .

  回复于 2024-04-29T13:23:37+08:00
• 1

  不使用递归CTE的替代方法是使用具有足够记录的已知表，以便您可以对其进行迭代：

  -- generation parameters
  declare @batchCount INT = 100000
  declare @totalCount INT = 30000000
  declare @loopCount INT = @totalCount / @batchCount
  
  DECLARE @i as int = 0
  -- loops are slow, but here we have only a few
  WHILE (@i < @loopCount)
  BEGIN
      -- insert can be put just here to actually perform the insert
      -- ROW_NUMBER gives us the numbering, but order does not matter, so using SELECT 1
      select TOP (@batchCount) N'你好','VJ'+ cast(@i * @batchCount + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as varchar(20))
      from sys.messages
  
      SET @i = @i + 1
  END
  

  sys.messages 是一个非常大的表（至少 200K 条记录），所以它可以安全地用于 100K 批次 .

  时间使用 recursive CTE ：51s

  使用上述解决方案的时间：28秒

  （在 SQL Server 2014 Express 实例上测试，仅 SELECT ）

  回复于 2024-04-29T13:23:37+08:00