From: "lrlebron@..." <lrlebron@...>
Date: 2007-12-27T07:34:59+09:00
Subject: Speed up ado updates

I have a script that uses ado to update a database. The script works
but it is very slow.

require 'win32ole'

data_source = "C:\path\F1Backup.sdf"
prefix = "E:\user\"

begin
  db = WIN32OLE.new("ADODB.Connection")
  db.open("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=#{data_source}")
rescue Exception => ex
  puts ex.to_s
  exit
end

sql_string=<<End_of_String
Select FilePath from catalog WHERE ParentDirectory Is Null
End_of_String

rs = db.execute(sql_string)

rs.MoveFirst
while !rs.eof
  rs.fields.each do |field|
    file_path = field.value.to_s.gsub("'", "''")
    string = file_path.split('__--')[0].gsub(prefix,'')
    parent_directory = string.slice(0,string.rindex("\
\")).insert(1,':')
    sql_update = "Update catalog set
ParentDirectory='#{parent_directory}' WHERE FilePath='#{file_path}'"
    puts sql_update
    db.execute(sql_update)
  end
  rs.MoveNext
end

Any ideas on how to make it run faster?

thanks,

Luis