Listbuilder performance problems

Sam Giacco

Hi everyone! I've noticed a recurring problem with listbuilder performance. I will often have sql queries which will run instantly in SSMS directly against the database. However, once I put the sql into a TVFImplementation of a QueryViewSpec, the performance degrades terribly. The results can go from finishing in less than 1 second, to taking 3 or more minutes to complete.

I'm presuming this is because the spec creates a table-valued function, since when I select directly from the TVF in SSMS, I get the same poor performance as I see in CRM itself.

I'm curious if others encounter this same issue, and if there is anything that may help. TVFs have very limited coding options as compared to a stored proc (like datalists use).
Thanks in advance!

Keegan Wade

@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.