哪一种效率更高？

2007-12-23 10:25:36 174 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

在Proteus中用80C51模拟4线SPI点亮OLED屏幕在Proteus里用80C51模拟4线SPI控制OLED屏幕使其显示图案（汇编实现）。阅读详情

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

方案一：



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

...全文

汽车LED驱动芯片TPS92682-Q1 SPI接口配置与调试实战指南 SPI（串行外设接口）作为一种高速、全双工的同步串行通信总线，广泛应用于微控制器与外围设备之间的数据交换。其工作原理基于主从架构，通过时钟、数据线和片选信号实现设备间的精准时序控制。在汽车电子和工业控制领域，SPI因其简单高效、易于扩展的特性，成为配置复杂电源管理芯片、传感器和存储器的关键技术。通过SPI接口，工程师能够动态调整设备参数、实时读取状态信息，并实现复杂的故障诊断与保护策略，极大地提升了系统的灵活性与可靠性。本文以汽车级双通道LED恒流驱动器TPS92682-Q1为例，深入解析如何通过SPI接口阅读详情

chengwenjian

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

pt1314917 2007-12-25

打赏
举报



这样效率会更高一点：



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

yangjiexi 2007-12-25

打赏
举报



--帮顶一下!

chengwenjian 2007-12-25

打赏
举报

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

gimse7en 2007-12-25

打赏
举报

mark

chengwenjian 2007-12-24

打赏
举报

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



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

 From KSO_tb_e_SalesContract

chengwenjian 2007-12-24

打赏
举报

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

chengwenjian 2007-12-24

打赏
举报

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

-狙击手- 2007-12-23

打赏
举报

二，

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

中国风 2007-12-23

打赏
举报

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

dawugui 2007-12-23

打赏
举报

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

ghd2004 2007-12-23

打赏
举报

看看

哪一种效率更高？

相关推荐