Listbuilder performance problems

@Sam Giacco I agree this is a drawback of using listbuilders. To cope with this, when things start to get slow with a larger sql statement, and further indexing isn't making a dent, then I'll often try re-arranging some of the sql into “outer apply” and CTE statements to take the load off the main sql statement. If that doesn't work, then I might change my TVF definition to a fixed table definition so that I can use separate sql statements and table variables within the TVF, that way everything is not imposed on a single large sql statement / query plan.

e.g. instead of

create function dbo.USR_MY_FUNCTION ()

returns table

as return (

I'll use:

create function dbo.USR_MY_FUNCTION ()

returns @my_table table (ID uniqueidentifier……)

as begin

In the latter function I can define temp tables, e.g. declare @intermediary_table table (@NAME nvarchar…) and insert chunks of the solution into various table variables, then join things up in the end. Not as good as using temp tables in a stored procedure, but it can certainly help when the principal sql statement begins to struggle.