亲爱的朋友， 这些天，我在将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 cfcponent_fixed_id,cfc.level5_code_fixed,cfcponent_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 cfcponent_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 cfcponent_level_id=clponent_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数据库中的搜索优化
发布评论