我有一个性能问题。
我创建了一个从文件接收数据的表,使用
当我运行这个
我创建了一个从文件接收数据的表,使用
BULK INSERT
。然后我使用多个INNER JOIN
进行SELECT
,将正确的数据插入到另一个表中。当我运行这个
SELECT
时,它需要太长时间(超过一小时),然后我停止了它。我的解决方案是将这个查询分成3个,创建@temp
表。令我惊讶的是,这只需要3分钟。我试图理解的是,为什么将我的查询分成3个比一个选择语句更快。以下是我的查询:SELECT t1.ReturnINT, t1.ReturnBIT, t2.ReturnINT, t3.ReturnINT, t5.ReturnINT, t1.ReturnDateTime
FROM t1
INNER JOIN t2
ON t2.my_column_varchar = t1.my_column_varchar
INNER JOIN t3
ON t3.my_column_number = t1.my_column_number AND t2.my_column_ID = t3.my_column_ID
INNER JOIN t4
ON t4.my_column_varchar = t1.my_column_varchar
INNER JOIN t5
ON t5.my_column_int = t1.my_column_int AND t5.my_column_int = t4.my_column_int AND t2.my_column_int = t5.my_column_int
INNER JOIN t6
ON t6.my_column_int = t5.my_column_int AND t6.my_column_int = t2.my_column_int
INNER JOIN t7
ON t7.my_column_int = t6.my_column_int
INNER JOIN t8
ON t8.my_column_int = t3.my_column_int AND t8.my_column_datetime = t1.my_column_datetime
INNER JOIN t9
ON t9.my_column_int = t3.my_column_int AND t8.my_column_datetime BETWEEN t9.my_column_datetime1 AND t9.datetime1 + t9.my_column_datetime2
INNER JOIN t10
ON t10.my_column_int = t9.my_column_int AND t10.my_column_int = t6.my_column_int
INNER JOIN t11
ON t11.my_column_int = t9.my_column_int AND t8.my_column_datetime = t11.my_column_datetime
----编辑----
没有where子句,我的查询就像我在这里放的一样。
这是我的错误查询语句,我忘记把它们放在这里了。它需要3分钟才能运行。
DECLARE @temp TABLE (
<Some_columns>
)
INSERT INTO @temp
SELECT <My_Linked_Columns>
FROM t1
INNER JOIN t2
ON t2.my_column_varchar = t1.my_column_varchar
INNER JOIN t3
ON t3.my_column_number = t1.my_column_number AND t2.my_column_ID = t3.my_column_ID
INNER JOIN t4
ON t4.my_column_varchar = t1.my_column_varchar
INNER JOIN t5
ON t5.my_column_int = t1.my_column_int AND t5.my_column_int = t4.my_column_int AND t2.my_column_int = t5.my_column_int
DECLARE @temp2 TABLE(
<Some_Columns>
)
INSERT INTO @temp2
SELECT <More_Linked_Columns>
FROM @temp as temp
INNER JOIN t6
ON t6.my_column_int = temp.my_column_int AND t6.my_column_int = temp.my_column_int
INNER JOIN t7
ON t7.my_column_int = t6.my_column_int
INNER JOIN t8
ON t8.my_column_int = temp.my_column_int AND t8.my_column_datetime = temp.my_column_datetime
DECLARE @temp3 TABLE(
<Some_Columns>
)
INSERT INTO @temp3
SELECT <More_Linked_Columns>
FROM @temp2 AS temp2
INNER JOIN t9
ON t9.my_column_int = temp2.my_column_int AND temp2.my_column_datetime BETWEEN t9.my_column_datetime1 AND t9.datetime1 + t9.my_column_datetime2
INNER JOIN t10
ON t10.my_column_int = t9.my_column_int AND t10.my_column_int = temp2.my_column_int
INNER JOIN t11
ON t11.my_column_int = t9.my_column_int AND temp2.my_column_datetime = t11.my_column_datetime
SELECT <All_Final_Columns>
FROM @temp3
----EDITED 3----
在研究更多事情时,我发现了执行计划中的一个问题。我有一个嵌套循环,估计返回1行,但实际上返回了1,204,014行。我猜问题就出在这里,但我还没有找出如何在不将查询分解为3部分的情况下解决此问题(现在我知道为什么分解它会更快啦)。