SQL数据库中的搜索优化 [英] Search optimization in SQL Database
问题描述
亲爱的朋友，
这些天，我在将SQL中的数据搜索达到最佳水平方面遇到了很大的麻烦.我在SQL中有一个数据结构，其中包含大量数据，并且数据每天都在增加.
我们的项目与C#.Net2.0和SQL Server 2005中的房地产和制造公司有关.它包含组件和材料(项目)的主表.
有一个表组件母版，其中组件以良好的速度增长.我们的客户以以下方式询问搜索:-
如果搜索包含以下文本:-电缆端接套件，浇铸树脂化合物".
然后搜索应像这样执行:-
1).首先搜索确切的关键字，即电缆端接套件，浇铸树脂化合物"
2).第二次搜索应针对整个字符串中的每个单词，即电缆" ，结尾" ，终止" ， "Kit" ，发布" ，树脂" ，化合物".
3).第三次搜索应类似于:-％电缆端接套件，浇铸树脂化合物％"
我已经完成了一个存储过程，将所有搜索到的项目收集到
一个表格，然后我可以通过该表格将它们选择到一个数据表中.但是即使是存储过程也要花很多时间来执行，而我别无选择:((而是切换到codeproject以获得答案.
我将附加用于搜索的整个存储过程:-
Dear Friends,
I am in a huge trouble these days in bringing the data search in SQL to an optimum level. I have a data structure in SQL which contains huge amount of data and that data is increasing day by day.
Our project is related to real estate and manufacturing firms in C#.Net2.0 and SQL Server 2005. It contains a master table of components and materials (items).
There is a table component master in which the component are increasing at a good pace. Our client has asked the search in the following pattern:-
If the search contains text as:- "Cable End Termination Kit, Cast Resin Compound".
Then the search should execute like this:-
1). First Search on exact keyword i.e., "Cable End Termination Kit, Cast Resin Compound"
2). Second search should be on the each word in the whole string i.e, "Cable","End","Termination","Kit","Cast","Resin","Compound".
3). Third search should be like:- "%Cable End Termination Kit, Cast Resin Compound%"
I have made a stored procedure that collects all the searched items into
a table and through which i can then select them into a datatable. But even the stored procedure is taking lot of time to execute and i am left with no other option :( but to switch to codeproject for the answer.
I am attaching the whole stored procedure that I am using to search:-
ALTER Procedure [dbo].[sp_Search_keyword_components_fixed_code_New]
(
-- Add the parameters for the function here
@keystring varchar(200)
,@Current_Logged_User int
)
AS
Begin
declare @count_row int
declare @count int
declare @countId int
declare @TempId int
declare @tempWord varchar(200)
declare @tempWord2 varchar(400)
declare @ColumnSearchFieldTemp varchar(200)
declare @ColumnIdFieldTemp varchar(200)
--temp table
Declare @temp_Table_keyword Table
(
Id int IDENTITY(1,1),
key_word varchar(200)
)
Declare @temp_Table_Id Table
(
Id int IDENTITY(1,1),
TempId int
)
Declare @temp_Table_Id_Distn Table
(
Id int IDENTITY(1,1),
TempId int
)
Declare @temp_ComponentL5 table
(
component_fixed_id int,
level5_code_fixed varchar(5),
component_level_id int,
long_description varchar(max),
short_description varchar(max),
urc_code varchar(50),
source_code varchar(50),
unit_name varchar(25),
type_name varchar(100)
)
Declare @temp_component_fixed_Id TABLE
(
Id int IDENTITY(1,1),
TempIdTable int
)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
--set @TableNameTemp =@TableName
--set @ColumnSearchFieldTemp = 'level_name'
--set @ColumnIdFieldTemp=@ColumnIdField
insert into @temp_Table_keyword values (@keystring)
insert into @temp_Table_keyword select Data from [dbo].[Split] (Replace(LTRIM(RTRIM(@keystring)),',',' ') ,' ')
-- Insert statements for procedure here
-- select * from @temp_Table_keyword
select @count_row=count(*) from @temp_Table_keyword
insert into @temp_Table_Id (TempId) select component_fixed_id from component_fixed_code where
Replace(LTRIM(RTRIM(short_description)),',',' ')=Replace(LTRIM(RTRIM(@keystring)),',',' ') or Replace(LTRIM(RTRIM(long_description)),',',' ')=Replace(LTRIM(RTRIM(@keystring)),',',' ');
--select* from @temp_Table_Id
--loop0
set @count=1
set @tempWord2 = ''
while @count <= @count_row
begin
select @tempWord = key_word from @temp_Table_keyword where Id = @count;
--print convert(varchar(10), @count) +'. '+@tempWord;
select @tempWord2 = @tempWord2 + @tempWord ;
select @count = @count+1;
--print @tempWord2 ;
print @tempWord2 ;
insert into @temp_Table_Id (TempId) select component_fixed_id from component_fixed_code where Replace(LTRIM(RTRIM(short_description)),',',' ') like Replace(LTRIM(RTRIM(@keystring)),',',' ') or Replace(LTRIM(RTRIM(long_description)),',',' ') like Replace(LTRIM(RTRIM(@keystring)),',',' ');
insert into @temp_Table_Id (TempId) select component_fixed_id from component_fixed_code where Replace(LTRIM(RTRIM(short_description)),',',' ') like Replace(LTRIM(RTRIM(@tempWord)),',',' ')+'%' or Replace(LTRIM(RTRIM(long_description)),',',' ') like Replace(LTRIM(RTRIM(@tempWord)),',',' ')+'%';
insert into @temp_Table_Id (TempId) select component_fixed_id from component_fixed_code where Replace(LTRIM(RTRIM(short_description)),',',' ') like '%'+ Replace(LTRIM(RTRIM(@tempWord)),',',' ') or Replace(LTRIM(RTRIM(long_description)),',',' ') like '%'+ Replace(LTRIM(RTRIM(@tempWord)),',',' ');
insert into @temp_Table_Id (TempId) select component_fixed_id from component_fixed_code where Replace(LTRIM(RTRIM(short_description)),',',' ') like '%'+ Replace(LTRIM(RTRIM(@tempWord)),',',' ') +'%' or Replace(LTRIM(RTRIM(long_description)),',',' ') like '%'+ Replace(LTRIM(RTRIM(@tempWord)),',',' ') +'%';
end
set @count=1
--loop1
while @count <= @count_row
begin
select @tempWord = key_word from @temp_Table_keyword where Id = @count;
insert into @temp_Table_Id (TempId) select component_fixed_id from component_fixed_code where Replace(LTRIM(RTRIM(short_description)),',',' ') like '%'+ Replace(LTRIM(RTRIM(@tempWord)),',',' ') +'%' or Replace(LTRIM(RTRIM(long_description)),',',' ') like '%'+ Replace(LTRIM(RTRIM(@tempWord)),',',' ') +'%';
SET @count = @count+1;
end
set @count=1
select @count_row=count(*) from @temp_Table_Id
--loop2
while @count <= @count_row
begin
select @TempId=TempId from @temp_Table_Id where Id=@count;
set @countId = (select count(@TempId) from @temp_Table_Id_Distn where TempId=@TempId );
--select @countId ,@TempId,@count
if(@countId =0)
begin
insert into @temp_Table_Id_Distn (TempId) select TempId from @temp_Table_Id where Id=@count;
end
SET @count = @count+1;
end
INSERT INTO @temp_component_fixed_Id (TempIdTable) select TempId from @temp_Table_Id_Distn
Insert into @temp_ComponentL5
select cfc.component_fixed_id,cfc.level5_code_fixed,cfc.component_level_id,cfc.long_description,cfc.short_description,cfc.urc_code,src.source_code,uom.unit_symbol unit_name,mt.type_name
from component_fixed_code cfc
inner join @temp_component_fixed_Id f on cfc.component_fixed_id=f.TempIdTable
inner join source src on cfc.source_id=src.source_id
inner join user_default_sources uds on src.source_id=uds.source_id
inner join user_default_options udo on uds.user_default_option_id=udo.user_default_option_id
inner join unit_of_measurement uom on cfc.unit_id=uom.unit_id
inner join component_levels cl on cfc.component_level_id=cl.component_level_id
inner join material_type mt on cl.material_type_id=mt.material_type_id where udo.user_id=@Current_Logged_User order by f.Id
select * from @temp_ComponentL5
End
请帮助
谢谢&问候
Varun Sareen
Kindly help
Thanks & Regards
Varun Sareen
推荐答案
还没有看到您的查询.我不会使用任何游标或锁.我还将尝试进行第二次搜索，并使用这些结果来创建#1和#3的结果.
您的表结构是什么样的?表格中有多少个不同的单词?您可以预先计算搜索吗?
例如，使用单词word_id_no
创建一个表 使用word_id_no和for_key_row_no_from_your_original_table创建另一个表吗?
当您插入新行时，编写宏以进行相关的簿记.
Haven''t seen your queries. I would not use any cursors or locks. I also would try doing the second search and using those results to create the results for #1 and #3.
What is your table structure like? How many distinct words are in the table? Can you precompute the search?
For example, create a table with word, word_id_no
Create another table with word_id_no and for_key_row_no_from_your_original_table?
When you insert new rows, write macros to do the related bookkeeping.
这篇关于SQL数据库中的搜索优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
