使用用户定义的函数和性能？

I'm using stored procedure to fetch data and i needed to filter dynamically. For example if i dont want to fetch some data which's id is 5, 10 or 12 im sending it as string to procedure and im converting it to table via user defined function. But i must consider performance so here is a example:

我正在使用存储过程来获取数据,我需要动态过滤。例如,如果我不想获取一些id为5,10或12的数据,我将其作为字符串发送到过程并通过用户定义的函数将其转换为表。但我必须考虑性能,所以这里是一个例子:

Solution 1:

SELECT * 
FROM Customers 
WHERE CustomerID NOT IN (SELECT Value 
                         FROM dbo.func_ConvertListToTable('4,6,5,1,2,3,9,222',','));

Solution 2:

CREATE TABLE #tempTable (Value NVARCHAR(4000));

INSERT INTO #tempTable 
        SELECT Value FROM dbo.func_ConvertListToTable('4,6,5,1,2,3,9,222',',')

SELECT * 
    FROM BusinessAds 
    WHERE AdID NOT IN (SELECT Value FROM #tempTable)

DROP TABLE #tempTable

Which solution is better for performance?

哪种解决方案更适合性能?

1 个解决方案

#1

You would probably be better off creating the #temp table with a clustered index and appropriate datatype

使用聚簇索引和适当的数据类型创建#temp表可能会更好

CREATE TABLE #tempTable (Value int primary key);
INSERT INTO #tempTable 
SELECT DISTINCT Value 
FROM dbo.func_ConvertListToTable('4,6,5,1,2,3,9,222',',')

You can also put a clustered index on the table returned by the TVF.

您还可以在TVF返回的表上放置聚簇索引。

As for which is better SQL Server will always assume that the TVF will return 1 row rather than recompiling after the #temp table is populated, so you would need to consider whether this assumption might cause sub optimal query plans for the case that the list is large.

至于哪个更好,SQL Server将始终假设TVF将返回1行而不是在填充#temp表后重新编译,因此您需要考虑此假设是否可能导致列表为的情况下的子优化查询计划大。

#1