Ramón Ontiveros (1/11/2011)
By the way, I did work around this problem replacing the ITVF with store procedures, I don't know exactly way, but talking about performance, it's better a sp. I could use a lot of nested sp and make a more DRY design. I think maybe its because in the sp I can use temporary tables that use less memory and the SQL Server can make a better execution plan. The disadvantage is that sp are a bit more dificult to write and less maintainable than ITVF's
yeah, without seeing the code for the functions or the proc, I couldn't tell you why either. But when you're trying to figure this stuff out, the answer usually can be found in the execution plans. That's where I'd go to understand why something is running slow.
"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