哪一种效率更高？

[chengwenjian]

现有一个代码翻译函数供查询语句使用，以下两种写法，哪种方案效率更高一些呢？

方案一：

crate function [dbo].[KSO_fn_CityTranslation]
(
  @Citys varchar(60)
)
RETURNS varchar(200)
As
Begin
  declare @Result varchar(200)

  declare @S1 varchar(60)
  declare @S2 varchar(60)
  declare @Pos int
  
  declare @Tmp varchar(40)
  set @S1 = @Citys
  set @Result = ''
  while (@S1 <> '')
  begin
    set @Pos = charindex(',', @S1)
    if  @Pos > 0
    begin
      set @S2 = substring(@S1, 1, @Pos - 1)
      set @S1 = substring(@S1, @Pos + 1, 200)
    end
    else
    begin
      set @S2 = @S1
      set @S1 = ''
    end
    if @S2 <> ''
    begin
      set @Tmp = null
      select @Tmp = IsNull(City, @S2) From KSO_tb_b_city Where Code = @S2 and DeleteFlag = 0
      if @Tmp is not null
         set @Result = @Result + @Tmp + ','         
    end  
  end
  if @Result <> ''
  begin
    if substring(@Result, Len(@Result), 1) = ','
    begin
      set @Result = substring(@Result, 1, Len(@Result) - 1)
    end
  end

  return @Result  
End

方案二：

crate function [dbo].[KSO_fn_CityTranslation]
(
  @Citys varchar(60)
)
RETURNS varchar(200)
As
Begin
  declare @Result varchar(200)

  Select @Result = IsNull(@Result + ',', '') + City 
  From KSO_tb_b_City Where charindex(Code + ',', @Citys + ',') > 0

  return @Result  
End

表结构如下：

CREATE TABLE [dbo].[KSO_tb_b_City](
	[Code] [varchar](3) NOT NULL,
	[City] [varchar](40) NULL,
	[DeleteFlag] [bit] NULL,
	[IsDefault] [bit] NULL,
	[CreateTime] [datetime] NULL,
	[CreateOperator] [varchar](20) NULL,
	[LastUpdateTime] [datetime] NULL,
	[LastUpdateOperator] [varchar](20) NULL,
	[ByNo] [varchar](4) NULL,
	[ShortName] [varchar](2) NULL,
 CONSTRAINT [PK_EA_tb_b_City] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

示例数据：

INSERT INTO dbo.KSO_tb_b_City ( Code, City, DeleteFlag, IsDefault, CreateTime, CreateOperator, LastUpdateTime, LastUpdateOperator, ByNo, ShortName ) 
		 VALUES ( '010', '北京', 0, 1, NULL, NULL, NULL, NULL, NULL, '京' ) 
go
INSERT INTO dbo.KSO_tb_b_City ( Code, City, DeleteFlag, IsDefault, CreateTime, CreateOperator, LastUpdateTime, LastUpdateOperator, ByNo, ShortName ) 
		 VALUES ( '020', '广州', 0, 0, NULL, NULL, NULL, NULL, NULL, '穗' ) 
go
INSERT INTO dbo.KSO_tb_b_City ( Code, City, DeleteFlag, IsDefault, CreateTime, CreateOperator, LastUpdateTime, LastUpdateOperator, ByNo, ShortName ) 
		 VALUES ( '021', '上海', 0, 0, NULL, NULL, NULL, NULL, '', '沪' ) 
go
INSERT INTO dbo.KSO_tb_b_City ( Code, City, DeleteFlag, IsDefault, CreateTime, CreateOperator, LastUpdateTime, LastUpdateOperator, ByNo, ShortName ) 
		 VALUES ( '022', '天津', 0, 0, '12/23/2005 12:38:24.140 PM', 'F010033', '12/23/2005 12:38:24.140 PM', 'F010033', '', '' ) 
go
INSERT INTO dbo.KSO_tb_b_City ( Code, City, DeleteFlag, IsDefault, CreateTime, CreateOperator, LastUpdateTime, LastUpdateOperator, ByNo, ShortName ) 
		 VALUES ( '023', '重庆', 0, 0, '12/23/2005 12:40:35.827 PM', 'F010033', '12/23/2005 12:40:35.827 PM', 'F010033', '', '' ) 
go
INSERT INTO dbo.KSO_tb_b_City ( Code, City, DeleteFlag, IsDefault, CreateTime, CreateOperator, LastUpdateTime, LastUpdateOperator, ByNo, ShortName ) 
		 VALUES ( '024', '沈阳', 0, 0, '12/23/2005 12:29:21.500 PM', 'F010033', '12/23/2005 12:29:21.500 PM', 'F010033', '', '' ) 
go

[ghd2004]

看看

[dawugui]

自己测试一下不就知道了?

[中国风]

方案二..
--------
方案一把简单的问题复杂化了

[-狙击手-]

二，

但是你的那些记录全是唯一的一条吗？

[chengwenjian]

在其他表格中将会是“010,022,024”或者“010”这样的值，使用该函数后将会表现为“北京,天津,沈阳”、“北京”。

[chengwenjian]

在存放“010,022,024”或者“010”这样的值的表格中（如KSO_tb_e_SalesContract表PbCitys字段），没有为该字段建立索引。

[chengwenjian]

使用如下语句查询，第一种方案比第二种方案返回结果更快一些，难道是charindex函数效率低？

Select top 2 ContractId, PbCitys, dbo.KSO_fn_CityTranslation(PbCitys)
 From KSO_tb_e_SalesContract

[gimse7en]

mark

[chengwenjian]

自己顶下。哪会大侠能现身给解释下？

[yangjiexi]

--帮顶一下!

[pt1314917]

这样效率会更高一点：

ALTER   function FLX_Get_HE05_Name
(@values varchar(60))
returns varchar(200)
AS
begin
DECLARE @FirstWord varchar(64)
DECLARE @Results varchar(500)
DECLARE @len int

set @Results = ''

set @len = len(@values)
WHILE @len > 0
begin
    DECLARE @Oldword        VARCHAR(4000)
    DECLARE @Length         INT
    DECLARE @CommaLocation  INT
    DECLARE @CurData        VARCHAR(200)

    SELECT @Oldword = @values
    IF NOT @Oldword IS NULL  
    BEGIN
        SELECT @CommaLocation = CHARINDEX(',',@Oldword)
        SELECT @Length = DATALENGTH(@Oldword)
	       IF @CommaLocation = 0
	        BEGIN
	            SELECT @FirstWord = @Oldword
	            SELECT @values = NULL
	            set @len = 0
	        END
		else
		begin
		        SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)
		        SELECT @values = SUBSTRING(@Oldword, @CommaLocation + 1, @Length - @CommaLocation)
		        set @len =  @Length - @CommaLocation
		end
    END
    if(len(@FirstWord) > 0)
    Begin
	select @CurData = city FROM KSO_tb_b_City WHERE code=@FirstWord
	IF(len(@Results) > 0)
	Begin
		set @Results = @Results + ','
	END
	set @Results = @Results + @CurData
    END
end

return @Results
end