Speed up ado updates

[lrlebron]

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

 

[Jano Svitok]

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)

 

[David Mullet]

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

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

 

[lrlebron]

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

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

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