SQL 表值函数(fn_Split) --- IN(表值函数) 与 CHARINDEX、LIKE 性能测试

• 什么是表值函数
  当调用的时候，结果集是一张表结果集

--无参数
CREATE  FUNCTION tvpoints ( )
RETURNS TABLE
AS
RETURN( SELECT    *FROM      tb_users);

• 以上 RETURN 后 返回的是一张 查询表的结果集

--有参
/****** Object: UserDefinedFunction [dbo].[fn_Split] Script Date: 2022/4/12 14:57:35 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[fn_Split](@Input NVARCHAR(MAX) ,@Separator NVARCHAR(MAX) = ',' ,@RemoveEmptyEntries BIT = 1)
RETURNS @TABLE TABLE([Id] INT IDENTITY(1, 1) ,[Value] NVARCHAR(MAX))
ASBEGIN DECLARE @Index INT ,@Entry NVARCHAR(MAX);SET @Index = CHARINDEX(@Separator, @Input);WHILE ( @Index > 0 )BEGINSET @Entry = LTRIM(RTRIM(SUBSTRING(@Input, 1, @Index - 1)));IF ( @RemoveEmptyEntries = 0 )OR ( @RemoveEmptyEntries = 1AND @Entry <> '')BEGININSERT  INTO @TABLE( [Value] )VALUES  ( @Entry );END;SET @Input = SUBSTRING(@Input,@Index + DATALENGTH(@Separator) / 2,LEN(@Input));SET @Index = CHARINDEX(@Separator, @Input);END;SET @Entry = LTRIM(RTRIM(@Input));IF ( @RemoveEmptyEntries = 0 )OR ( @RemoveEmptyEntries = 1AND @Entry <> '')BEGININSERT  INTO @TABLE( [Value] )VALUES  ( @Entry );END;RETURN;END;

• [dbo].[fn_Split] 分割表函数，

  @Input 需要分割的字符
  @Separator 分割的字符中特殊字符进行分割
  @RemoveEmptyEntries 索引位置
  @TABLE 分割后存入表类型中

• 场景：

• A表 TREE_CODE字段是 存的值是 ,91175,36035,24916, 类型string 对应 多条B表

• B表ID字段是 存的值是 91175、36035、24916、类型int 那么如何 让 A表 TREE_CODE字段 关联 B表ID字段
  

• [fn_Split] 分割表函数 变成表结果集,91175,36035,24916,

SELECT  LI.TREE_CODE ,LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) ON LT.ID IN (91175,36035,24916)/*一般方式IN 把 LI.TREE_CODE(,91175,36035,24916,)中的字符变成INT 类型 如LT.ID IN (91175,36035,24916)对于数据量大的一般会些一个 [fn_Split] 分割表函数*/
WHERE   LI.ID = 66888;--[fn_Split] 分割表函数
SELECT  LI.TREE_CODE ,LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) ON LT.ID IN (SELECT Value FROM [dbo].[fn_Split](LI.TREE_CODE,',', 1) )
WHERE   LI.ID = 66888;

LT.ID IN (SELECT Value FROM [dbo].[fn_Split](LI.TREE_CODE,',', 1) )
--影响性能的地方
--要去进入fn_Split 函数内部逻辑进行处理 ，最后以表结果的形式输出
--LT.ID IN （） IN的匹配逻辑是全表搜索

• 以上这种方式 性能会很慢，占用资源多
• 所以有下面两种方式
• CHARINDEX判断是否存在包含 ，速度比调用fn_Split 表值函数快3-5倍

SELECT  LI.TREE_CODE ,LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) ON CHARINDEX(','+ CONVERT(VARCHAR(15), LT.ID)+ ',',LI.TREE_CODE) > 0/*影响性能的地方--CHARINDEX 匹配是否存在 W_D_PACKING_LIST 全表匹配--有强制转换 VARCHAR--匹配 后有 判断*/
WHERE   LI.ID = 66888;

• LIKE模糊查询，大数据情况下 速度比CHARINDEX 快0.8倍

--用LT.ID 去模糊匹配 LI.TREE_CODE 
--LT.ID 要转VARCHAR类型 并且 一定要加',' 因为LI.TREE_CODE 里面是以','分割的
SELECT  LI.TREE_CODE ,LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) ON LI.TREE_CODE LIKE '%,'+ CONVERT(VARCHAR(15), LT.ID)+ ',%'/*影响性能的地方--LIKE 模糊匹配W_D_PACKING_LIST 全表匹配，如果字段值格式不统一很容易弄错 --有强制转换 VARCHAR*/
WHERE   LI.ID = 66888;

总结：

• 如果数据量不大，追求最稳定方式 选 [fn_Split] 分割表函数
• 如果数据量大，追求性能，相对稳定的方式 选CHARINDEX
• 追求高性能选LIKE，注意字段值的格式统一