How does it works? - Multiple SubSelect in Query

Question

I have a query generated by QueryFolding of PowerBi Desktop. I was trying to understand how it works. I can see that there is a big query, with nested sub-selects.

If I run this query, this took about 1 seconds to complete. But when I run the second query, i.e. the inner-one of the multiple subselects, this tooks about two minutes.

So, how does SQL server handles the TOP 1000 or the WHERE in a sub-select? Why is the inner query slower than the whole query?

Thanks!!

Whole Query (Faster)

select top 1000
    [_].[NOMBRE_1] as [NOMBRE_1],
    [_].[SUSCRIPCIO] as [SUSCRIPCIO],
    [_].[ANOMES] as [ANOMES],
    1 as [Rut]
from 
(
    select [_].[NOMBRE_1],
        [_].[SUSCRIPCIO],
        [_].[ANOMES]
    from 
    (
        select [NOMBRE_1],
            [SUSCRIPCIO],
            [ANOMES]
        from [dbo].[TABLE] as [$Table]
    ) as [_]
    where [_].[SUSCRIPCIO] >= convert(datetime2, '2020-01-01 00:00:00') and [_].[SUSCRIPCIO] <= convert(datetime2, '2020-02-01 00:00:00')
) as [_]

Inner SubSelects (Slower)

select [NOMBRE_1],
    [SUSCRIPCIO],
    [ANOMES]
from [dbo].[TABLE] as [$Table]

I have to ask, why would you ever chose to alias your objects as `[_]` or `[$Table]` — Thom A, Jun 24 '20 at 19:47
I didn't choose that. That code is "automatically" generated by the "QueryFolding" feature of PowerBi (BTW, I hate it) — Cristian Avendaño, Jun 24 '20 at 19:55

score 0 · Accepted Answer · answered Jun 24 '20 at 21:28

0

Because the whole query includes two filters:

where [_].[SUSCRIPCIO] >= convert(datetime2, '2020-01-01 00:00:00') and [_].[SUSCRIPCIO] <= convert(datetime2, '2020-02-01 00:00:00')

and

top 1000

This significantly limits the values the query returns, allowing the query to run faster. The inner query, on the other hand, has no filters, so it must return the full result set. If [$Table] has a ton of rows, then this can be a lengthy process

answered Jun 24 '20 at 21:28

cpalmer

311
2
9

Thanks. But the weird part, is that the outer query uses the inner query, and the outer query is faster than the inner. So, should I assume that SQL Server is capable of filter the inner query with the WHERE of the outer query? – Cristian Avendaño Jul 07 '20 at 13:32
1

Correct. The filters in the outer queries mean that it only has to grab a limited set of values from the inner query. It doesn't fully evaluate the inner query first and then filter it, but instead filters as it goes until the criteria is met. Just like if you had a table with millions of rows and you filtered it using 'select top 10 *', it wouldn't try to select all rows and then only return the top ten, it would return 10 rows and then stop running. [This thread](https://stackoverflow.com/questions/2326395/which-one-have-better-performance-derived-tables-or-temporary-tables) explains further – cpalmer Jul 07 '20 at 17:18

How does it works? - Multiple SubSelect in Query

1 Answers1