使用关键字列和变量作为要搜索的文本来优化SQL搜索 [英] Optimizing an SQL search using a column for keywords and a variable as the text to be searched
问题描述
我知道如何做到这一点的唯一方法是一次选择一列中的每一行,然后使用带有各自通配符的LIKE运算符来查看是否在变量中的文本中的任何位置找到列中的关键字。我尝试这样做的每一种方式最终都会导致效率低下。
另一种方法是,如果我可以将像FREETEXT这样的运算符的顺序颠倒过来像FREETEXT(@input,关键字),这将是非常棒的。但我似乎无法找到一种简单而有效的方法。
关于如何尽可能有效地做到这一点的任何提示?我非常感谢你的帮助!
编辑:
这里是我的存储过程,供参考: / p>
GO
@input varchar(1000),
@debug varchar(25)输出
AS
BEGIN
SELECT TOP 1 @debug = kw.keyword
FROM(SELECT @input input)bigstring
INNER JOIN table1 kw
on bigstring.input LIKE'%'+ kw.keyword +'%'
END
将@keywords声明为table(keyword varchar(50))
INSERT INTO @关键字
VALUES('quandary'),
('variable'),
('paragraph'),
('Narwhal')
DECLARE @input as varchar(max)
SET @input ='我的窘境。我有一个包含一段文本的变量,并且我有一列充满关键字的列。我想根据包含在变量'
SELECT关键字,CHARINDEX(关键字,@input,0)$ b $中的全部文本来搜索列中包含的每个关键字b FROM @keywords
SELECT kw.keyword
FROM
(SELECT @input input)bigstring
INNER JOIN @keywords kw
on bigstring.input like '%'+ kw.keyword +'%'
(4行受影响)
关键字
------ ----------------- --------------------
quandary 10
variable 29
第54段
Narwhal 0
(4行受影响)
关键字
----------- -------------
窘境
变量
段落
(3行受影响)
如果有CROSS APPLY解决方案,我也不会感到惊讶
更新
仅取得第一个关键字作为out param
CREATE TABLE table1(keyword varchar(50 ))
INSERT INTO table1
VALUES('quandary'),
('variable'),
('paragraph'),
('Narwhal')
GO
程序
CREATE proc testKeyword
@input varchar(1000),
@debug varchar(25)输出
AS
BEGIN
SELECT TOP 1 @debug = kw.keyword
FROM(SELECT @input input)bigstring
INNER JOIN table1 kw
on bigstring.input LIKE'%' + kw.keyword +'%'
END
测试
DECLARE @debug varchar(25)
EXEC testKeyword'Heres my quandary。我有一个包含一段文本的变量,并且我有一列充满关键字的列。我想搜索列中包含的每个关键字与包含在我的变量中的全部文本',
@debug out
SELECT @debug
输出
-------------------------
窘境
(1行受影响)
Here's my quandary. I have a variable that contains a paragraph of text, and I have a column full of keywords. I want to search each of the keywords contained in the column against the entirety of the text contained within my variable.
The only way that I know how to do this is to select each row of the column one at a time, and then use the LIKE operator with wildcards on each side to see if the keyword from the column is found anywhere in the text within the variable. Every way that I try and do this ends up seeming GROSSLY inefficient.
Another way of looking at this is, if I could reverse the order of an operator like FREETEXT to do something like FREETEXT(@input, keywords), that would be awesome. But I just can't seem to figure out a way to do it simply and efficiently like that.
Any tips on how to do this as efficiently as possible? I am very grateful for the help!
Edit:
Here is my stored procedure, for reference:
GO
@input varchar(1000),
@debug varchar(25) output
AS
BEGIN
SELECT TOP 1 @debug = kw.keyword
FROM (SELECT @input input) bigstring
INNER JOIN table1 kw
on bigstring.input LIKE '%' + kw.keyword + '%'
END
Here are two ways depending on what you looking to do (assuming SQL 2005+ from the FREETEXT
keyword). The first select after the sample data returns the index of the keyword (filter out zeros if you don't want keywords that aren't found). The second just checks for the existance
Declare @keywords as table (keyword varchar(50))
INSERT INTO @keywords
VALUES ('quandary'),
('variable'),
('paragraph'),
('Narwhal')
DECLARE @input as varchar(max)
SET @input = 'Heres my quandary. I have a variable that contains a paragraph of text, and I have a column full of keywords. I want to search each of the keywords contained in the column against the entirety of the text contained within my variable'
SELECT keyword, CHARINDEX(keyword, @input , 0)
FROM @keywords
SELECT kw.keyword
FROM
(SELECT @input input) bigstring
INNER JOIN @keywords kw
on bigstring.input like '%' + kw.keyword + '%'
(4 row(s) affected)
keyword
----------------------- --------------------
quandary 10
variable 29
paragraph 54
Narwhal 0
(4 row(s) affected)
keyword
-----------------------
quandary
variable
paragraph
(3 row(s) affected)
I wouldn't be surprised if there was a CROSS APPLY solution as well
Update Getting only the first keyword out as an out param
Data
CREATE TABLE table1 (keyword varchar(50))
INSERT INTO table1
VALUES ('quandary'),
('variable'),
('paragraph'),
('Narwhal')
GO
Proc
CREATE proc testKeyword
@input varchar(1000),
@debug varchar(25) output
AS
BEGIN
SELECT TOP 1 @debug = kw.keyword
FROM (SELECT @input input) bigstring
INNER JOIN table1 kw
on bigstring.input LIKE '%' + kw.keyword + '%'
END
Test
DECLARE @debug varchar(25)
EXEC testKeyword 'Heres my quandary. I have a variable that contains a paragraph of text, and I have a column full of keywords. I want to search each of the keywords contained in the column against the entirety of the text contained within my variable',
@debug out
SELECT @debug
outputs
-------------------------
quandary
(1 row(s) affected)
这篇关于使用关键字列和变量作为要搜索的文本来优化SQL搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!