Search optimization in SQL Database

See more:

SQL-Server-2005

C#2.0

C#

, +

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

Kindly help

Thanks & Regards

Varun Sareen