I have a script that applies the INTERSECT
operator to 2 separate queries.
Query1 executes instantly and returns ~1k rows. Query2 executes almost instantly and returns 200k rows
However, when running [Query1] INTERSECT [Query2]
, it executes for a long time without returning any rows in the process.
I have already found solutions to return the desired results, but I need to identify what is causing the original script to run forever. The script is automatically generated by configurations made in a GUI.
I can not show the full code, but I'll try to outline it
--query1
select col1, col2 from
(
select col1, col2...
-- 2 Left Join's here
intersect
select col1, col2...
-- 2 Left Join's here
) table1
intersect
--query2
select col1, col2 from
(
select col1, col2...
-- 2 Left Joins
UNION
select col1, col2...
-- 2 Left Joins
UNION
select col1, col2...
-- 2 Left Joins
) table2
In the estimated execution plan, Table Spool (Lazy Spool) has a high cost: 30%.
Aucun commentaire:
Enregistrer un commentaire