Re: Speed up ado updates

On Dec 26, 2007 11:34 PM, lrlebron@xxxxxxxxx <lrlebron@xxxxxxxxx> wrote:
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

If you run the script under ruby-prof -p graph <your_script_name.rb>
you'll find out what parts
of the script take the most of the time. You'll find out whether you
should optimize the ruby part, or the query itself (ADO part).

Here you'll find explanation of the ruby-prof output:
http://on-ruby.blogspot.com/2006/08/ruby-prof-and-call-graphs.html

Notes:

1. I'd make data_source and prefix constants (capital letters, this
might actually hurt the performance -- it depends on whether const
lookup is faster than local var lookup or the other way round -- you
can find out yourself using Benchmark class)

2. You need to escape \ in string literals

DATA_SOURCE = "C:\\path\\F1Backup.sdf"
PREFIX = "E:\\user\\"

3. you might get some performance gains by making some of the
gsub/inserts in place ("!" versions -- gsub!, you'd need to change
your code to accomodate for the change)

4. something might be gained by replacing prefix/PREFIX with a Regex
and fixing its start (PREFIX = /^E:\\user\\/), when it indeed is a
prefix
(again, verify my guess with Benchmark)

.

Relevant Pages

  • Re: Generic delete
    ... this is what I use to mass-delete datasets from a catalog: ... ARG DSNPREFIX ... SAY A DATASET NAME PREFIX IS REQUIRED. ... For IBM-MAIN subscribe / signoff / archive access instructions, ...
    (bit.listserv.ibm-main)
  • Re: modify output of script
    ... nothing is needed), or any other (prefix with newline), ... use regexps in case statements ... puts unless first_group ...
    (comp.lang.ruby)
  • Re: How do I exit from a block
    ... def list base_dir, prefix ... puts "break dosent work either" ... I am trying to recurse all directories and find certain type of files. ...
    (comp.lang.ruby)
  • RE: create hyperlink from predefined string+number field
    ... (The prefix is ... "Uriel TK" wrote: ... > web site catalog, that opens the web browser when clicked ... the hyperlink consists of a constant string which is ...
    (microsoft.public.access.formscoding)
  • Re: Reverse Divisible Numbers (#161)
    ... next if (prefix + suffix).to_i> limit ... puts "Found #numbers" ... def rdn ... nums = rdn ...
    (comp.lang.ruby)