动态SQL INSERT需要的时间比它应该使用的时间长30倍

Question

我们已经构建了一个可用于创建数据仓库的C#.NET系统。该系统采用选定的数据库，并针对这些数据库运行脚本，以创建组合的数据库/仓库。

现在，我有三个数据库要编译成一个数据库，并且我正在从每个数据库中复制两个表(表[neneneba XI]和表[XII]，它们具有一对多关系，但在复制/INSERT INTO时没有设置约束)。要运行的脚本的数字和每个表的相关大小如下：

执行的脚本由30个SQL查询组成。

1. 数据库A:

   Table [XI]  29,026 Rows (size 20,128Kb).
   Table [XII] 531,958 Rows (size 50,168Kb).
   Time taken for entire script: 1.51s.
   

2. 数据库B：

   Table [XI]  117,877 Rows (size 17,000Kb).
   Table [XII] 4,000,443 Rows (size 512,824Kb).
   Time taken for entire script: 2.04s.
   

这两者运行良好且速度快。下一个几乎与第一个完全相同，但需要40倍的长度！

1. 数据库C:

   Table [XI]  29,543 Rows (size 20,880Kb).
   Table [XII] 538,302 Rows (size 68,000Kb).
   Time taken for entire script: 44.38s.
   

我不明白为什么要花这么长时间。我使用过SQL Server Profiler和性能监视器，但我无法确定性能发生这种巨大变化的原因。

用于进行更新的查询是动态的，显示在这个问题的底部——由于显式引用了所需的列，所以查询很大。我的问题是；是什么导致了执行时间的过度增加？

任何线索都将不胜感激。

SQL:

DECLARE @DbName NVARCHAR(128);
SET @DbName = (SELECT TOP 1 [DbName] 
FROM [IPACostAdmin]..[TmpSpecialOptions]);
DECLARE @FilterSql NVARCHAR(MAX);
SET @FilterSql = (SELECT TOP 1 [AdditionalSQL] 
FROM [IPACostAdmin]..[TmpSpecialOptions]);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @SQL1 NVARCHAR(MAX);
DECLARE @SQL2 NVARCHAR(MAX);
SET @SQL1 = 
'INSERT INTO [' + @DbName + ']..[Episode] 
([Fields1], ..., [FieldN])'; 
SET @SQL2 = 
'SELECT 
[Fields1], ..., [FieldN] 
FROM [B1A] ' + @FilterSql + ';'; 
SET @SQL = @SQL1 + @SQL2;
EXEC(@SQL);
GO

注意：为了清楚起见，我将动态SQL拆分为@SQL1和@SQL2。还要注意的是，由于篇幅的原因，以及它在很大程度上是多余的，我没有显示所有列。

第1版。

1.数据库在同一台服务器上

2.数据库文件(包括日志)位于同一驱动器上的同一目录中

3.在INSERT INTO时，没有在源数据库(数据库A/B/C)或数据仓库数据库上设置主键/外键或约束

第2版。我在管理工作室运行了上述查询，花了5秒

Edit3.我添加了一个临时CLUSTERED INDEX，希望这能帮助这个查询，但这也没有帮助

Answer 1

了解一些信息会很好：

1:数据库在同一台服务器上？

2:在A和C的情况下，db文件和日志文件在同一个驱动器上？

(有一次我遇到了两个数据库的问题，其中一个在SSD驱动器上，另一个在HDD中。这是读取数据的问题)

3:DB关于碎片的统计数据？(表没有约束，但定义了索引？)

Answer 2

这是由于在前一个CREATE CLUSTERED INDEX查询有时间更新整个表之前运行了DELETE查询造成的。解决方案是使用BEGIN TRANSACTION和COMMIT关键字。这将强制SQL Server在尝试任何其他操作之前完成索引。

请注意，只有在CREATE CLUSTERED INDEX查询后面有一条修改现有选项卡的动态SQL语句时，才可能出现此问题。

我希望这能帮助其他人。