rgp151 (12/7/2016)
I have a large aggregation query on a large dataset that run forever and never return, but if I run the main part of the query by itself it returns in 5 minutes. I can explain it better with an example:
SELECT
*
FROM
(
SELECT
key,
SUM(value)
FROM
some_table
GROUP BY
key
) s
If I run just the code inside the virtual table, it runs in 5 min, but as soon as I simply nest it and select * from it, then it run indefinitely. Of course I need to do more than select *. The internal query does a bunch of aggregations, then the external query does additional calculations with the values.
I did however try literally just changing it to * to troubleshoot and I still have the same performance problem.
Any ideas what could be causing this?
Can you post (as a .sqlplan attachment) the estimated plan for this, please -
Cheers
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden