A problem in memory when I run sp with a large number of records !

 

i describe problem , hope me find way solve !

my task how find "similiar" words in our database ! now, have use software find similiar name. example :

the name : "donaldson filtration slovensko s.r." , name : "penta slovensko donaldsin spol. sro" "similiar". in case : word "donaldson" , "donaldsin" can called similiar because diffrence not more 1 character in same positon in string.

i used stored procedure sovle problem because larg number of records in each table (for on 1 million records each table).

the way had used first create temperary table contain

_____________________

create table #seperatestring1
(
 string1 nvarchar(100)
)

create table #seperatestring2
(
 string2 nvarchar(100)
)

____________

so that, analyze string 2 tables detecting blank character between 2 words !

then, compare values between 2 tables,

_________________________

declare st1_cursor cursor
  select string1 #seperatestring1
  open st1_cursor    
   
  fetch next st1_cursor @string1
  while @@fetch_status = 0
  begin

   declare st2_cursor cursor
   select string2 #seperatestring2
   open st2_cursor  
   
   fetch next st2_cursor @string2
   while @@fetch_status = 0      
   begin
    
    exec comparestring @string1, @string2, @result = @ketqua output
    
    if @ketqua = 1
     begin
      exec setcol1 @id, @rescol1 = @rescol1 output
      exec setcol2 @idci, @rescol2 = @rescol2 output
      select @rescol3 = 'name: '+ char(13) + char(10) + @string1 + ' <==> ' + @string2
      select @rescol4 = 0
       
      select @rescol5 = getdate()
      
      if (not exists(select * result iadata @rescol1 , datafound @rescol2 , reason @rescol3)) , (not exists(select * dictionary value = @string1)) , (not exists(select * dictionary value = @string2))
      begin
       insert result (iadata, datafound, reason, decide, datenow) values (@rescol1, @rescol2, @rescol3, @rescol4, @rescol5)
      end

     end
    fetch next st2_cursor @string2
   end

   close st2_cursor
   deallocate st2_cursor           

   fetch next st1_cursor @string1
  end     

  close st1_cursor
  deallocate st1_cursor 

______________________________________
results add "result" table

after that, deallocate temporary tables have created.

________________
drop table #seperatestring1
drop table #seperatestring2
________________

this sp run in 3 hours !
problems occour memory ! hour run, memory detected in "task manger" grown 200mb --> 300 mb (amazing , suprised !) , computer "run" slowlier before ! don't know why ! can me explain.

for me, way explain in case microsoft sql server 2000, not able free memory after each session, still in ram (and on ram, because there no room contain them)! that, grown in amazing way !

my solution in case use array of string compare (not simulate virtual array)! but, in data type of sp not have "array" !

what can ? please me find way solve problem, !

i think fuzzy lookup , fuzzy grouping transforms in integration services work solving problems 1 described. if need more details, here integration services dedicated forum: http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

hope helps

SQL Server  >  SQL Server Database Engine