problem with delay time in stored procedure

Question

hi,
i have pasted the stored procedure below and the execution time of stored procedure takes more time.

CREATE PROCEDURE p_diamondsearch_stud_Earring_test
(
@LowPrice varchar(50)= '100',
@HeighPrice varchar(50)= '1000000',
@Cut1 varchar(20)='' ,
@Cut2 varchar(20)=''  ,
@Shape varchar(50) = '0',
@Weight1 varchar(6) = '0',
@Weight2 varchar(6) = '0',
@ColorLow varchar(50) ,
@ColorHeigh varchar(50),
@ClarityLow varchar(50),
@ClarityHeigh varchar(50),
@OrderBy varchar(50)='catalognr',
@SortOrder varchar(10)='ASC'
)
 AS
BEGIN
create table #Diamonds1
(
partno1 varchar(30),color1 varchar(10),weight1 decimal(18,6),cut1 varchar(10),clarity1 varchar(10),length1 decimal(18,6),depth1 decimal(18,6),width1 decimal(18,6),sell1 decimal(10,2),LWRatio1 decimal(20,2),polish1 varchar(10),symmtry1 varchar(10),lab varchar(10),table_perc decimal(10,2),fluorescence varchar(10),shape varchar(10)
)
create table #Diamonds2
(
partno1 varchar(30),color1 varchar(10),weight1 decimal(18,6),cut1 varchar(10),clarity1 varchar(10),length1 decimal(20,2),depth1 decimal(20,2),width1 decimal(20,2),sell1 decimal(10,2),LWRatio1 decimal(20,2),polish1 varchar(10),symmtry1 varchar(10),lab varchar(10),table_perc decimal(10,2),fluorescence varchar(10),shape varchar(10)
)
declare @StrSQL varchar(5000),
@tablevar int
SET  @StrSQL = ''
                   SET  @StrSQL = @StrSQL +   ' SELECT   D.partno as partno1,D.color as color1,D.weight as weight1 ,D.cut as cut1 ,D.clarity as clarity1 ,D.length as lenght1,D.depth as depth1,D.width as width1 ,D.sell as sell1 ,LWRatio1 = (D.length/D.width), D.polish as polish1 ,D.symmetry as symmtry1,D.lab as lab, D.table_perc as tableperc1,D.fluorescence as fluorescence1,D.shape
                    FROM product_diam D where  (D.catalognr  = ''1025'' or D.catalognr = ''1027'')  AND D.New_Release=''N'' AND (D.qtyoh - D.qtycom) > 0 and D.cut > 0 and D.avail <> ''T'' and  (D.below_standard = 0) '
          --PRICE
          if len(@LowPrice) > 0 and len(@HeighPrice) > 0
          Begin
                   Set @StrSQL = @StrSQL + '  AND (D.sell )  BETWEEN ' + @LowPrice + ' AND ' + @HeighPrice
          End
          --Cut
          if len(@Cut1) > 0 and len(@Cut2) > 0
          Begin
                   Set @StrSQL = @StrSQL + '  AND D.cut    BETWEEN '+ @Cut1 + 'AND ' + @Cut2
           End
          --Shape
          if len(@Shape)>0
          Begin
                   Set @StrSQL = @StrSQL + '  AND D.shape = ' + @Shape
          End
          --Weight
          if len(@Weight1) > 0 and len(@Weight2) > 0
          Begin
                  Set @StrSQL = @StrSQL + '  AND (D.weight)  BETWEEN ' + @Weight1 + ' AND ' + @Weight2
                 
          End
          --Color
          if len(@ColorLow) > 0 and len(@ColorHeigh) > 0
          Begin
                   Set @StrSQL = @StrSQL + '  AND D.color   BETWEEN ''' + @ColorLow + ''' AND ''' + @ColorHeigh + ''''
                 
          End
          --Clarity
          if len(@ClarityLow) > 0 and len(@ClarityHeigh) > 0
          Begin
                   Set @StrSQL = @StrSQL + '  AND D.clarity   BETWEEN ' + @ClarityLow + ' AND ' + @ClarityHeigh
                 
          End
         
     
--print(@StrSQL)
declare @str varchar(5000)
set @str=''
 insert into #Diamonds1  Exec(@StrSQL) 
insert into #Diamonds2 Exec(@StrSQL) 
create index diamond_ind1 on #Diamonds1(weight1,cut1,color1,clarity1,length1,depth1,width1,table_perc,shape,symmtry1,polish1,fluorescence,partno1)
create index diamond_ind2 on #Diamonds2(weight1,cut1,color1,clarity1,length1,depth1,width1,table_perc,shape,symmtry1,polish1,fluorescence,partno1)
set @str = @str + ' SELECT  D.partno1,D.color1,D.weight1,D.cut1,D.clarity1,D.length1,D.depth1,D.width1,D.sell1,D.polish1,D.symmtry1,D.lab,D.table_perc as tableperc1,D.fluorescence as fluorescence1,D.shape as shape1,LWRatio1=(D.length1/D.width1)
                              ,P.partno1 as partno2,P.color1 as color2 ,P.weight1 as weight2 ,P.cut1 as cut2 ,P.clarity1 as clarity2 ,P.length1 as length2,P.depth1 as depth2 ,P.width1 as width2 ,P.sell1 as sell2 , P.polish1 as polish2 ,P.symmtry1 as symmetry2,P.lab as lab2,P.shape as shape2,LWRatio2=(D.length1/D.width1)
                   ,P.table_perc as tableperc2,P.fluorescence as fluorescence2,Total =(D.sell1 + P.sell1)
                            FROM #Diamonds1 D join #Diamonds2  P on D.partno1 <> P.partno1 and 
     (P.weight1  between (D.weight1 -  .01) and (D.weight1 + .01))
                   and (((D.cut1= 1) and (P.cut1 = 1))
                             or ((D.cut1 between 1 and 2 ) and  (P.cut1 between 1 and 2 ))
                             or ((D.cut1 between 2 and 3 ) and  (P.cut1 between 2 and 3 ))
                             or ((D.cut1 between 3 and 4 ) and  (P.cut1 between 3 and 4 ))
                             or ((D.cut1 = 4) and (P.cut1 = 4)))             
                   and (((D.color1 between ''D'' and ''E'') and (P.color1 between ''D'' and ''E''))
                             or ((D.color1 between ''E'' and ''F'') and (P.color1 between ''E'' and ''F''))
                             or ((D.color1 between ''F'' and ''G'') and (P.color1 between ''F'' and ''G''))
                            or ((D.color1 between ''G'' and ''H'') and (P.color1 between ''G'' and ''H''))
                             or ((D.color1 between ''H'' and ''I'') and (P.color1 between ''H'' and ''I''))
                             or ((D.color1 between ''I'' and ''J'') and (P.color1 between ''I'' and ''J''))
                             or ((D.color1 between ''J'' and ''K'') and (P.color1 between ''J'' and ''K''))
                             or ((D.color1 between ''K'' and ''L'') and (P.color1 between ''K'' and ''L''))
                             or ((D.color1 between ''L'' and ''M'') and (P.color1 between ''L'' and ''M'')))
                   and (((D.clarity1 = 5) and (P.clarity1 = 5))
                             or ((D.clarity1 between 10 and 15) and  (P.clarity1 between 10 and 15))
                             or ((D.clarity1 between 20 and 25) and  (P.clarity1 between 20 and 25))
                             or ((D.clarity1 between 30 and 35) and  (P.clarity1 between 30 and 35))
                             or ((D.clarity1 = 40) and (P.clarity1 = 40))) '

        if @Shape=4
                    Begin
                   set @str = @str +  'and ((P.length1 between  D.length1 - .10 and D.length1 + .10)  or (P.length1 between  D.length1 and D.length1 + .10))
                   and ((P.depth1 between  D.depth1 - .10 and D.depth1 + .10)  or (P.depth1 between  D.depth1 and D.depth1 + .10))
                   and ((P.width1 between  D.width1 - .10 and D.width1 + .10)  or (P.width1 between  D.width1 and D.width1 + .10))
                  and (D.length1/D.width1) between (P.length1/P.width1)-.01 and (P.length1/P.width1)+.01
                   and  ((D.table_perc between P.table_perc - 2 and P.table_perc + 2 ) and (P.table_perc between D.table_perc - 2 and D.table_perc + 2 ))'           
       End
                   if @Shape<>4
                   Begin
                 set @str = @str + 'and ((P.length1 between  D.length1 - .10 and D.length1 + .10)  or (P.length1 between  D.length1 and D.length1 + .10))
                   and ((P.depth1 between  D.depth1 - .10 and D.depth1 + .10)  or (P.depth1 between  D.depth1 and D.depth1 + .10))
                   and ((P.width1 between  D.width1 - .10 and D.width1 + .10)  or (P.width1 between  D.width1 and D.width1 + .10))
                   and (D.length1/D.width1) between (P.length1/P.width1)-.05 and (P.length1/P.width1)+.05
                   and  ((D.table_perc between P.table_perc - 5 and P.table_perc + 5 ) and (P.table_perc between D.table_perc - 5 and D.table_perc + 5 ))'                               
                   End
                  set @str = @str + 'and D.cut1=P.cut1 and D.shape = P.shape  and D.symmtry1= P.symmtry1 and D.polish1=P.polish1 and D.fluorescence=P.fluorescence  AND ((D.partno1 + P.partno1)  <> (P.partno1+D.partno1)) '
    if len(@OrderBy)>0
          Begin
                   if (@OrderBy = 'sell')
                   Begin
                             Set @str = @str + '  Order By (D.sell1)   ' + @SortOrder                    
                   End
                   else
                   Begin
  set @OrderBy = @OrderBy + '1' 
                             Set @str = @str + '  Order By D.' + @OrderBy+  ',P.'+ @OrderBy + '  ' + @SortOrder
                   end
          End
          END
Exec(@str) 
drop index #Diamonds1.diamond_ind1
drop index #Diamonds2.diamond_ind2
drop table #Diamonds1
drop table #Diamonds2

--Exec(@StrSQL) 
--print @StrSQL
GO

in the above procedure i have created two dummy tables and inserted some values in the dummy tables based on some condition.This process takes only less time.
But after that i am selecting some records from the dummy tables based on some condition but here the delay time seem to be very high.How to solve this problem.
one of them told to do the following
After inserting to the dummy table create index for the fields which is used in the last search conditions
Check which condition is taking more time.
i have also created the index for dummy tables but the time seems to be very high.....
Please any one hepl me..........Is there any other way to solve it.
It's urgent...
Regards,
Prakash