Darryll Petrancuri (1/11/2011)
I'm seeing a related issue in that when I try to use an ITVF as the source data for an INSERT INTO a table (using minimally logged operations), where the ITVF is at the root of a hierarchy of nested ITVFs, the performance of the INSERT INTO totally sucks. We're talking fairly large resultsets (6-12 million records).I'm scratching my head, and I've been at this for some time.
Any comments or thoughts.
Respectfully,
Darryll
Nesting that is like nesting views. At some point the optimizer is overwhelmed and just comes up with whatever plan it can. It's a bad design approach that does lead to issues like you're experiencing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning