Speed up ado updates

Ruby
1

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

2

On Dec 26, 2007 11:34 PM, [email protected] [email protected] wrote:

db.open("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
rs = db.execute(sql_string)
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:

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\”

  1. 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)

  2. 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)

3

[email protected] wrote:

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

…

Any ideas on how to make it run faster?

thanks,

Luis

On the select query end of things, you might want to try using the
Recordset object’s GetRows method, which returns all records as an array
of columns. Then use Ruby’s transpose method to convert it to an array
of rows. This may (or may not) be faster than moving from record to
record via ADO.

rows = recordset.GetRows.transpose
rows.each do |row|
…
end

On the update query end of things, you may want to consider using
transactions to submit your updates as a batch rather than one at a
time. With SQL Server and ADO, I think you do this with SQL commands but
am not certain of the exact syntax. If you have a large number of
updates/inserts, transactions might save significant time.

These suggestions may or may not help, but I thought I’d pass them
along.

David

4

On Dec 27, 7:46 am, David M. [email protected] wrote:

Luis
end
David

http://rubyonwindows.blogspot.comhttp://rubyonwindows.blogspot.com/search/label/sqlserver

–
Posted viahttp://www.ruby-forum.com/.

The transactions seem to speed up the process a bit but only on
smaller database tables. I had a database table with ~10,000 records
and it was about 30% faster with transactions. However, on a much
larger table ~400,000 records it seems to take just as long.

thanks,

Luis