使用存储过程重新计数 [英] to retrive count using stored procedure

问题描述

i希望使用like operator.in从三个colmuns中检索计数我的数据库我有70万的记录使用存储过程的column1，column2，column3使用like运算符我想得到计数，但它需要5分钟来提取查询，如何在一分钟内得到计数，我为column1，column2创建索引，但我没有为column3创建







cretae procedure [dbo ]。[getcount]

（



@keyword varchar（50），

@totalCount int OUT

）

开始



从TableName中选择@ totalCount = count（*）其中（（Column1 Like' ％'+ @ keyword +'％'）或（Column2Like'％'+ @ keyword +'％'）或（Column3赞'％'+ @ keyword +'％'））





结束













请告诉我如何解决它

解方案

简单地取代*与列名。它将减少执行查询的时间。

 cretae  procedure  [dbo]。[getcount] 
（
  @ keyword   varchar （ 50 ），
）
  as  
 声明 
  @ totalCount   int  OUT 
 开始 
 选择 @ totalCount = count（column1）+ count（column2）+ count（column3）< span class =code-keyword> from  TableName  where （（Column1 赞 ' ％' + @ keyword + ' ％'）或（Column2Like ' ％' + @ keyword + ' ％'）或（Column3 赞  ' ％' + @ keyword + ' ％ '））
 
  end  

Hi,



i want to retreive the count from three colmuns using like operator.in my database i had 70 lakhs of record am using the stored procedure for column1,column2,column3 using like operator i want to get the count but it is taking 5 min to exicute the query,how to get the count within a min,and i created index for column1,column2 but i didn't created for column3



cretae procedure [dbo].[getcount]
(

@keyword varchar(50),
@totalCount int OUT
)
as begin

select @totalCount=count(*) from TableName where ((Column1 Like '%'+@keyword+'%') or(Column2Like '%'+@keyword+'%') or (Column3 Like '%'+@keyword+'%'))


end






Please tell me how to solve it

解决方案

simply replace * with column name. It will reduce the time for executing the query.

cretae procedure [dbo].[getcount]
(
@keyword varchar(50),
)
as 
declare 
@totalCount int OUT
begin
select @totalCount=count(column1) + count(column2) + count(column3) from TableName where ((Column1 Like '%'+@keyword+'%') or(Column2Like '%'+@keyword+'%') or (Column3 Like '%'+@keyword+'%'))
 
end

这篇关于使用存储过程重新计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！