Database Reference
In-Depth Information
Figure 12-1.
Execution plan for the query that joins a table and a function
As you know, SQL Server always estimates that multi-statement table-valued functions return just one row.
It would lead to a very inefficient execution plan in our example.
The I/O statistics and execution time on my computer produced the results shown in Listing 12-5.
Listing 12-5.
Using temporary tables to optimize queries: I/O statistics and execution time
Table 'Orders'. Scan count 250, logical reads 201295
Table '#25869641'. Scan count 1, logical reads 1
SQL Server Execution Times:
CPU time = 249 ms, elapsed time = 239 ms.
Now let's change our approach and populate a temporary table with the values returned by
ParseIDList
function, as shown in Listing 12-6.
Listing 12-6.
Using temporary tables to optimize queries: temporary table approach
declare
@List varchar(8000)
-- Populate @List with comma-separated list of integers
-- from 1 to 250
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N4)
select @List = convert(varchar(8000),
(
select ID as [text()], ',' as [text()]
from IDs
where ID <= 250
for xml path('')
)
)
