mardi 3 mars 2015

What is causing SQL script to run forever?


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