title: Parsing large txt files in ruby taking a lot of time?
body: <p>below is the code to download a txt file from internet approx 9000 lines and populate the database, I have tried a lot but it takes a lot of time more than 7 minutes. I am using win 7 64 bit and ruby 1.9.3. Is there a way to do it faster ??</p>
  
  <pre><code>require 'open-uri'
  require 'dbi'
  dbh = DBI.connect("DBI:Mysql:mfmodel:localhost","root","")
  #file = open('http://www.amfiindia.com/spages/NAV0.txt')
  file = File.open('test.txt','r')
  lines = file.lines
  2.times { lines.next }
  curSubType = ''
  curType = ''
  curCompName = ''
  lines.each do |line|
      line.strip!
  if line[-1] == ')'
      curType,curSubType = line.split('(')
      curSubType.chop!
  elsif line[-4..-1] == 'Fund'
      curCompName = line.split(" Mutual Fund")[0] 
  elsif line == ''
      next
  else
      sCode,isin_div,isin_re,sName,nav,rePrice,salePrice,date = line.split(';')
      sCode = Integer(sCode)
      sth = dbh.prepare "call mfmodel.populate(?,?,?,?,?,?,?)"
      sth.execute curCompName,curSubType,curType,sCode,isin_div,isin_re,sName
  end
  end
  dbh.do "commit"
  dbh.disconnect
  file.close
  
  
  106799;-;-;HDFC ARBITRAGE FUND RETAIL PLAN DIVIDEND OPTION;10.352;10.3;10.352;29-Jun-2012
  </code></pre>
  
  <p>This is the format of data to be inserted in the table. Now there are 8000 such lines and how can I do an insert by combining all that and call the procedure just once. Also, does mysql support arrays and iteration to do such a thing inside the routine. Please give your suggestions.Thanks.  </p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have to make insertion's into the tables depending on whether they are already exist or not, also I need to make use of conditional comparison's before inserting into the table. I definitely can't write SQL statements for these, so I wrote SQL stored procedures. Now I have a list @the_data, how do I pass that to the procedure and then iterate through it all on MySQL side. Any ideas ?</p>
  
  <pre><code>insert into mfmodel.company_masters (company_name) values
  #{@the_data.map {|str| "('#{str[0]}')"}.join(',')}
  </code></pre>
  
  <p>this makes 100 insertions but 35 of them are redundant so I need to search the table for existing entries before doing a insertion.</p>
  
  <p>Any Ideas ? <strong>thanks</strong></p>