[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Speed up ado updates

lrlebron@gmail.com

12/26/2007 10:31:00 PM

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
3 Answers

Jano Svitok

12/27/2007

0

On Dec 26, 2007 11:34 PM, lrlebron@gmail.com <lrlebron@gmail.com> 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-g...

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

12/27/2007 1:47:00 PM

0

lrlebron@gmail.com 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

http://rubyonwindows.bl...
http://rubyonwindows.bl.../search/label/sqlserver

--
Posted via http://www.ruby-....

lrlebron@gmail.com

12/27/2007 9:54:00 PM

0

On Dec 27, 7:46 am, David Mullet <david.mul...@gmail.com> wrote:
> lrleb...@gmail.com 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
>
> http://rubyonwindows.blogspot.comhttp://rubyonwindows.blogspot.com/search/label...
>
> --
> Posted viahttp://www.ruby-....

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