Why do nested select commands take longer than temporary tables?
Forgive me if this is a repetitive and / or obvious question, but I cannot find a satisfactory answer either on stackoverflow or elsewhere on the internet.
Using Microsoft SQL Server, I have a nested select query that looks like this:
select *
into FinalTable
from
(select * from RawTable1 join RawTable2)
join
(select * from RawTable3 join RawTable4)
Instead of using nested selections, the query can be written using temporary tables, for example:
select *
into Temp1
from RawTable1 join RawTable2
select *
into Temp2
from RawTable3 join RawTable4
select *
into FinalTable
from Temp1 join Temp2
Though equivalent, the second (non-nested) query runs orders of magnitude faster than the first (nested) query. This is true on both my development server and client server. Why?
source to share
The database engine keeps the subqueries in the required memory at runtime, since they are virtual and not physical, the optimizer cannot choose the best route, or at least not before sorting in the plan. It also means that the optimizer will perform multiple full table scans for each operation, rather than a possible index lookup on a temporary table.
Consider each subquery as a juggling ball. The more subqueries you give the db engine, the more things it juggles at one time. If you simplify this in temp table code packages, the optimizer will find a clear route, in most cases regardless of indexes, at least for later versions of SQL Server.
source to share