I am optimizing a query which involves a UNION ALL
of two queries. Both Queries have been more than optimized and run at less than one second separately. However, when I perform the union of both, it takes around 30 seconds to calculate everything.
我正在优化一个涉及UNION ALL两个查询的查询。两种查询都经过优化,并且分别在不到一秒的时间内运行。但是,当我执行两者的并集时,计算所有内容大约需要30秒。
I won't bother you with the specific query, since they are optimized as they get, So let's call them Optimized_Query_1
and Optimized_Query_2
我不打扰你使用特定的查询,因为它们在获得时进行了优化,所以让我们称之为Optimized_Query_1和Optimized_Query_2
Number of rows from Optimized_Query_1 is roughly 100K
Optimized_Query_1的行数大约为100K
Number of rows from Optimized_Query_2 is roughly 200K
Optimized_Query_2的行数大约为200K
SELECT * FROM (
Optimized_Query_1
UNION ALL
Optimized_Query_2
) U
ORDER BY START_TIME ASC
I do require for teh results to be in order, but I find that with or without the ORDER BY at the end the query takes as much time so shouldn't make no difference.
我确实要求结果有序,但我发现无论有没有ORDER BY,查询都需要花费尽可能多的时间,所以不应该有任何区别。
Apparently the UNION ALL creates a temporary table in memory, from where the final results are then given, is there any way to work around this?
显然UNION ALL在内存中创建了一个临时表,然后从那里得到最终结果,有什么方法可以解决这个问题吗?
Thanks
1 个解决方案
#1
2
You can't optimize UNION ALL
. It simply stacks the two results sets on top of each other. Compared to UNION
where an extra step is required to remove duplicates, UNION ALL
is a straight stacking of the two result sets. The ORDER BY
is likely taking additional time.
您无法优化UNION ALL。它只是将两个结果集叠加在一起。与需要额外步骤来删除重复项的UNION相比,UNION ALL是两个结果集的直接堆叠。 ORDER BY可能需要额外的时间。
You can try creating a VIEW
out of this query.
您可以尝试使用此查询创建VIEW。
#1
2
You can't optimize UNION ALL
. It simply stacks the two results sets on top of each other. Compared to UNION
where an extra step is required to remove duplicates, UNION ALL
is a straight stacking of the two result sets. The ORDER BY
is likely taking additional time.
您无法优化UNION ALL。它只是将两个结果集叠加在一起。与需要额外步骤来删除重复项的UNION相比,UNION ALL是两个结果集的直接堆叠。 ORDER BY可能需要额外的时间。
You can try creating a VIEW
out of this query.
您可以尝试使用此查询创建VIEW。